mysql 排序规则对尾部空格的处理

背景

数据备份：test环境（mysql 8） ——> 开发环境 （mysql 5.7）

需要备份的一张表：taxk_receipt 下面是表结构（部分字段）：

CREATE TABLE 
tax_receipt
 (
  
id
 int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  
tracking_number
 varchar(255) DEFAULT NULL COMMENT '物流商单号',
  
file_name
 varchar(255) DEFAULT NULL COMMENT '原始文件名',
  PRIMARY KEY (
id
),
  UNIQUE KEY 
uidx_tracking_number
 (
tracking_number
)
) ENGINE=InnoDB AUTO_INCREMENT=798 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='税金单';

其中 tracking_number 字段加了唯一索引

1. 首先在test环境执行备份命令将数据dump到一个sql文件中 ：tax_receipt_backup_20231220.sql
2. 将此sql文件下载到本地，进入开发环境数据 执行次sql文件 source /tmp/tax_receipt_backup_20231220.sql
3. 结果报错：Unknown collation: ‘utf8mb4_0900_ai_ci‘

分析

这里报错原因和容易在网上查得到：

原sql文件是mysql（8.0版本），高级往低级（5.7版本）导入时出现排序规则COLLATE不兼容的情况。

于是我就手动将从test环境dump出的sql文件中的 utf8mb4_0900_ai_ci 全部替换成了mysql 5.7 版本支持的排序规则：utf8mb4_general_ci

排序规则替换过后是不报Unknown collation的错误了，但是又有了新的报错：

Duplicate entry ’LGJ20230808000032_小蝌蚪税金单’ for key ’tracking_number‘.

去test环境数据库查一下数据，发现在这张表有唯一索引的情况下，竟然可以看到两个**‘tracking_number’字段为‘LGJ20230808000032_小蝌蚪税金单’**的记录

1. 使用全模糊查询：

1. 使用=查询

1. 然后猜测可能这两条记录的tracking_number值可能编码不同？或者是里面加了不同的特殊字符？

1. 后面通过左右模糊查询对比后猜测： id=734的tracking_number后面可能有特殊符号（换行符或者空格之类的）

1. 然后查一下两条记录的字段长度，猜测八成就是一个空格

1. 最后通过字符串拼接查询，确定是空格导致

总结

问：为什么 mysq8.0版本中utf8mb4_0900_ai_ci排序规则通过唯一索引确定唯一值的时候不会忽略空格，而mysql5.7中的utf8mb4_general_ci排序规则会确定唯一值的时候会自动忽略空格

答：https://cloud.tencent.com/developer/article/2306655