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

问题是这样的：
一张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类型的校对规则，这样不容易出错

赞 (0)

相关推荐

  • mysql的校对规则引起的问题分析

    问题是这样的: 一张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:)

  • MySQL表排序规则不同错误问题分析

    MySQL多表join时报错如下:[Err]1267 – Illegal mix of collations(utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '= 就是说两个表的排序规则(COLLATION)不同,无法完成比较.COLLATION是用在排序,大小比较上,一个字符集有一个或多种COLLATION,并且以_ci(大小写不敏感)._cs(大小写敏感)或_bin(二进制)结束.在做比较时,应该确

  • MySQL校对规则(COLLATION)的具体使用

    目录 简介 MySQL的校对规则 校对规则列举 查看校对规则 修改校对规则 总结: 简介 本文介绍MySQL的校对规则. MySQL的校对规则 两个不同的字符集不能有相同的校对规则. 每个字符集有一个默认校对规则.例如: utf8mb4默认校对规则:utf8mb4_general_ci. utf8默认校对规则:utf8_general_ci. 存在校对规则命名约定:它们以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)._cs(大小写敏感)或_bin(二进制,大小写敏感)结

  • mysql字符集和校对规则(Mysql校对集)

    mysql字符集和校对规则(Mysql校对集)

    简要说明 字符集和校对规则 字符集是一套符号和编码.校对规则是在字符集内用于比较字符的一套规则. MySql在collation提供较强的支持,oracel在这方面没查到相应的资料. 不同字符集有不同的校对规则,命名约定:以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)._cs(大小写敏感)或_bin(二元)结束 校对规则一般分为两类: binary collation,二元法,直接比较字符的编码,可以认为是区分大小写的,因为字符集中'A'和'a'的编码显然不同. 字符集

  • mysql中binlog_format模式与配置详细分析

    mysql复制主要有三种方式:基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication, RBR),混合模式复制(mixed-based replication, MBR).对应的,binlog的格式也有三种:STATEMENT,ROW,MIXED. ① STATEMENT模式(SBR) 每一条会修改数据的sql语句会记录到binlog中.优点是并不需要记录每一条sql语句和每一行的数据变化,减少了binl

  • Mysql中校对集utf8_unicode_ci与utf8_general_ci的区别说明

    下面摘录一下Mysql 5.1中文手册中关于utf8_unicode_ci与utf8_general_ci的说明: 当前,utf8_unicode_ci校对规则仅部分支持Unicode校对规则算法.一些字符还是不能支持.并且,不能完全支持组合的记号.这主要影响越南和俄罗斯的一些少数民族语言,如:Udmurt .Tatar.Bashkir和Mari. utf8_unicode_ci的最主要的特色是支持扩展,即当把一个字母看作与其它字母组合相等时.例如,在德语和一些其它语言中'ß'等于'ss'. u

  • Mysql启动报ERROR：2002的分析与解决

    前言 本文主要给大家介绍了关于Mysql启动报ERROR:2002的分析与解决,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧. 1.故障现象 [root@localhost scripts]# mysql -u root ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysqld.sock' (2) 2.故障分析 查看mysql实例的状态 [root@localhost

  • mysql分区功能详解,以及实例分析

    一,什么是数据库分区 前段时间写过一篇关于mysql分表的 的文章,下面来说一下什么是数据库分区,以mysql为例.mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面 (可以通过my.cnf中的datadir来查看),一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表 索引的.如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这 一张

  • 关于Mysql通用查询日志和慢查询日志分析

    MySQL中的日志包括:错误日志.二进制日志.通用查询日志.慢查询日志等等.这里主要介绍下比较常用的两个功能:通用查询日志和慢查询日志. 1.通用查询日志:记录建立的客户端连接和执行的语句. 2.慢查询日志:记录所有执行时间超过longquerytime秒的所有查询或者不使用索引的查询 一.通用查询日志 在学习通用日志查询时,需要知道两个数据库中的常用命令: 1) show variables like '%general%'; 可以查看,当前的通用日志查询是否开启,如果general_log的

  • MySQL中 and or 查询的优先级分析

    MySQL中 and or 查询的优先级分析

    这个可能是容易被忽略的问题,首选我们要清楚: MySQL中,AND的执行优先级高于OR.也就是说,在没有小括号()的限制下,总是优先执行AND语句,再执行OR语句. 比如: select * from table where  条件1 AND 条件2 OR 条件3 等价于 select * from table where  ( 条件1 AND 条件2 )  OR 条件3 select * from table where  条件1 AND  条件2 OR 条件3 AND 条件4 等价于 sel

随机推荐