问题是这样的:
一张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类型的校对规则,这样不容易出错
mysql的校对规则引起的问题分析
2019-10-24 17:38 Mysql
在以前用oracle的时候,很少关于它的collation方法,但是在mysql中,这点不加注意的话,却有可能会出现问题。
延伸 · 阅读
- 2019-10-24MySQL collation方法
- 2019-10-24Mysql如何避免全表扫描的方法
- 2019-10-24用MySQL创建数据库和数据库表代码
- 2019-10-24Mysql默认设置的危险性分析
- 2019-10-24MySql增加用户、授权、修改密码等语句
- 2019-10-24mysql数据库导出xml的实现方法
精彩推荐
- Mysql
MySQL5.0存储过程教程
Introduction 简介 MySQL 5.0 新特性教程是为需要了解5.0版本新特性的MySQL老用户而写的。简单的来说是介绍了“存储过程、触发器、视图、信息架构视图”,在此...
- Mysql
MySQL忘记密码恢复密码的实现方法
流传较广的方法,mysql中文参考手册上的,各位vps主机租用客户和服务器托管用户忘记mysql5.1管理员密码时,可以使用这种方法破解下 ...
- Mysql
MySQL按时间统计数据的方法总结
在本篇MYSQL的内容里,我们给大家整理了关于按时间统计数据的方法内容,有需要的朋友们学习下。...
- Mysql
浅谈mysql使用limit分页优化方案的实现
在mysql中limit可以实现快速分页,但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了,否则可能卡死你的服务器哦。感兴趣的可以...
- Mysql
在MySQL数据库中复位根用户的密码的方法
如果你从未为 MySQL 设置根用户 密码 ,服务器在以根用户身份进行连接时不需要密码。但是,建议你为每个账户设置密码。 如果你以前设置了根用户密码,...
- Mysql
MYSQL常用命令与实用技巧
MYSQL常用命令与实用技巧...
- Mysql
比较详细的MySQL字段类型说明
MySQL支持大量的列类型,它可以被分为3类:数字类型、日期和时间类型以及字符串(字符)类型。本节首先给出可用类型的一个概述,并且总结每个列类型的...
- Mysql
MySQL数据库备份方法说明
在数据库表丢失或损坏的情况下,备份你的数据库是很重要的。如果发生系统崩溃,你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态...