mysql连接校对:mysql的校对规则引起的问题分析

问题是这样:
张test表集采用latin1
select to_id from test where to_id='cn象_王';
+---------------+
| to_id |
+---------------+
| cn陶_陶 |
| cn象_王 |
+---------------+
2 rows in (0.00 sec)
取cn象_王数据,居然把cn陶_陶数据也取回来了
这显然是不允许
查看它们编码:
(root@im_offlog1a:)[test]> select hex('cn陶_陶');
+----------------+
| hex('cn陶_陶') |
+----------------+
| 636ECCD55FCCD5 |
+----------------+
1 row in (0.00 sec)
(root@im_offlog1a:)[test]> select hex('cn象_王');
+----------------+
| hex('cn象_王') |
+----------------+
| 636ECFF35FCDF5 |
+----------------+
1 row in (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( 2元)结束
比如latin1集有以下几种校正规则:
校对规则 含义
latin1_german1_ci 德国DIN-1
latin1_swedish_ci 瑞典/芬兰
latin1_danish_ci 丹麦/挪威
latin1_german2_ci 德国 DIN-2
latin1_bin 符合latin1编码 2进制
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 所以它永远是采用对应默认校对规则
当然我们其实也没必要重建表格只需要alter table db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin这样转换即可

另外建议collation都尽量采用集相应bin类型校对规则这样不容易出错

延伸阅读

• 2010-12-9-- mysql问题,mysql的校对规则引起的问题分析