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

站长

2023年09月16日 22:35 · 阅读数 14

背景

近期工作过程中，发现一种奇怪的现象，在 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官方针对这种情况给予例子

值	CHAR(4)	需要存储的大小	VARCHAR(4)	需要存储的大小
''	' '	4 bytes	''	1 bytes
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

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.

参考文档

转载自:https://juejin.cn/post/7277489276270395433