MySQL尾部空格处理与哪些设置有关?

2023-07-20 16:14 由潇湘隐者发表于 #数据库

在之前的这篇文章Oracle/MySQL/PG/SQL Server关系数据库中NULL与空字符串的区别^[1]中，简单对比、介绍了一下MySQL的NULL/空格/尾部空格处理方式，主要对比了NULL与长度为空的字符串，其实很多地方没有分析到位就一笔带过了。这篇文章重新来细说一下MySQL的尾部空格处理方式。在MySQL中，有几个因素会影响MySQL如何处理空格。这里简单浅析一下.

MySQL的排序规则有一个属性Pad Attributes属性，这个属性的设置会影响数据库如何处理尾部空格（是否忽略尾部空格），如下官方文档^[2]描述

Collation Pad Attributes

Collations based on UCA 9.0.0 and higher are faster than collations based on UCA versions prior to 9.0.0. They also have a pad attribute of NO PAD, in contrast to PAD SPACE as used in collations based on UCA versions prior to 9.0.0. For comparison of nonbinary strings, NO PAD collations treat spaces at the end of strings like any other character (see Trailing Space Handling in Comparisons).

Comparison of nonbinary string values (CHAR, VARCHAR, and TEXT) that have a NO PAD collation differ from other collations with respect to trailing spaces. For example, 'a' and 'a ' compare as different strings, not the same string. This can be seen using the binary collations for utf8mb4. The pad attribute for utf8mb4_bin is PAD SPACE, whereas for utf8mb4_0900_bin it is NO PAD. Consequently, operations involving utf8mb4_0900_bin do not add trailing spaces, and comparisons involving strings with trailing spaces may differ for the two collations

官方文档^[3]，关于比较中尾部空格处理介绍如下：

Trailing Space Handling in Comparisons
MySQL collations have a pad attribute, which has a value of PAD SPACE or NO PAD:
• Most MySQL collations have a pad attribute of PAD SPACE.
• The Unicode collations based on UCA 9.0.0 and higher have a pad attribute of NO PAD; see Section 10.10.1, “Unicode Character Sets”.
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.
The differing behaviors can be demonstrated using the two utf8mb4 binary collations, one of which is PAD SPACE, the other of which is NO PAD.

我们先来看看PAD SPACE与NO PAD的具体意义：

PAD SPACE：在排序和比较运算中，忽略字符串尾部空格。
NO PAD：在排序和比较运算中，字符串尾部空格当成普通字符，不能忽略。

SELECT * 
FROM INFORMATION_SCHEMA.COLLATIONS
       WHERE CHARACTER_SET_NAME = 'utf8mb4';

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT * 
    -> FROM INFORMATION_SCHEMA.COLLATIONS
    ->        WHERE CHARACTER_SET_NAME = 'utf8mb4'
    ->   AND COLLATION_NAME IN('utf8mb4_general_ci','utf8mb4_0900_ai_ci');
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |
| utf8mb4_general_ci | utf8mb4            |  45 |            | Yes         |       1 | PAD SPACE     |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
2 rows in set (0.00 sec)

mysql>

下面我们来测试演练一下，首先新建一个测试数据库gsp

CREATE DATABASE IF NOT EXISTS gsp   
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;

mysql> create table test(id int not null, name varchar(16));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(id , name) values(1, null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id , name) values(2, '');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(id , name) values(3, ' '); -- 包含一个空格
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id , name) values(4, '  ');-- 包含两个空格
Query OK, 1 row affected (0.00 sec)

mysql> 


#length获取字符串字节长度的内置函数，
#char_length获取字符串长度的内置函数
#hex将字符或数字转换为十六进制格式
mysql> select id, length(name), char_length(name), hex(name) from test;
+----+--------------+-------------------+-----------+
| id | length(name) | char_length(name) | hex(name) |
+----+--------------+-------------------+-----------+
|  1 |         NULL |              NULL | NULL      |
|  2 |            0 |                 0 |           |
|  3 |            1 |                 1 | 20        |
|  4 |            2 |                 2 | 2020      |
+----+--------------+-------------------+-----------+
4 rows in set (0.00 sec)

