什么？MySQL的等值查询竟然出错了？？

吉玉

1 问题背景2 验证2.1数据准备2.2问题验证2.3验证小结3 分析原因4 精确查询的方法4.1LIKE4.2 BINARY5 总结1 问题背景前段时间，一个业务线的小伙伴大G找过来，如下是我俩的对话。大G：云杰，听说你MySQL挺厉害的，我最近遇到一个奇怪问题，不知道你遇到过没，请教你下。我：请教不敢当，我也就是个MySQL入门级选手，说来看看。大G：WHERE条件去等值查询字符串，结果却查出来几条尾部有空格的，明明不相等。我：不会吧？这么神奇，这个真没遇到过！大G：不信你试试！我：试试就试试！抱着求知的心态，开启了本篇的探索之旅。2 验证2.1数据准备首先在测试库里建表，并准备相关的原数据。创建个user_info表，分别插入'adu'(无空格)、'adu'(一个空格)、'adu  '(四个空格)三个用户。CREATE TABLE `user_info` (  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增ID',  `user_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '名字',   RIMARY KEY (`id`),  KEY `idx_user_name` (`user_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'; INSERT INTO user_info(user_name) values('adu'); #无空格INSERT INTO user_info(user_name) values('adu '); #一个空格INSERT INTO user_info(user_name) values('adu    '); #四个空格2.2问题验证2.2.1尾部空格验证SELECT * FROM user_info WHERE user_name = 'adu'; #无空格SELECT * FROM user_info WHERE user_name = 'adu '; #一个空格SELECT * FROM user_info WHERE user_name = 'adu  '; #两个空格SELECT * FROM user_info WHERE user_name = 'adu    '; #四个空格我们使用如上条件去查，还真复现了！无论查询中尾部带有几个空格，结果是一样的，都会命中'adu'、'adu'、'adu  '三个用户，结果如下图所示（红框圈起来的表示我们认为不应该出现的异常结果）：太神奇了！2.2.2头部空格验证那如果把空格放在前面呢？再来一把，结果如下：这下又匹配不上了。空格放在后边可以，放在前边不可以，这太神奇了！！2.2.3唯一索引验证那如果在user_name字段上建唯一索引，还能插入这三条记录吗？再来一把，结果如下：也不行，被唯一索引约束住了。2.2.4长度验证那这三条记录的user_name长度又分别是多少呢？确实长度也不一样。2.3验证小结从结果上来看，明明是三个长度不同的字符串，空格放在前边被认为是不同，放在后边又被认为是相同，而且唯一索引也冲突。我们有充足的理由怀疑MySQL忽略字符串尾部的空格，把'adu'、'adu'、'adu  '都当成'adu'来处理。这确实超出了已有的认知，那背后的原因究竟又是什么呢？3 分析原因查询MySQL的官方文档[1]，原来跟字符串的校对规则有关。原来MySQL的校对规则基于PADSPACE，这就意味着CHAR、VARCHAR、TEXT等字符串的等值比较（“=”）会忽略掉尾部的空格，而且官网也说了，适用于所有MySQL版本，并且不会改变。这。。。既然MySQL官网说的这么肯定，那么自信来自哪里呢？我们继续追查SQL规范，原来SQL规范还真对这块做了特别说明[2]，如下所示：既然规范都这样要求了，等值查询“=”不能精确查询，那么到底该如何精确地进行等值查询呢？4 精确查询的方法通过调研，我们可以通过以下两种方式进行精确等值查询。4.1LIKELIKE是基于逐个字符进行比较的，这样就不会忽略尾部的空格，官网[3]对这块也有特别的说明。那么我们再使用LIKE进行等值查询，结果还真可以！4.2 BINARYBINARY不是函数，是类型转换运算符，它用来强制它后面的字符串转为二进制字节，再逐个字节比较，也可以理解成精确匹配，官网[4]对这块也有特别的说明。那么我们再使用BINARY进行等值查询，结果也是可以的。5 总结MySQL的CHAR、VARCHAR、TEXT等字符串字段在等值比较（"="）时，基于PADSPACE校对规则，会忽略掉尾部的空格；在存储时，不会自动截断尾部的空格，会按原值存储；如果想要精确查询就不能用等值查询（"="），而应改用LIKE或BINARY；认知有界，而求知无界。关于作者杜云杰，高级架构师，转转架构部负责人，转转技术委员会执行主席，腾讯云TVP。负责服务治理、MQ、云平台、APM、IM、分布式调用链路追踪、监控系统、配置中心、分布式任务调度平台、分布式ID生成器、分布式锁等基础组件。微信号：waterystone，欢迎建设性交流。道阻且长，拥抱变化；而困而知，且勉且行。参考资料[1]TheCHARandVARCHARTypes:https://dev.mysql.com/doc/refman/5.7/en/char.html[2]MySQLcomparisonoperator,spaces:https://stackoverflow.com/questions/10495692/mysql-comparison-operator-spaces[3]StringComparisonFunctionsandOperators:https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html[4]BINARY:https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#operator_binary