MySQL 字元集和校對

use case

首先看個例子:

有個user 表

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）然後比較編碼。我們稱這是一個大小寫不敏感的 校對規則。比二元校對規則複雜一些。

10.3.1. 伺服器字元集和校對
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的預設校對規則。因此，以下三個命令有相同的效果：
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系統變數的值。在運行時能夠改變這些變數的值。
10.3.2. 資料庫字元集和校對
每一個資料庫有一個資料庫字元集和一個資料庫校對規則，它不能夠為空白。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的預設校對規則。
·        否則，採用伺服器字元集和伺服器校對規則。
MySQL的CREATE DATABASE ... DEFAULT CHARACTER SET ...文法與標準SQL的CREATE SCHEMA ... CHARACTER SET ...文法類似。因此，可以在同一個MySQL伺服器上建立使用不同字元集和 校對規則的資料庫。
如果在CREATE TABLE語句中沒有指定表字元集和校對規則，則使用資料庫字元集和校對規則作為預設值。它們沒有其它目的。
預設資料庫的字元集和校對規則可以用作character_set_database和 collation_database系統變數。無論何時預設資料庫更改了，伺服器都設定這兩個變數的值。如果沒有 預設資料庫，這兩個變數與相應的伺服器層級的變數（character_set_server和collation_server）具有相同的值。
10.3.3. 表字元集和校對
每一個表有一個表字元集和一個校對規則，它不可為空。為指定表字元集和校對規則，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。
·        如果指定了CHARACTER SET X而沒有指定COLLATE Y，那麼採用CHARACTER SET X和CHARACTER SET X的預設校對規則。
·        否則，採用伺服器字元集和伺服器校對規則。
如果在列定義中沒有指定列字元集和校對規則，則預設使用表字元集和校對規則。表字元集和校對規則是MySQL的擴充;在標準SQL中沒有。
10.3.4. 列字元集和校對
每一個“字元”列（即，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按照下面的方式選擇列字元集和校對規則：
·        如果指定了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。