对于第一条原因只能说是开发时疏漏，没什么可说的，我们着重了解下第二条，为什么 MySQL 会忽略掉查询条件最后的空格。本文基于 MySQL 8.0.28，文章中有些内容是 MySQL 8.0 新增的，但主体也适用于 5.x 版本。
在探究之前我们需要准备下使用的数据库，毕竟实践出来的结果才是真实的，首先我们准备一个测试使用的数据库和表，结构如下，字符集和排序规则先选择比较常用的 utf8mb4 和 utf8mb4_unicode_ci，之后在表里插入两条数据：
第一行好理解，你存进去的时候没带空格，数据库自己填充上了空格，总不能查出来的结果还变了吧；第二行则是入库的时候字符串最后的字符和数据库填充的字符是同一种，查询的时候数据库怎么分得清是你自己填的还是它填的呢，直接一刀切。而 varchar 类型因为不会被填充，所以查询结果中完成的保留下了尾部空格。
varchar 对于尾部空格的处理
上节了解过 char 类型查询时会忽略尾部空格，但是在实际使用中发现 varchar 也有类似的规则，在查看文档时发现有以下一段内容，摘自【11.3.2 The CHAR and VARCHAR Types】
Values in CHAR, VARCHAR, and TEXT columns are sorted and compared according to the character set collation assigned to the column.
MySQL collations have a pad attribute of PAD SPACE, other than Unicode collations based on UCA 9.0.0 and higher, which have a pad attribute of NO PAD.
根据这一段描述，我们可以得知 char、varchar 和 text 内容的排序和比较过程受排序规则影响，在 UCA 9.0.0 之前 pad 属性默认为 PAD SPACE，而之后的默认属性为 NO PAD。
在官方文档中可以找到以下说明，摘自【Trailing Space Handling in Comparisons】
For nonbinary strings (CHAR, VARCHAR, and TEXT values), the string collation pad attribute determines treatment in comparisons of trailing spaces at the end of strings:
-
For PAD SPACE collations, trailing spaces are insignificant in comparisons; strings are compared without regard to trailing spaces.
-
NO PAD collations treat trailing spaces as significant in comparisons, like any other character.
这一段主要描述 char、varchar 和 text 类型在比较时，如果排序规则的 pad 属性为 PAD SPACE 则会忽略尾部空格，NO PAD 属性则不会，而这正解释了最初的问题。我们通过修改列的排序规则验证以下，首先看一下当前使用 PAD SPACE 时的查询结果。
mysql> show full columns from test;
+————–+————-+——————–+——+—–+———+——-+———————————+———+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+————–+————-+——————–+——+—–+———+——-+———————————+———+
| id | int | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name_char | char(20) | utf8mb4_unicode_ci | YES | | NULL | | select,insert,update,references | |
| name_varchar | varchar(20) | utf8mb4_unicode_ci | YES | | NULL | | select,insert,update,references | |
+————–+————-+——————–+——+—–+———+——-+———————————+———+
3 rows in set (0.01 sec)
mysql> select * from test where name_varchar = ‘varchar2’;
+—-+———–+—————+
| id | name_char | name_varchar |
+—-+———–+—————+
| 2 | char2 | varchar2 |
+—-+———–+—————+
1 row in set (0.01 sec)
可以看到在 PAD SPACE 属性下可以通过varchar2查询到varchar2，说明比较时忽略的尾部的空格，我们将 name_varchar 的排序规则切换为 UCA 9.0.0 以后版本再来看一下结果。
mysql> ALTER TABLE test CHANGE name_varchar name_varchar VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show full columns from test;
+————–+————-+——————–+——+—–+———+——-+———————————+———+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+————–+————-+——————–+——+—–+———+——-+———————————+———+
| id | int | NULL | NO | PRI | NULL | | select,insert,update,references | |
| name_char | char(20) | utf8mb4_unicode_ci | YES | | NULL | | select,insert,update,references | |
| name_varchar | varchar(20) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | |
+————–+————-+——————–+——+—–+———+——-+———————————+———+
3 rows in set (0.01 sec)
mysql> select * from test where name_varchar = ‘varchar2’;
Empty set (0.00 sec)
与预期一样，切换排序规则后，尾部空格参与比较，已经不能通过varchar2查询到varchar2了。
确定排序规则的 pad 属性
那接下来的问题是如何判断当前的排序规则是基于 UCA 9.0.0 之前还是之后的版本呢？其实在 mysql 8.x 版本中，排序规则保存在 information_schema 库的 COLLATIONS 表中，可以通过以下语句查询对应的 pad 属性值，例如我们一开始选择的 utf8mb4_unicode_ci。
mysql> select collation_name, pad_attribute from information_schema.collations where collation_name = ‘utf8mb4_unicode_ci’;
+——————–+—————+
| collation_name | pad_attribute |
+——————–+—————+
| utf8mb4_unicode_ci | PAD SPACE |
+——————–+—————+
1 row in set (0.00 sec)
除了查询数据库以外，还可以通过排序规则的名称进行区别，在官方文档中有以下一段描述，摘自【Unicode Collation Algorithm (UCA) Versions】
MySQL implements the _unicode_ci collations according to the Unicode Collation Algorithm (UCA) described at http://www.unicode.org/reports/tr10/. The collation uses the version-4.0.0 UCA weight keys: http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt. The _unicode_ci collations have only partial support for the Unicode Collation Algorithm.
Unicode collations based on UCA versions higher than 4.0.0 include the version in the collation name. Examples:
-
utf8mb4_unicode_520_ci is based on UCA 5.2.0 weight keys (http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt),
-
utf8mb4_0900_ai_ci is based on UCA 9.0.0 weight keys (http://www.unicode.org/Public/UCA/9.0.0/allkeys.txt).
可以看出，名称类似 _unicode_ci 的排序规则是基于 UCA 4.0.0 的，而 _520_ci 是基于 UCA 5.2.0，_0900_ci 是基于 UCA 9.0.0 的。通过查询数据库验证，排序规则中包含 0900 字样的 pad 属性均为 NO PAD，符合以上描述。
需要注意的是 binary 排序规则的 pad 属性为 NO PAD，这里其实不是个例外，因为 char、varchar 和 text 类型都归类为nonbinary。
到此这篇关于MySQLVarchar类型尾部空格的文章就介绍到这了,更多相关MySQLVarchar类型尾部空格内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
