一、事故还原
我们仍然使用学生信息表,但是我们只需要保留两个字段即可:
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 ('duduu');
#尾部带一个空格
INSERT INTO `student_info` (`name`) VALUES ('duduu ');
#尾部带两个空格
INSERT INTO `student_info` (`name`) VALUES ('duduu ');
需要注意的是,name字段值分别是:不带空格的duduu、带一个空格的duduu、带两个空格的duduu,且空格都是加在了尾部。我们查询这几条记录的name字段的长度:
mysql> select length(name) from student_info;
+--------------+
| length(name) |
+--------------+
| 5 |
| 6 |
| 7 |
+--------------+
3 rows in set (0.00 sec)
这个结果是符合预期的,MySQL检索CHAR类型的数据时,CHAR类型的字段会去除尾部的空格;检索VARCHAR类型的字段数据时,会保留数据尾部的空格,因此这里的长度分别是5、6、7。我们针对name做简单的等值查询试试:
#不带空格的duduu
mysql> select * from student_info where name='duduu';
+----+---------+
| id | name |
+----+---------+
| 1 | duduu |
| 2 | duduu |
| 3 | duduu |
+----+---------+
3 rows in set (0.00 sec)
#带一个空格
mysql> select * from student_info where name='duduu ';
+----+---------+
| id | name |
+----+---------+
| 1 | duduu |
| 2 | duduu |
| 3 | duduu |
+----+---------+
3 rows in set (0.00 sec)
#带两个空格
mysql> select * from student_info where name='duduu ';
+----+---------+
| id | name |
+----+---------+
| 1 | duduu |
| 2 | duduu |
| 3 | duduu |
+----+---------+
3 rows in set (0.00 sec)
我们会发现,无论后面带多少空格,我们始终可以把所有的用户信息都查询出来,这个并不符合我们的预期啊!!!如果我们将空格都放在最前面呢?会发生什么事情?我们来试试。删除表数据后,重新插入数据:
#前面无空格
INSERT INTO `student_info` (`name`) VALUES ('duduu');
#前面带一个空格
INSERT INTO `student_info` (`name`) VALUES (' duduu');
#前面带两个空格
INSERT INTO `student_info` (`name`) VALUES (' duduu');
此时分别计算字段字符长度:
mysql> select * from student_info;
+----+---------+
| id | name |
+----+---------+
| 1 | duduu |
| 2 | duduu |
| 3 | duduu |
+----+---------+
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)
可以看到空格放在前面和放在后面的查询结果是不一样的,这个倒不是我们关注的重点,我们关注等值查询是否可以正常查询出来:
#不带空格的duduu
mysql> select * from student_info where name='duduu';
+----+-------+
| id | name |
+----+-------+
| 1 | duduu |
+----+-------+
1 row in set (0.00 sec)
#带一个空格
mysql> select * from student_info where name=' duduu';
+----+--------+
| id | name |
+----+--------+
| 2 | duduu |
+----+--------+
1 row in set (0.00 sec)
#带两个空格
mysql> select * from student_info where name=' duduu';
+----+---------+
| id | name |
+----+---------+
| 3 | duduu |
+----+---------+
1 row in set (0.00 sec)
通过实验发现,当空格放在前面时,等值查询是没有问题的,但是空格放在后面时,等值查询并不符合我们的预期。
二、合理推测
通过上面的实验,明明是三个长度不同的字符串,空格放在前边被认为是不同,放在后边又被认为是相同,难道说:对于MySQL来说,字符串做字符串等值判断时会忽略后面的空格,duduu(不带空格)、duduu(后面带一个空格)、duduu(后面带两个空格),都被认作是不带空格的duduu了吗?既然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 ('duduu');
Query OK, 1 row affected (0.00 sec)
mysql> #尾部带一个空格
mysql> INSERT INTO `student_info` (`name`) VALUES ('duduu ');
ERROR 1062 (23000): Duplicate entry 'duduu ' for key 'uk_name'
mysql> #尾部带两个空格
mysql> INSERT INTO `student_info` (`name`) VALUES ('duduu ');
ERROR 1062 (23000): Duplicate entry 'duduu ' for key 'uk_name'
不出所料,
进行字符串等值判断时,MySQl认为duduu(不带空格)、duduu(后面带一个空格)、duduu(后面带两个空格)是重复的三个字符串,后面两条数据由于违背了唯一性,因而不可以被插入,我们去官方文档看看有没有提及这一点。
三、权威文档
官方文档链接: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
我们来实验下:
#不带空格的duduu
mysql> select * from student_info where name like 'duduu';
+----+-------+
| id | name |
+----+-------+
| 1 | duduu |
+----+-------+
1 row in set (0.00 sec)
#带一个空格
mysql> select * from student_info where name like 'duduu ';
+----+--------+
| id | name |
+----+--------+
| 2 | duduu |
+----+--------+
1 row in set (0.00 sec)
#带两个空格
mysql> select * from student_info where name like 'duduu ';
+----+---------+
| id | name |
+----+---------+
| 3 | duduu |
+----+---------+
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进行等值查询进行验证:
#不带空格的duduu
mysql> select * from student_info where name = BINARY 'duduu';
+----+-------+
| id | name |
+----+-------+
| 1 | duduu |
+----+-------+
1 row in set (0.00 sec)
#带一个空格
mysql> select * from student_info where name = BINARY 'duduu ';
+----+--------+
| id | name |
+----+--------+
| 2 | duduu |
+----+--------+
1 row in set (0.00 sec)
#带两个空格
mysql> select * from student_info where name = BINARY 'duduu ';
+----+---------+
| id | name |
+----+---------+
| 3 | duduu |
+----+---------+
1 row in set (0.00 sec)
可见BINARY也可以解决此问题。
五、总结
本文的结论如下:
-
在存储时,不会自动截断尾部的空格,会按原值存储;
-
MySQL的CHAR、VARCHAR、TEXT等字符串字段在等值比较("=")时,基于PAD SPACE校对规则,会忽略掉尾部的空格;
-
如果想要精确查询就不能用等值查询("="),而应改用LIKE或BINARY;