工作踩坑系列——MySQL字符串类型尾部空格到底去哪了？

背景

近期工作过程中，发现一种奇怪的现象，在 MySQL 中，对于 CHAR 类型，插入数据的时候，自动删除了尾部空格

原因分析

MySQL 字符串类型存储机制

• CHAR类型：对于 CHAR 类型的存储长度在创建表时声明(长度在0～255)，当存储 CHAR 类型的值的时候，会自动在值右侧填充到指定长度的空格

• VARCHAR类型：对于 VARCHAR 类型的存储长度是动态变化的(长度在0～65535)，当存储 VARCHAR 类型的值的时候，不会自动在值的右侧填充到指定长度的空格

• TEXT / BLOB类型：对于 TEXT / BLOB 类型，它与 CHAR/VARCHAR 类型最主要的区别是，不需要指定该字段的长度，该类型和 VARCHAR 类似，存储时不会自动在值的右侧填充空格

MySQL 字符串类型写入机制

• CHAR类型：在非严格SQL模式下，如果值的长度超过了该列声明的长度，超过的长度将会截断(空格)，并生成告警，对于非空格的值，MySQL 将不会进行截断操作，而是报错

• VARCHAR类型：VARCHAR 类型和 CHAR 类型类似，同样会进行截断(空格)处理，对于非空格的值，则会进行报错

原文：If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode.

MySQL官方针对这种情况给予例子

MySQL 字符串类型查询机制

• CHAR类型：MySQL 的排序规则是基于 PAD SPACE ，在查询的时候，会自动忽略尾部空格，比较规则也是基于此，MySQL 官方提供了一个 SQL_MOD 值 PAD_CHAR_TO_FULL_LENGTH，即可不忽略尾部空格

• VARCHAR / TEXT / BLOB类型：对于这些字符串类型，不同的 MySQL 版本处理机制有所不同

  • MySQL 5.7：在查询/匹配时，会忽略尾部的空格(数据和查询条件)进行匹配
  • MySQL 8.0：对于查询的逻辑不再去除尾部空格，而是采用精确匹配的方式

MySQL 排序集

出现上述这个原因是 MySQL 有PAD SPACE和NO PAD两种不同的排序规则，这两种排序规则 MySQL 官方是这样解释的

• 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:

• 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.

参考文档

• dev.mysql.com/doc/refman/…
• dev.mysql.com/doc/refman/…
• dev.mysql.com/doc/refman/…