mysql> select * from test where name='';
+----+------+
| id | name |
+----+------+
|  2 |      |
|  3 |      |
|  4 |      |
+----+------+
3 rows in set (0.00 sec)

mysql>

mysql> select * from test where name=' '; -- 包含一个空格
+----+------+
| id | name |
+----+------+
|  2 |      |
|  3 |      |
|  4 |      |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from test where name='  '; -- 包含两个空格

+----+------+
| id | name |
+----+------+
|  2 |      |
|  3 |      |
|  4 |      |
+----+------+
3 rows in set (0.00 sec)

mysql>

官方文档中也要一个例子简单说明，两者比较时，如何处理尾部空格。如下所示，相当直观、明了：

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          1 |
+------------+
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+

我们上面的例子中，gsp数据库的排序规则为utf8mb4_general_ci，它的Pad Attributes属性为PAD SPACE，在排序和比较运算中，数据库将忽略字符串尾部空格，所以上面几个SQL的查询结果是一样的。如果你数据库的排序规则选择utf8mb4_0900_ai_ci，那么Pad Attributes属性为NO PAD，此时,在排序和比较运算中，字符串尾部空格当成普通字符处理，不能忽略.我们知道MySQL的排序规则分为服务器级别，数据库级别、表级别、列级别、SQL语句级别。优先级顺序为：SQL语句级别>列级别>表级别>数据库级别>服务器级别。你可以重新建一个数据库，设置数据库排序规则为utf8mb4_0900_ai_ci，也可以修改其他级别的排序规则...。这里我们就直接修改表的列级别排序规则。脚本如下所示：

drop table test;
create table test(id int not null, name varchar(16) collate utf8mb4_0900_ai_ci );
insert into test(id , name) values(1, null);
insert into test(id , name) values(2, '');
insert into test(id , name) values(3, ' '); -- 包含一个空格
insert into test(id , name) values(4, '  ');-- 包含两个空格

然后，我们测试一下，如下所示，此时由于数据库将字符串尾部空格当成普通字符，所以此时的查询结果就不同了。如下所示

mysql> select * from test where name=''; -- 长度为零的空字符串
+----+------+
| id | name |
+----+------+
|  2 |      |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test where name=' '; -- 含一个空格
+----+------+
| id | name |
+----+------+
|  3 |      |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test where name='  ';-- 包含两个空格
+----+------+
| id | name |
+----+------+
|  4 |      |
+----+------+
1 row in set (0.00 sec)

mysql>

另外，我们这里测试的是VARCHAR类型，如果字段类型为CHAR呢？其实呢，对于CHAR类型和VARCHA类型，它们的存储略有区别：

CHAR(N)：当插入的字符数小于N，它会在字符串的右边补充空格，直到总字符数达到N再进行存储；当查询返回数据时默认会将字符串尾部的空格去掉，除非SQL_MODE设置PAD_CHAR_TO_FULL_LENGTH。

VARCHAR(N)：当插入的字符数小于N，它不会在字符串的右边补充空格，insert内容原封不动的进行存储；如果原本字符串右边有空格，在存储和查询返回时都会保留空格

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

PAD_CHAR_TO_FULL_LENGTH

By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval.

Note: As of MySQL 8.0.13, PAD_CHAR_TO_FULL_LENGTH is deprecated. Expect it to be removed in a future version of MySQL.

注意事项：

PAD_CHAR_TO_FULL_LENGTH只影响CHAR类型，不影响VARCHAR类型。
MySQL 8.0.13后，PAD_CHAR_TO_FULL_LENGTH参数过时/废弃了。这个参数可能在后续的MySQL版本中被移除。

当前版本（MySQL 8.0.33）中，暂时还可以在SQL_MODE中设置这个参数，不过默认不会设置此参数。那么我们来测试验证一下：

