mysql字符集和校對簡析
use case
首先看個例子:
有個user 表
www.2cto.com
User( username VARCHAR(5) CHARACTER SET utf8COLLATE utf8_general_ci);
有記錄('username');和('UserNAme');
使用 select * from User where username='username'; 將會選出兩條數據
然後將username的COLLATE改為utf8_bin;
User( username VARCHAR(5) CHARACTER SET utf8 COLLATE utf8_bin);
再使用 select * from User where username='username'; 將會選出第一條數據
這是為什麼呢?請往下看:
字符集(CHARACTER SET)是一套符號和編碼。校對規則(COLLATE)是在字符集內用於比較字符的一套規則。讓我們使用一個假想字符集的例子來區別清楚。
假設我們有一個字母表使用了四個字母：‘A’、‘B’、‘a’、‘b’。我們為每個字母賦予一個數值：‘A’=0，‘B’= 1，‘a’= 2，‘b’= 3。字母‘A’是一個符號，數字0是‘A’的編碼，這四個字母和它們的編碼組合在一起是一個字符集。
假設我們希望比較兩個字符串的值：‘A’和‘B’。比較的最簡單的方法是查找編碼：‘A’為0，‘B’為1。因為0 小於1，我們可以說‘A’小於‘B’。我們做的僅僅是在我們的字符集上應用了一個 校對規則。校對規則是一套規則（在這種情況下僅僅是一套規則）：“對編碼進行比較。”我們稱這種全部可能的規則中的最簡單的 校對規則為一個binary（二元）校對規則。
但是，如果我們希望小寫字母和大寫字母是等價的，應該怎樣？那麼，我們將至少有兩個規則：（1）把小寫字母‘a’和‘b’視為與‘A’和‘B’等價；（2）然後比較編碼。我們稱這是一個大小寫不敏感的 校對規則。比二元校對規則復雜一些。
MySQL支持的字符集和校對
http://dev.mysql.com/doc/refman/5.1/zh/charset.html#charset-charsets
服務器字符集和校對
MySQL服務器有一個服務器字符集和一個服務器校對規則，它們均不能設置為空。
MySQL按照如下方法確定服務器字符集和服務器校對規則：
· 當服務器啟動時根據有效的選項設置
· 根據運行時的設定值
在服務器級別，確定方法很簡單。當啟動mysqld時，根據使用的初始選項設置來確定服務器字符集和 校對規則。可以使用--default-character-set設置字符集，並且可以在字符集後面為 校對規則添加--default-collation。如果沒有指定一個字符集，那就與--default-character-set=latin1相同。如果你僅指定了一個字符集（例如，latin1），但是沒有指定一個 校對規則，那就與--default-charset=latin1 --default-collation=latin1_swedish_ci相同，因為latin1_swedish_ci是latin1的默認校對規則。因此，以下三個命令有相同的效果：
www.2cto.com
shell> mysqld
shell> mysqld --default-character-set=latin1
shell> mysqld --default-character-set=latin1 \
--default-collation=latin1_swedish_ci
更改設定值的一個方法是通過重新編譯。如果希望在從源程序構建時更改默認服務器字符集和校對規則，使用：--with-charset和--with-collation作為configure的參量。例如：
shell> ./configure --with-charset=latin1
或者：
shell> ./configure --with-charset=latin1 \
--with-collation=latin1_german1_ci
mysqld和configure都驗證字符集/校對規則組合是否有效。如果無效，每個程序都顯示一個錯誤信息，然後終止。
當前的服務器字符集和校對規則可以用作character_set_server和collation_server系統變量的值。在運行時能夠改變這些變量的值。
數據庫字符集和校對
每一個數據庫有一個數據庫字符集和一個數據庫校對規則，它不能夠為空。CREATE DATABASE和ALTER DATABASE語句有一個可選的子句來指定數據庫字符集和校對規則：
CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
例如：
CREATE DATABASE db_name
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
MySQL這樣選擇數據庫字符集和數據庫校對規則：
· 如果指定了CHARACTER SET X和COLLATE Y，那麼采用字符集X和校對規則Y。
· 如果指定了CHARACTER SET X而沒有指定COLLATE Y，那麼采用CHARACTER SET X和CHARACTER SET X的默認校對規則。
· 否則，采用服務器字符集和服務器校對規則。
www.2cto.com
MySQL的CREATE DATABASE ... DEFAULT CHARACTER SET ...語法與標准SQL的CREATE SCHEMA ... CHARACTER SET ...語法類似。因此，可以在同一個MySQL服務器上創建使用不同字符集和 校對規則的數據庫。
如果在CREATE TABLE語句中沒有指定表字符集和校對規則，則使用數據庫字符集和校對規則作為默認值。它們沒有其它目的。
默認數據庫的字符集和校對規則可以用作character_set_database和 collation_database系統變量。無論何時默認數據庫更改了，服務器都設置這兩個變量的值。如果沒有 默認數據庫，這兩個變量與相應的服務器級別的變量（character_set_server和collation_server）具有相同的值。
表字符集和校對
每一個表有一個表字符集和一個校對規則，它不能為空。為指定表字符集和校對規則，CREATE TABLE 和ALTER TABLE語句有一個可選的子句：
CREATE TABLE tbl_name (column_list)
[DEFAULT CHARACTER SET charset_name [COLLATE collation_name]]
ALTER TABLE tbl_name
[DEFAULT CHARACTER SET charset_name] [COLLATE collation_name]
例如：
CREATE TABLE t1 ( ... )
DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
MySQL按照下面的方式選擇表字符集和 校對規則：
· 如果指定了CHARACTER SET X和COLLATE Y，那麼采用CHARACTER SET X和COLLATE Y。 www.2cto.com
· 如果指定了CHARACTER SET X而沒有指定COLLATE Y，那麼采用CHARACTER SET X和CHARACTER SET X的默認校對規則。
· 否則，采用服務器字符集和服務器校對規則。
如果在列定義中沒有指定列字符集和校對規則，則默認使用表字符集和校對規則。表字符集和校對規則是MySQL的擴展;在標准SQL中沒有。
列字符集和校對
每一個“字符”列（即，CHAR、VARCHAR或TEXT類型的列）有一個列字符集和一個列 校對規則，它不能為空。列定義語法有一個可選子句來指定列字符集和校對規則：
col_name {CHAR | VARCHAR | TEXT} (col_length)
[CHARACTER SET charset_name [COLLATE collation_name]]
例如：
CREATE TABLE Table1
(
column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci
);
MySQL按照下面的方式選擇列字符集和校對規則：
www.2cto.com
· 如果指定了CHARACTER SET X和COLLATE Y，那麼采用CHARACTER SET X和COLLATE Y。
· 如果指定了CHARACTER SET X而沒有指定COLLATE Y，那麼采用CHARACTER SET X和CHARACTER SET X的默認校對規則。
· 否則，采用表字符集和服務器校對規則。
CHARACTER SET和COLLATE子句是標准的SQL。
