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

逃离我推掉我的手 2023-10-01 23:43 15阅读 0赞

*  1.问题背景
 *  2.验证
    
     *  2.1 数据准备
     *  2.2 问题验证
     *  2.3 验证小结
 *  3.分析原因
 *  4.精确查询的方法
    
     *  4.1 LIKE
     *  4.2 BINARY
 *  5.总结

### 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 '名字',
     
      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 '三个用户，结果如下图所示（红框圈起来的表示我们认为不应该出现的异常结果）：

![10793fe1bd8c0080b6f2a877117ba0d4.png][]

太神奇了！

2.2.2 头部空格验证

那如果把空格放在前面呢？再来一把，结果如下：

![6b68fc968e97e0bcba6d0f336f44c194.png][]

这下又匹配不上了。空格放在后边可以，放在前边不可以，这太神奇了！！

2.2.3 唯一索引验证

那如果在user\_name字段上建唯一索引，还能插入这三条记录吗？再来一把，结果如下：

![d7bed72ecb807f076ddfcbf8ac623982.png][]

也不行，被唯一索引约束住了。

2.2.4 长度验证

那这三条记录的user\_name长度又分别是多少呢？

![84cf2da754d8e3291331353b84d0621f.png][]

确实长度也不一样。

#### 2.3 验证小结 ####

从结果上来看，明明是三个长度不同的字符串，空格放在前边被认为是不同，放在后边又被认为是相同，而且唯一索引也冲突。 我们有充足的理由怀疑MySQL忽略字符串尾部的空格，把'adu'、'adu '、'adu '都当成'adu'来处理 。这确实超出了已有的认知，那背后的原因究竟又是什么呢？

### 3.分析原因 ###

查询MySQL的 官方文档 \[1\] ，原来跟字符串的 **校对规则** 有关。

![77ca99b43b69194a236b7e4187ace605.png][]

原来MySQL的校对规则基于 `PAD SPACE` ，这就意味着CHAR、VARCHAR、TEXT等字符串的等值比较（“=”）会忽略掉尾部的空格，而且官网也说了， **适用于所有MySQL版本，并且不会改变** 。这。。。

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

![522d7f0ae95593f12e08a4dc7a8db14b.png][]

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

### 4.精确查询的方法 ###

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

#### 4.1 LIKE ####

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

![da4b7e0e245f012d4891d7e45256ca20.png][]

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

![18577547870cadaae7799d995732487b.png][]

#### 4.2 BINARY ####

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

![b2e94e98015925de54c8309e348343b1.png][]

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

![9fd802b9b179350dae547203de1dc33f.png][]

### 5.总结 ###

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

[10793fe1bd8c0080b6f2a877117ba0d4.png]: https://img-blog.csdnimg.cn/img_convert/10793fe1bd8c0080b6f2a877117ba0d4.png
[6b68fc968e97e0bcba6d0f336f44c194.png]: https://img-blog.csdnimg.cn/img_convert/6b68fc968e97e0bcba6d0f336f44c194.png
[d7bed72ecb807f076ddfcbf8ac623982.png]: https://img-blog.csdnimg.cn/img_convert/d7bed72ecb807f076ddfcbf8ac623982.png
[84cf2da754d8e3291331353b84d0621f.png]: https://img-blog.csdnimg.cn/img_convert/84cf2da754d8e3291331353b84d0621f.png
[77ca99b43b69194a236b7e4187ace605.png]: https://img-blog.csdnimg.cn/img_convert/77ca99b43b69194a236b7e4187ace605.png
[522d7f0ae95593f12e08a4dc7a8db14b.png]: https://img-blog.csdnimg.cn/img_convert/522d7f0ae95593f12e08a4dc7a8db14b.png
[da4b7e0e245f012d4891d7e45256ca20.png]: https://img-blog.csdnimg.cn/img_convert/da4b7e0e245f012d4891d7e45256ca20.png
[18577547870cadaae7799d995732487b.png]: https://img-blog.csdnimg.cn/img_convert/18577547870cadaae7799d995732487b.png
[b2e94e98015925de54c8309e348343b1.png]: https://img-blog.csdnimg.cn/img_convert/b2e94e98015925de54c8309e348343b1.png
[9fd802b9b179350dae547203de1dc33f.png]: https://img-blog.csdnimg.cn/img_convert/9fd802b9b179350dae547203de1dc33f.png