10｜第十话：基础篇- MySQL字符串等值查询的一个有趣现象

《09｜第九话：基础篇-MySQL之初级查询》中学习了各种初级查询语法，本篇文章介绍一个关于查询的有趣现象，准备好小板凳和瓜子饮料，我们坐过来看到底发生了什么事情。

一、事故还原

我们仍然使用学生信息表，但是我们只需要保留两个字段即可：

CREATE TABLE `student_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '姓名',  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生基本信息表';

先插入三条数据备用：

#尾部无空格
INSERT INTO `student_info` (`name`) VALUES ('fossi');
#尾部带一个空格
INSERT INTO `student_info` (`name`) VALUES ('fossi ');
#尾部带两个空格
INSERT INTO `student_info` (`name`) VALUES ('fossi  ');

需要注意的是，name字段值分别是：不带空格的fossi、带一个空格的fossi、带两个空格的fossi，且空格都是加在了尾部。

我们查询这几条记录的name字段的长度：

mysql> select length(name) from student_info;
+--------------+
| length(name) |
+--------------+
|            5 |
|            6 |
|            7 |
+--------------+
3 rows in set (0.00 sec)

这个结果是符合预期的，我们在《07｜第七话：基础篇-MySQL数据类型之字符串类型》中得出并验证了此结论：MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格；检索VARCHAR类型的字段数据时，会保留数据尾部的空格，因此这里的长度分别是5、6、7。

我们针对name做简单的等值查询试试：

#不带空格的fossi
mysql> select * from student_info where name='fossi';
+----+---------+
| id | name    |
+----+---------+
|  1 | fossi   |
|  2 | fossi   |
|  3 | fossi   |
+----+---------+
3 rows in set (0.00 sec)

#带一个空格
mysql> select * from student_info where name='fossi ';
+----+---------+
| id | name    |
+----+---------+
|  1 | fossi   |
|  2 | fossi   |
|  3 | fossi   |
+----+---------+
3 rows in set (0.00 sec)

#带两个空格
mysql> select * from student_info where name='fossi  ';
+----+---------+
| id | name    |
+----+---------+
|  1 | fossi   |
|  2 | fossi   |
|  3 | fossi   |
+----+---------+
3 rows in set (0.00 sec)

我们会发现，无论后面带多少空格，我们始终可以把所有的用户信息都查询出来，这个并不符合我们的预期啊！！！

如果我们将空格都放在最前面呢？会发生什么事情？我们来试试。删除表数据后，重新插入数据：

#前面无空格
INSERT INTO `student_info` (`name`) VALUES ('fossi');
#前面带一个空格
INSERT INTO `student_info` (`name`) VALUES (' fossi');
#前面带两个空格
INSERT INTO `student_info` (`name`) VALUES ('  fossi');

此时分别计算字段字符长度：

mysql> select * from student_info; 
+----+---------+
| id | name    |
+----+---------+
|  1 | fossi   |
|  2 |  fossi  |
|  3 |   fossi |
+----+---------+
3 rows in set (0.00 sec)

mysql> select length(name) from student_info;
+--------------+
| length(name) |
+--------------+
|            5 |
|            6 |
|            7 |
+--------------+
3 rows in set (0.00 sec)

可以看到空格放在前面和放在后面的查询结果是不一样的，这个倒不是我们关注的重点，我们关注等值查询是否可以正常查询出来：

#不带空格的fossi
mysql> select * from student_info where name='fossi';
+----+-------+
| id | name  |
+----+-------+
|  1 | fossi |
+----+-------+
1 row in set (0.00 sec)

#带一个空格
mysql> select * from student_info where name=' fossi';
+----+--------+
| id | name   |
+----+--------+
|  2 |  fossi |
+----+--------+
1 row in set (0.00 sec)

#带两个空格
mysql> select * from student_info where name='  fossi';
+----+---------+
| id | name    |
+----+---------+
|  3 |   fossi |
+----+---------+
1 row in set (0.00 sec)

通过实验发现，当空格放在前面时，等值查询是没有问题的，但是空格放在后面时，等值查询并不符合我们的预期。

二、合理推测

通过上面的实验，明明是三个长度不同的字符串，空格放在前边被认为是不同，放在后边又被认为是相同，难道说：对于MySQL来说，字符串做字符串等值判断时会忽略后面的空格，fossi（不带空格）、fossi（后面带一个空格）、fossi（后面带两个空格），都被认作是不带空格的fossi了吗？

既然MySQL认为这三个字符串是一个，那么我们看看给name增加唯一索引时，插入这几条数据会不会报错。

我们索性重新准备学生信息表：

CREATE TABLE `student_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '姓名',
  UNIQUE KEY uk_name (name),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生基本信息表';