drop table test;
create table test(id int not null, name char(16)  );
insert into test(id , name) values(1, null);
insert into test(id , name) values(2, '');
insert into test(id , name) values(3, ' '); -- 包含一个空格
insert into test(id , name) values(4, '  ');-- 包含两个空格

mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

mysql> --如下所示，SQL_MODE没有设置PAD_CHAR_TO_FULL_LENGTH时，查询返回数据时默认会将字符串尾部的空格去掉，所以你看到长度为零
mysql> select id, length(name), char_length(name),hex(name) from test;
+----+--------------+-------------------+-----------+
| id | length(name) | char_length(name) | hex(name) |
+----+--------------+-------------------+-----------+
|  1 |         NULL |              NULL | NULL      |
|  2 |            0 |                 0 |           |
|  3 |            0 |                 0 |           |
|  4 |            0 |                 0 |           |
+----+--------------+-------------------+-----------+
4 rows in set (0.00 sec)

mysql>

那么我们手工设置一下当前会话的SQL_MODE,然后对比测试一下：

mysql> set sql_mode='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show variables like 'sql_mode';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| sql_mode      | PAD_CHAR_TO_FULL_LENGTH |
+---------------+-------------------------+
1 row in set (0.01 sec)

mysql> select id, length(name), char_length(name),hex(name) from test;
+----+--------------+-------------------+----------------------------------+
| id | length(name) | char_length(name) | hex(name)                        |
+----+--------------+-------------------+----------------------------------+
|  1 |         NULL |              NULL | NULL                             |
|  2 |           16 |                16 | 20202020202020202020202020202020 |
|  3 |           16 |                16 | 20202020202020202020202020202020 |
|  4 |           16 |                16 | 20202020202020202020202020202020 |
+----+--------------+-------------------+----------------------------------+
4 rows in set (0.00 sec)

mysql>

通过上面的分析讲述，我们知道当数据库的排序规则的Pad Attributes属性为NO PAD时，此时SQL_MODE的PAD_CHAR_TO_FULL_LENGTH设置与否将会影响查询结果。我们新建一个gsp2数据库，数据库排序规则为utf8mb4_0900_ai_ci，下面我们通过实验对比一下就知道了：

mysql> use gsp2;
Database changed
mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

-- 对于CHAR类型，查询返回数据时默认会将字符串尾部的空格去掉，所以name=''会得到三条记录
mysql> select * from test where name='';
+----+------+
| id | name |
+----+------+
|  2 |      |
|  3 |      |
|  4 |      |
+----+------+
3 rows in set (0.01 sec)

mysql> select * from test where name=' ';
Empty set (0.00 sec)

mysql> select * from test where name='  ';
Empty set (0.00 sec)

mysql> set sql_mode='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: PAD_CHAR_TO_FULL_LENGTH
1 row in set (0.01 sec)
-- SQL_MODE设置PAD_CHAR_TO_FULL_LENGTH。查询返回数据时，字符串尾部的空格不会去掉，此时，name字段时16个空格，故而下面查询条件查不到数据。
mysql>  select * from test where name='';
Empty set (0.01 sec)

mysql> select * from test where name=' ';
Empty set (0.00 sec)

mysql> select * from test where name='  ';
Empty set (0.00 sec)

mysql>

总结

关于MySQL的尾部空格是否忽略，以及对查询结果的影响，既跟数据库的排序规则有关（确切来说，是跟数据库排序规则的Pad Attributes有关），其实还跟字符类型和SQL_MODE是否设置PAD_CHAR_TO_FULL_LENGTH有关。

参考资料

[1]

1: https://mp.weixin.qq.com/s?__biz=Mzg2OTAwMTE3NQ==&mid=2247488201&idx=1&sn=ff31b7d0f824992c77ff432a9013a42f&chksm=cea2e071f9d5696777621e1122dcd619d4b734f2d28ca8ce25c96b316cc6bc95a1849c04367d#rd

[2]

2: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html

[3]

3: https://dev.mysql.com/doc/refman/8.0/en/charset-binary-collations.html