记一次MySQL字符串类型末尾包含空格导致的问题

记一次MySQL字符串类型末尾包含空格导致的问题

起因：

测试同事反映自动化测试数据没有展示出来

排查发现是python统计脚本插入数据时，有唯一键索引错误

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, "Duplicate entry 'xxx' for key 'xxx_key'")

而这些数据是从ClickHouse中GroupBy之后，写入MySQL。

ClickHouse中数据：

执行的SQL

SELECT
	new_version,
	old_version 
FROM
	tablexxx 
GROUP BY
	new_version,
	old_version;

clickhouse中的结果

由于是在腾讯云的控制台上执行的SQL，所以结果中显示的‘v2’都是一样的，感到十分不解。后面转了Base64后才发现有所不同。

既然不相同，为什么插入MySQL还会报错呢？

在MySQL中制造模拟数据执行上述SQL后发现如下结果：

MySQL中的结果

MySQL中的结果只有一条！！！

在stackoverflow上找到了一个类似的问题，先上地址：url

结论：

根据 MySQL 的默认设置，对于字符串类型的列，尾部的空格会被忽略，因此 ‘v2 ’ 和 ‘v2’ 在比较时被认为是相等的。

MySQL官方手册里的说明

大意就是MySQL校对规则有一个pad属性，默认采用“PAD SPACE”。只有某些基于UCA9.0.0或之上的Unicode校对规则是“NO PAD”。

对于非二进制字符串（CHAR、VARCHAR 和 TEXT），字符串校对规则的 pad 属性决定了在比较中对字符串末尾的尾随空格的处理方式。“NO PAD”将尾部空格视为比较中的重要字符，就像任何其他字符一样。“PAD SPACE” 校对规则在比较中将尾随空格视为无关紧要的；字符串的比较将忽略尾随空格。

“PAD SPACE”时，如果一个列具有需要唯一值的索引，在该列中插入只是尾部多了几个空格的值将导致key错误。例如，如果一个表包含’a’，那么尝试插入’a ’ 将导致错误。