此时插入数据：

mysql> #尾部无空格
mysql> INSERT INTO `student_info` (`name`) VALUES ('fossi');
Query OK, 1 row affected (0.00 sec)

mysql> #尾部带一个空格
mysql> INSERT INTO `student_info` (`name`) VALUES ('fossi ');
ERROR 1062 (23000): Duplicate entry 'fossi ' for key 'uk_name'
mysql> #尾部带两个空格
mysql> INSERT INTO `student_info` (`name`) VALUES ('fossi  ');
ERROR 1062 (23000): Duplicate entry 'fossi  ' for key 'uk_name'

不出所料，进行字符串等值判断时，MySQl认为fossi（不带空格）、fossi（后面带一个空格）、fossi（后面带两个空格）是重复的三个字符串，后面两条数据由于违背了唯一性，因而不可以被插入，我们去官方文档看看有没有提及这一点。

三、权威文档

官方文档链接：https://dev.mysql.com/doc/refman/5.7/en/char.html

有提到这样的描述：

MySQL的校对规则基于PAD SPACE，这就意味着CHAR、VARCHAR、TEXT等字符串的等值比较（“=”）会忽略掉尾部的空格。但是这个规则不包括LIKE这样的模糊查询语句。

图中代码示例说明的是使用=这样的等值查询时会自动忽略后面的空格，’Jones’和’Jones ‘都可以被查询到，但是Like需要精准匹配上空格才行。

四、解决思路

既然=查询会自动忽略后面的空格，如何解决呢？下面有两个思路。

🧷思路一：LIKE

如上所提到，官方文档给出了一个思路，即使用like。

LIKE是基于逐个字符进行比较的，这样就不会忽略尾部的空格，官方文档有所提及：https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

我们来实验下：

#不带空格的fossi
mysql> select * from student_info where name like 'fossi';
+----+-------+
| id | name  |
+----+-------+
|  1 | fossi |
+----+-------+
1 row in set (0.00 sec)

#带一个空格
mysql> select * from student_info where name like 'fossi ';
+----+--------+
| id | name   |
+----+--------+
|  2 | fossi  |
+----+--------+
1 row in set (0.00 sec)

#带两个空格
mysql> select * from student_info where name like 'fossi  ';
+----+---------+
| id | name    |
+----+---------+
|  3 | fossi   |
+----+---------+
1 row in set (0.00 sec)

这里可以得到两个结论：

LIKE后面的字段可以不带通配符；
LIKE是基于逐个字符进行比较的，这样就不会忽略尾部的空格

🧷思路二：BINARY

BINARY不是函数，是类型转换运算符，它用来强制它后面的字符串转为二进制字节，再逐个字节比较，也可以理解成精确匹配，官网上面有这样的描述：

https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#operator_binary

可以看到，空格在BINARY转换后并不会被忽略，那么我们再使用BINARY进行等值查询进行验证：

#不带空格的fossi
mysql> select * from student_info where name = BINARY 'fossi';
+----+-------+
| id | name  |
+----+-------+
|  1 | fossi |
+----+-------+
1 row in set (0.00 sec)

#带一个空格
mysql> select * from student_info where name = BINARY 'fossi ';
+----+--------+
| id | name   |
+----+--------+
|  2 | fossi  |
+----+--------+
1 row in set (0.00 sec)

#带两个空格
mysql> select * from student_info where name = BINARY 'fossi  ';
+----+---------+
| id | name    |
+----+---------+
|  3 | fossi   |
+----+---------+
1 row in set (0.00 sec)