MySQL collation方法

問題是這樣的：
一張test的表，字元集採用的latin1。

select to_id from test where to_id='cn象_王';
+---------------+
| to_id |
+---------------+
| cn陶_陶 |
| cn象_王 |
+---------------+
2 rows in set (0.00 sec)

取cn象_王的資料,居然把cn陶_陶的資料也取回來了。
這顯然是不允許的。

查看它們的編碼：

(root@im_offlog1a)[test]> select hex('cn陶_陶');
+----------------+
| hex('cn陶_陶') |
+----------------+
| 636ECCD55FCCD5 |
+----------------+
1 row in set (0.00 sec)

(root@im_offlog1a)[test]> select hex('cn象_王');
+----------------+
| hex('cn象_王') |
+----------------+
| 636ECFF35FCDF5 |
+----------------+
1 row in set (0.00 sec)

編碼的確是不一樣的，但是為什麼mysql會認為這兩條記錄是一樣的呢？

一開始我們就把問題定位於collation引起的問題。

show variables查看
| collation_connection | latin1_swedish_ci
| collation_database | latin1_swedish_ci
| collation_server | latin1_swedish_ci

手工把這些參數修改為latin1_bin，結果居然一樣。這下感覺真是奇怪了。

這裡先解釋一下mysql collation的命名規則：
它們以其相關的字元集名開始，通常包括一個語言名，並且以_ci（大小寫不敏感）、_cs（大小寫敏感）或_bin（二元）結束

比如latin1字元集有以下幾種校正規則：

校對規則 含義
latin1_german1_ci 德國DIN-1
latin1_swedish_ci 瑞典/芬蘭
latin1_danish_ci 丹麥/挪威
latin1_german2_ci 德國 DIN-2
latin1_bin 符合latin1編碼的二進位
latin1_general_ci 多種語言(西歐)
latin1_general_cs 多種語言(西歐ISO),大小寫敏感
latin1_spanish_ci 現代西班牙

最後我們將表格重建，手工指定表格層級的collation為latin1_bin。
這個問題就得到瞭解決。

那麼問題又來了，為什麼我前面手工測試latin1_bin時不生效呢？

原來MySQL按照下面的方式選擇表字元集和 校對規則：
如果指定了CHARACTER SET X和COLLATE Y，那麼採用CHARACTER SET X和COLLATE Y。
如果指定了CHARACTER SET X而沒有指定COLLATE Y，那麼採用CHARACTER SET X和CHARACTER SET X的預設校對規則。
否則，採用伺服器字元集和伺服器校對規則。

而我們在建表的時候指定了character set，所以它永遠是採用對應的預設的校對規則。

當然我們其實也沒必要重建表格，只需要alter table db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin這樣轉換即可。

另外建議collation都盡量採用字元集相應的bin類型的校對規則，這樣不容易出錯。

再說說我自己的體會

覺得 character set latin1 collate latin1_bin 就是老版的 VARCHAR BINARY 的改進，只是新版的先用 character set 定字元集，再用此字元集名字加 _bin 定校對規則為二進位的，從而確保中文查詢正確。
再測試了一下，把此欄位屬性改為不帶 BINARY 的
ALTER TABLE `comment_content_1_01` CHANGE `thread` `thread` VARCHAR( 50 ) DEFAULT NULL
然後再看錶結構確實變成 `thread` varchar(50) default NULL, 即不帶 character set latin1 collate latin1_bin 了，可見character set latin1 collate latin1_bin 就是老版的 VARCHAR BINARY 的改進。

此外還讀到更方便的做法，不用逐個改欄位屬性，而只要表格層級的collation為latin1_bin就行了。
測試：
alter table comment_content_1_01 CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin
後，

再匯出表結構

CREATE TABLE comment_content_1_01 (
content_id int(11) NOT NULL auto_increment,
thread varchar(50) collate latin1_bin default NULL,
uname varchar(100) collate latin1_bin default NULL,
nick varchar(100) collate latin1_bin default NULL,
uid int(11) unsigned default NULL,
content text collate latin1_bin,
post_time datetime default NULL,
post_ip int(10) unsigned default NULL,
`status` enum('unaudit','normal','deleted') collate latin1_bin NOT NULL default 'unaudit',
PRIMARY KEY (content_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

即便原來沒定各欄位的 collate，現在也全都是 collate latin1_bin 了。