技术分享 | check(col_name＜＞‘‘)为何把空格拒之门外
1、问题描述
前两天在群里看到同事反馈一个空格问题，大致现象如下：
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)mysql> create table t1(-> c1 int,-> c2 varchar(4) check(c2<>'') #单引号之间无空格-> )engine=innodb;
Query OK, 0 rows affected (0.21 sec)mysql> insert into t1 select 1,' '; #c2字段插入两个空格
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.check定义c2<>''，往c2字段插入空格，提示违反check约束。
为什么insert语句中的' '(单引号之间有一个或多个空格)会被判断为''(单引号之间无空格)，导致插入失败？
2、涉及知识
2.1、Stored and Retrieved
https://dev.mysql.com/doc/refman/8.0/en/char.html
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.
VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.
CHAR(N)：当插入的字符数小于N，它会在字符串的右边补充空格，直到总字符数达到N再进行存储；当查询返回数据时默认会将字符串尾部的空格去掉，除非SQL_MODE设置PAD_CHAR_TO_FULL_LENGTH(手册显示8.0.13 deprecated，8.0.25还能使用)。
VARCHAR(N)：当插入的字符数小于N，它不会在字符串的右边补充空格，insert内容原封不动的进行存储；如果原本字符串右边有空格，在存储和查询返回时都会保留空格。
2.2、Collation Pad Attribute
https://dev.mysql.com/doc/refman/8.0/en/char.html
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字段，排序和比较运算依赖字段上的Collation，Collation的Pad属性控制字符串尾部空格处理方式。 可以通过INFORMATION_SCHEMA.COLLATIONS表，查看Collation所使用的Pad属性：
mysql> select collation_name,pad_attribute from information_schema.collations;
+----------------------------+---------------+
| collation_name | pad_attribute |
+----------------------------+---------------+
| armscii8_general_ci | PAD SPACE |
...
| utf8mb4_0900_bin | NO PAD |
+----------------------------+---------------+
272 rows in set (0.01 sec)2.3、Trailing Space Handling in Comparisons
https://dev.mysql.com/doc/refman/8.0/en/charset-binary-collations.html#charset-binary-collations-trailing-space-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.
"Comparison" in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant, regardless of collation.
PAD SPACE：在排序和比较运算中，忽略字符串尾部空格。 NO PAD：在排序和比较运算中，字符串尾部空格当成普通字符，不能忽略。
3、问题解决
以下操作基于MySQL 8.0.25 社区版
3.1、查看字段使用的Collation
mysql> show full fields in t1;
+-------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| c1 | int | NULL | YES | | NULL | | select,insert,update,references | |
| c2 | varchar(4) | utf8mb4_unicode_ci | YES | | NULL | | select,insert,update,references | |
+-------+------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)c2列的Collation是utf8mb4_unicode_ci。
3.2、查看Collation的Pad属性
mysql> select COLLATION_NAME,PAD_ATTRIBUTE from INFORMATION_SCHEMA.COLLATIONS where COLLATION_NAME in('utf8mb4_unicode_ci','utf8mb4_0900_ai_ci');
+--------------------+---------------+
| COLLATION_NAME | PAD_ATTRIBUTE |
+--------------------+---------------+
| utf8mb4_0900_ai_ci | NO PAD |
| utf8mb4_unicode_ci | PAD SPACE |
+--------------------+---------------+
1 row in set (0.00 sec)utf8mb4_unicode_ci的Pad属性是PAD SPACE，由2.3可知c2列在排序和比较运算中，忽略字符串尾部空格。
因此check比较时，会将插入的' '中的空格忽略，显然忽略空格后和check约束存在冲突，插入失败。
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)mysql> select ' ' = '';
+--------+
| ' '='' |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)3.3、如何让check约束按常规逻辑生效
这里的常规是指空格就是空格，不应该把空格忽略。只需将c2字段修改为NO PAD的Collation后，就能将空格正常插入：
mysql> insert into t1 select 1,' '; #c2字段插入两个空格
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.mysql> alter table t1 modify c2 varchar(4) collate utf8mb4_0900_ai_ci; #修改为NO PAD的Collation
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> insert into t1 select 1,' '; #c2字段插入两个空格
Query OK, 1 row affected (0.12 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into t1 select 1,''; #''之间无空格
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.mysql> select c1,c2,hex(c2) from t1;
+------+------+---------+
| c1 | c2 | hex(c2) |
+------+------+---------+
| 1 | | 2020 |
+------+------+---------+
1 row in set (0.01 sec)4、扩展
4.1、如果c2列是CHAR类型，和前面的问题表现一样吗
一样。CHAR、VARCHAR、TEXT在做排序和比较运算时，都是依据列的Collation的Pad属性处理字符串尾部的空格。此时拿来做比较运算的字符串是insert中的内容。
4.2、WHERE条件中表现形式是怎样的
创建一张新表并插入数据
mysql> create table t3(-> c1 int,-> c2 char(4) collate utf8mb4_unicode_ci,-> c3 char(4) collate utf8mb4_0900_ai_ci,-> c4 varchar(4) collate utf8mb4_unicode_ci,-> c5 varchar(4) collate utf8mb4_0900_ai_ci-> )engine=innodb;
Query OK, 0 rows affected (0.29 sec)mysql> insert into t3 select 1,'a','a','a','a';
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into t3 select 2,'a ','a ','a ','a '; #各列包含1个空格
Query OK, 1 row affected (0.20 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into t3 select 3,'a ','a ','a ','a '; #前两列3个空格，后两列2个空格
Query OK, 1 row affected (0.17 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into t3 select 4,'a ','a ','a ','a '; #前两列2个空格，后两列3个空格
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0观察WHERE条件返回结果，CHAR类型的返回受PAD_CHAR_TO_FULL_LENGTH影响(参考2.1)
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)mysql> select *,hex(c2),hex(c3),hex(c4),hex(c5) from t3 where c2='a';
+------+------+------+------+------+---------+---------+----------+----------+
| c1 | c2 | c3 | c4 | c5 | hex(c2) | hex(c3) | hex(c4) | hex(c5) |
+------+------+------+------+------+---------+---------+----------+----------+
| 1 | a | a | a | a | 61 | 61 | 61 | 61 |
| 2 | a | a | a | a | 61 | 61 | 6120 | 6120 |
| 3 | a | a | a | a | 61 | 61 | 612020 | 612020 |
| 4 | a | a | a | a | 61 | 61 | 61202020 | 61202020 |
+------+------+------+------+------+---------+---------+----------+----------+
4 rows in set (0.00 sec)c2 char->返回数据去掉字符串尾部的空格c2 utf8mb4_unicode_ci->PAD SPACE->排序和比较运算，忽略字符串尾部空格mysql> select *,hex(c2),hex(c3),hex(c4),hex(c5) from t3 where c3='a';
+------+------+------+------+------+---------+---------+----------+----------+
| c1 | c2 | c3 | c4 | c5 | hex(c2) | hex(c3) | hex(c4) | hex(c5) |
+------+------+------+------+------+---------+---------+----------+----------+
| 1 | a | a | a | a | 61 | 61 | 61 | 61 |
| 2 | a | a | a | a | 61 | 61 | 6120 | 6120 |
| 3 | a | a | a | a | 61 | 61 | 612020 | 612020 |
| 4 | a | a | a | a | 61 | 61 | 61202020 | 61202020 |
+------+------+------+------+------+---------+---------+----------+----------+
4 rows in set (0.01 sec)c3 char->返回数据去掉字符串尾部的空格c3 utf8mb4_0900_ai_ci->NO PAD->排序和比较运算，字符串尾部空格当成普通字符mysql> select *,hex(c2),hex(c3),hex(c4),hex(c5) from t3 where c4='a';
+------+------+------+------+------+---------+---------+----------+----------+
| c1 | c2 | c3 | c4 | c5 | hex(c2) | hex(c3) | hex(c4) | hex(c5) |
+------+------+------+------+------+---------+---------+----------+----------+
| 1 | a | a | a | a | 61 | 61 | 61 | 61 |
| 2 | a | a | a | a | 61 | 61 | 6120 | 6120 |
| 3 | a | a | a | a | 61 | 61 | 612020 | 612020 |
| 4 | a | a | a | a | 61 | 61 | 61202020 | 61202020 |
+------+------+------+------+------+---------+---------+----------+----------+
4 rows in set (0.00 sec)c4 varchar->返回数据保留插入时的空格c4 utf8mb4_unicode_ci->PAD SPACE->排序和比较运算，忽略字符串尾部空格mysql> select *,hex(c2),hex(c3),hex(c4),hex(c5) from t3 where c5='a';
+------+------+------+------+------+---------+---------+---------+---------+
| c1 | c2 | c3 | c4 | c5 | hex(c2) | hex(c3) | hex(c4) | hex(c5) |
+------+------+------+------+------+---------+---------+---------+---------+
| 1 | a | a | a | a | 61 | 61 | 61 | 61 |
+------+------+------+------+------+---------+---------+---------+---------+
1 row in set (0.00 sec)c5 varchar->返回数据保留插入时的空格c5 utf8mb4_0900_ai_ci->NO PAD->排序和比较运算，字符串尾部空格当成普通字符mysql> set sql_mode='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> select *,hex(c2),hex(c3),hex(c4),hex(c5) from t3 where c2='a';
+------+------+------+------+------+----------+----------+----------+----------+
| c1 | c2 | c3 | c4 | c5 | hex(c2) | hex(c3) | hex(c4) | hex(c5) |
+------+------+------+------+------+----------+----------+----------+----------+
| 1 | a | a | a | a | 61202020 | 61202020 | 61 | 61 |
| 2 | a | a | a | a | 61202020 | 61202020 | 6120 | 6120 |
| 3 | a | a | a | a | 61202020 | 61202020 | 612020 | 612020 |
| 4 | a | a | a | a | 61202020 | 61202020 | 61202020 | 61202020 |
+------+------+------+------+------+----------+----------+----------+----------+
4 rows in set (0.00 sec)c2 char->PAD_CHAR_TO_FULL_LENGTH->返回数据字符串右边补充空格c2 utf8mb4_unicode_ci->PAD SPACE->排序和比较运算，忽略字符串尾部空格mysql> select *,hex(c2),hex(c3),hex(c4),hex(c5) from t3 where c3='a';
Empty set (0.00 sec)c3 char->PAD_CHAR_TO_FULL_LENGTH->返回数据字符串右边补充空格c3 utf8mb4_0900_ai_ci->NO PAD->排序和比较运算，字符串尾部空格当成普通字符1~4行c3列返回值都包含空格，且c3列的Collation是NO PAD，字符串尾部空格不能忽略，where过滤找不到记录mysql> select *,hex(c2),hex(c3),hex(c4),hex(c5) from t3 where c4='a';
+------+------+------+------+------+----------+----------+----------+----------+
| c1 | c2 | c3 | c4 | c5 | hex(c2) | hex(c3) | hex(c4) | hex(c5) |
+------+------+------+------+------+----------+----------+----------+----------+
| 1 | a | a | a | a | 61202020 | 61202020 | 61 | 61 |
| 2 | a | a | a | a | 61202020 | 61202020 | 6120 | 6120 |
| 3 | a | a | a | a | 61202020 | 61202020 | 612020 | 612020 |
| 4 | a | a | a | a | 61202020 | 61202020 | 61202020 | 61202020 |
+------+------+------+------+------+----------+----------+----------+----------+
4 rows in set (0.00 sec)
c4 varchar->返回数据保留插入时的空格
c4 utf8mb4_unicode_ci->PAD SPACE->排序和比较运算，忽略字符串尾部空格mysql> select *,hex(c2),hex(c3),hex(c4),hex(c5) from t3 where c5='a';
+------+------+------+------+------+----------+----------+---------+---------+
| c1 | c2 | c3 | c4 | c5 | hex(c2) | hex(c3) | hex(c4) | hex(c5) |
+------+------+------+------+------+----------+----------+---------+---------+
| 1 | a | a | a | a | 61202020 | 61202020 | 61 | 61 |
+------+------+------+------+------+----------+----------+---------+---------+
1 row in set (0.00 sec)c5 varchar->返回数据保留插入时的空格c5 utf8mb4_0900_ai_ci->NO PAD->排序和比较运算，字符串尾部空格当成普通字符
此时拿来做比较运算的字符串是Retrieved的内容，CHAR和VARCHAR返回数据时对字符串尾部的空格处理方式不同，并且PAD_CHAR_TO_FULL_LENGTH只影响CHAR类型。
4.3、对唯一索引的影响
https://dev.mysql.com/doc/refman/8.0/en/char.html
For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters results in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error.
如果存在唯一索引(单列、字符类型)，插入的数据仅在尾部空格个数不同，有可能会报duplicate-key错误：
mysql> select c1,c4,c5,hex(c4),hex(c5) from t3;
+------+------+------+----------+----------+
| c1 | c4 | c5 | hex(c4) | hex(c5) |
+------+------+------+----------+----------+
| 1 | a | a | 61 | 61 |
| 2 | a | a | 6120 | 6120 |
| 3 | a | a | 612020 | 612020 |
| 4 | a | a | 61202020 | 61202020 |
+------+------+------+----------+----------+
4 rows in set (0.00 sec)mysql> alter table t3 add unique(c4);
ERROR 1062 (23000): Duplicate entry 'a' for key 't3.c4'mysql> alter table t3 add unique(c5);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0可以看到c4列创建唯一索引失败，c5列创建唯一索引成功。
c4 utf8mb4_unicode_ci->PAD SPACE->排序和比较运算，忽略字符串尾部空格，4行数据重复。
c5 utf8mb4_0900_ai_ci->NO PAD->排序和比较运算，字符串尾部空格当成普通字符，4行数据不同。
5、总结
Stored
| - | CHAR(N) | VARCHAR(N) |
|---|---|---|
| Stored | 字符不足Ｎ右边补空格 | 保留插入时的空格，不会在右边额外补充空格 |
Retrieved
| SQL_MODE | CHAR(N) | VARCHAR(N) |
|---|---|---|
| Default Value | 去掉字符串尾部的空格 | 保留插入时的空格 |
| PAD_CHAR_TO_FULL_LENGTH | 返回完整字符串，不足Ｎ右边补空格 | 保留插入时的空格 |
Comparison(不包括like)
| Pad Attribute | CHAR(N)/VARCHAR(N) | | --- | --- |--- | | PAD SPACE | 忽略字符串尾部空格 | | NO PAD | 字符串尾部空格当成普通字符，不能忽略 |
Enjoy GreatSQL :)
文章推荐：
技术分享 | MGR最佳实践(MGR Best Practice) https://mp.weixin.qq.com/s/66u5K7a9u8GcE2KPn4kCaA
技术分享 | 万里数据库MGR Bug修复之路 https://mp.weixin.qq.com/s/IavpeP93haOKVBt7eO8luQ
Macos系统编译percona及部分函数在Macos系统上运算差异 https://mp.weixin.qq.com/s/jAbwicbRc1nQ0f2cIa_2nQ
技术分享 | 利用systemd管理MySQL单机多实例 https://mp.weixin.qq.com/s/iJjXwd0z1a6isUJtuAAHtQ
产品 | GreatSQL，打造更好的MGR生态 https://mp.weixin.qq.com/s/ByAjPOwHIwEPFtwC5jA28Q
产品 | GreatSQL MGR优化参考 https://mp.weixin.qq.com/s/5mL_ERRIjpdOuONian8_Ow
关于 GreatSQL
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。
Gitee: https://gitee.com/GreatSQL/GreatSQL
GitHub: https://github.com/GreatSQL/GreatSQL
微信&QQ群：
可扫码添加GreatSQL社区助手微信好友，发送验证信息“加群”加入GreatSQL/MGR交流微信群，亦可直接扫码加入GreatSQL/MGR交流QQ群。
本文由博客一文多发平台 OpenWrite 发布！
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！