問題是這樣的：
一張test的表，字符集采用的latin1。
select to_id from test where to_id='cn象_王';
+---------------+
| to_id |
+---------------+
| cn陶_陶 |
| cn象_王 |
+---------------+
2 rows in set (0.00 sec)

取cn象_王的數(shù)據(jù),居然把cn陶_陶的數(shù)據(jù)也取回來了。

這顯然是不允許的。

查看它們的編碼：
(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會(huì)認(rèn)為這兩條記錄是一樣的呢？
一開始我們就把問題定位于collation引起的問題。
show variables查看
| collation_connection | latin1_swedish_ci
| collation_database | latin1_swedish_ci
| collation_server | latin1_swedish_ci

手工把這些參數(shù)修改為latin1_bin，結(jié)果居然一樣。這下感覺真是奇怪了。
這里先解釋一下mysql collation的命名規(guī)則：
它們以其相關(guān)的字符集名開始，通常包括一個(gè)語言名，并且以_ci（大小寫不敏感）、_cs（大小寫敏感）或_bin（二元）結(jié)束
比如latin1字符集有以下幾種校正規(guī)則：
校對(duì)規(guī)則 含義
latin1_german1_ci 德國DIN-1
latin1_swedish_ci 瑞典/芬蘭
latin1_danish_ci 丹麥/挪威
latin1_german2_ci 德國 DIN-2
latin1_bin 符合latin1編碼的二進(jìn)制
latin1_general_ci 多種語言(西歐)
latin1_general_cs 多種語言(西歐ISO),大小寫敏感
latin1_spanish_ci 現(xiàn)代西班牙

最后我們將表格重建，手工指定表格級(jí)別的collation為latin1_bin。
這個(gè)問題就得到了解決。

那么問題又來了，為什么我前面手工測(cè)試latin1_bin時(shí)不生效呢？
原來MySQL按照下面的方式選擇表字符集和 校對(duì)規(guī)則：
如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。
如果指定了CHARACTER SET X而沒有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默認(rèn)校對(duì)規(guī)則。
否則，采用服務(wù)器字符集和服務(wù)器校對(duì)規(guī)則。
而我們?cè)诮ū淼臅r(shí)候指定了character set，所以它永遠(yuǎn)是采用對(duì)應(yīng)的默認(rèn)的校對(duì)規(guī)則。

當(dāng)然我們其實(shí)也沒必要重建表格，只需要alter table db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin這樣轉(zhuǎn)換即可。


另外建議collation都盡量采用字符集相應(yīng)的bin類型的校對(duì)規(guī)則，這樣不容易出錯(cuò)

最新評(píng)論