数据库系列之MySQL数据库Varchar类型尾部空值问题

背景：研发人员在执行SQL语句“select xx from tb where c1=’aaa ’”查询时，发现并不能只查询出’aaa ’这样的字符串，而是把所有’aaa’这样的查出来。首先不管开发人员在插入数据的时候有没有进行去掉首尾字符串的处理，在MySQL 8.0版本中查询时会忽略掉字符串最后的空格，所以导致’aaa’+空格作为查询条件时和’aaa’为同一效果。为什么会出现这样的问题？

---

1、Char和Varchar的区别

MySQL数据库中char和varchar类型主要在存储方式和性能上有区别，主要有以下：

• 存储方式：
  • CHAR：CHAR类型用于存储固定长度的字符序列。当你定义一个CHAR(M)字段时，MySQL会为该字段分配M个字符的空间，并使用空格来填充未达到长度的部分。例如，如果你有一个CHAR(10)字段并插入字串"Hello"，MySQL会存储 "Hello "（注意尾部的空格）。
  • VARCHAR：VARCHAR类型用于存储可变长度的字符序列。当你定义一个VARCHAR(M)字段时，MySQL只会为该字段分配M个字符加上一个或两个额外字节的空间（用于存储字符串的长度）。如果字符串长度小于M，则只会使用必要的空间。例如，如果你有一个VARCHAR(10)字段并插入字符串"Hello"，MySQL只会储"Hello"（没有额外的空格）。
• 性能：
  • CHAR：由于CHAR字段的长度是固定的，所以在查询和比较时通常更快，因为MySQL可以直接定位到数据的位置，而无需先确定字符串的长度。但是，如果实际数据小于定义的长度，则会浪费存储空间。
  • VARCHAR：由于VARCHAR字段的长度是可变的，所以在查询和比较时可能需要一些额外的计算来确定字符串的长度。但是，VARCHAR可以更有效地利用存储空间，因为它只使用必要的空间。
• 最大长度：
  • CHAR和VARCHAR的最大长度都受MySQL版本和字符集的影响。在大多数MySQL版本中，CHAR的最大长度是255个字符，而VARCHAR的最大长度是65,535个字节（这取决于实际使用的字符集，因为每个字符可能占用不同的字节数）。
• 默认值：
  • 对于CHAR字段，如果没有明确指定默认值，MySQL会自动为其分配一个空格字符串作为默认值。
  • 对于VARCHAR字段，如果没有明确指定默认值，则其默认值为NULL。
• 尾部空格：
  • 当从CHAR字段检索数据时，MySQL会删除尾部的空格（SQL Mode未配置PAD_CHAR_TO_FULL_LENGTH）。但是，在比较操作中，尾部的空格是会被考虑的。
  • VARCHAR字段在存储和检索时都保留实际的字符串内容，包括任何尾部的空格。

在官网上对于char和varchar解释如下：

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.
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

下表列出不同字符串和长度定义在char和varchar类型下存储大小：

2、Varchar对于尾部空格的处理

从上述得知，char类型查询时会忽略尾部空格，varchar其实也有类似的规则，从官网得知：

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。从官网得知两个属性的不同：

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:

1. For PAD SPACE collations, trailing spaces are insignificant in comparisons; strings are compared without regard to trailing spaces.
2. NO PAD collations treat trailing spaces as significant in comparisons, like any other character.

不同属性在查询时候表现也是不同的，如下所示：

mysql> SELECT COLLATION_NAME, PAD_ATTRIBUTE
       FROM INFORMATION_SCHEMA.COLLATIONS
       WHERE COLLATION_NAME LIKE 'utf8mb4%bin';
+------------------+---------------+
| COLLATION_NAME   | PAD_ATTRIBUTE |
+------------------+---------------+
| utf8mb4_bin      | PAD SPACE     |
| utf8mb4_0900_bin | NO PAD        |
+------------------+---------------+
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 |
+------------+

也就是说在UCA 9.0.0以后版本name_varchar的排序规则为NO PAD后，尾部空格参与比较，在这之前比较时忽略的尾部的空格。另外在mysql 8.x版本中，排序规则保存在information_schema库的COLLATIONS表中，可以通过以下语句查询对应的pad属性值。

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)

3、如何临时规避该问题

当字符集排序规则没法修改的情况下，如何规避CHAR和VARCHAR值进行比较都忽略尾部空格的问题，可以使用length函数的方法，如下所示：

select * from table where c1='xxx ' and length(c1) = length('xxx ');

---

参考资料：

1. https://dev.mysql.com/doc/refman/8.0/en/char.html
2. https://www.jb51.net/article/243476.htm