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

1.问题背景

前段时间,一个业务线的小伙伴大G找过来,如下是我俩的对话。

柞水网站制作公司哪家好,找创新互联公司!从网页设计、网站建设、微信开发、APP开发、响应式网站开发等网站项目制作,到程序开发,运营维护。创新互联公司成立与2013年到现在10年的时间,我们拥有了丰富的建站经验和运维经验,来保证我们的工作的顺利进行。专注于网站建设就选创新互联公司

大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 '名字',

PRIMARY 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的校对规则基于PAD SPACE,这就意味着CHAR、VARCHAR、TEXT等字符串的等值比较(“=”)会忽略掉尾部的空格,而且官网也说了,适用于所有MySQL版本,并且不会改变。这。。。

既然MySQL官网说的这么肯定,那么自信来自哪里呢?我们继续追查SQL规范,原来SQL规范还真对这块做了特别说明[2],如下所示:

既然规范都这样要求了,等值查询“=”不能精确查询,那么到底该如何精确地进行等值查询呢?

4.精确查询的方法

通过调研,我们可以通过以下两种方式进行精确等值查询。

4.1 LIKE

LIKE是基于逐个字符进行比较的,这样就不会忽略尾部的空格,官网对这块也有特别的说明。

那么我们再使用LIKE进行等值查询,结果还真可以!

4.2 BINARY

BINARY不是函数,是类型转换运算符,它用来强制它后面的字符串转为二进制字节,再逐个字节比较,也可以理解成精确匹配,官网[4]对这块也有特别的说明。

那么我们再使用BINARY进行等值查询,结果也是可以的。

5.总结

  • MySQL的CHAR、VARCHAR、TEXT等字符串字段在等值比较("=")时,基于PAD SPACE校对规则,会忽略掉尾部的空格;
  • 在存储时,不会自动截断尾部的空格,会按原值存储;
  • 如果想要精确查询就不能用等值查询("="),而应改用LIKE或BINARY;
  • 认知有界,而求知无界。

关于作者

杜云杰,高级架构师,转转架构部负责人,转转技术委员会执行主席,腾讯云TVP。负责服务治理、MQ、云平台、APM、IM、分布式调用链路追踪、监控系统、配置中心、分布式任务调度平台、分布式ID生成器、分布式锁等基础组件。

道阻且长,拥抱变化;而困而知,且勉且行。

当前标题:什么?MySQL的等值查询竟然出错了?
转载来源:http://www.36103.cn/qtweb/news0/38700.html

网站建设、网络推广公司-创新互联,是专注品牌与效果的网站制作,网络营销seo公司;服务项目有等

广告

声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联