MySQL

记一次MySQL字符串类型末尾包含空格导致的问题 起因： 测试同事反映自动化测试数据没有展示出来 排查发现是python统计脚本插入数据时，有唯一键索引错误 sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, "Duplicate entry 'xxx' for key 'xxx_key'") 而这些数据是从ClickHouse中GroupBy之后，写入MySQL。 ClickHouse中数据： id new_version old_version 1 v1 ‘v2’ 2 v1 ‘v2 ’ (尾部有一个空格) 执行的SQL SELECT new_version, old_version FROM tablexxx GROUP BY new_version, old_version; clickhouse中的结果 new_version old_version v1 v2 v1 v2 由于是在腾讯云的控制台上执行的SQL，所以结果中显示的‘v2’都是一样的，感到十分不解。后面转了Base64后才发现有所不同。 既然不相同，为什么插入MySQL还会报错呢？ 在MySQL中制造模拟数据执行上述SQL后发现如下结果： MySQL中的结果 new_version old_version v1 v2 MySQL中的结果只有一条！！！ 在stackoverflow上找到了一个类似的问题，先上地址：url 结论： 根据 MySQL 的默认设置，对于字符串类型的列，尾部的空格会被忽略，因此 ‘v2 ’ 和 ‘v2’ 在比较时被认为是相等的。 MySQL官方手册里的说明 大意就是MySQL校对规则有一个pad属性，默认采用“PAD SPACE”。只有某些基于UCA9.0.0或之上的Unicode校对规则是“NO PAD”。 ...

INSERT IGNORE和STRICT模式 当STRICT模式打开时，如果您尝试将无效值使用INSERT插入表中，MySQL将返回错误并中止语句。 但是，如果您使用INSERT IGNORE语句，MySQL将发出警告而不是错误。此外，在将值添加到表之前，它将尝试调整值以使其有效。 请考虑以下示例。 首先，我们创建一个的新表名为tokens： CREATE TABLE tokens ( s VARCHAR(6) ); 在此表中，列s仅接受长度小于或等于6的字符串。 其次，将长度为7的字符串插入tokens表中。 INSERT INTO tokens VALUES('abcdefg'); MySQL发出以下错误，因为严格模式已启用。 ERROR 1406 (22001): Data too long for column 's' at row 1 第三，使用INSERT IGNORE语句插入相同的字符串。 INSERT IGNORE INTO tokens VALUES('abcdefg'); MySQL在将数据插入tokens表之前截断了数据。此外，它会发出警告。 +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 's' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec) 数据库结果 SELECT * FROM `tokens`;

原理： INSERT INTO 在执行数据插入操作时，会在数据库中对元组的主键进行检测，若没有存在，则执行插入动作，若存在，则会报错。 而INSERT IGNORE INTO命令与其不同的是，INSERT IGNORE会忽略数据库中已经存在的记录： 如果数据库没有数据，就插入新的数据；如果有数据的话就跳过这条数据。 这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。 需要说明的是，INSERT INTO和INSERT IGNORE INTO只根据“主键值”或“unique索引”进行判断，只要主键值已在数据库中存在，则认为即将插入重复记录。 两个字段： id 是主键 name是唯一索引 插入’yangqq6’时，与id10的数据唯一索引冲突了，但是消耗了主键11，所以插入’yangqq7’时id为12