解决由于“mysql8默认排序规则”导致“外健不兼容”问题

问题复现

新到手一个Java17 + mysql8的项目，使用开源工具flyway进行数据库的版本控制。
装环境，启动。
在flyway更新数据库的过程中报错，启动失败。

错误信息：

Migration V3.1.21__add_coupon_temp.sql failed
---------------------------------------------
SQL State  : HY000
Error Code : 3780
Message    : Referencing column 'client_id' and referenced column 'id' in foreign key constraint 'FKiywe9ktoq9d997123pmqdlmg5' are incompatible.
Location   : db/migration/V3.1.21__add_coupon_temp.sql (/Users/user/api/target/classes/db/migration/V3.1.21__add_coupon_temp.sql)
Line       : 3
Statement  : alter table coupon_temp add constraint FKiywe9ktoq9d997123pmqdlmg5 foreign key (client_id) references client (id)

关键信息是这一条：

Referencing column 'client_id' and referenced column 'id' in foreign key constraint 'FKiywe9ktoq9d997123pmqdlmg5' are incompatible.

翻译过来就是：

引用列“客户端ID”和引用列“ID”在外健约束中不兼容。
导致此版本的flyway变更失败。

原因分析

1. 主键与外键字段数据类型不一致
2. 主键与外键字段数据类型一致，但两个数据表的字符集或排序规则不一致。

经检查，外健关联的两个列的字符集均为varchar(255)，并且创建表的SQL语句中指定了字符集utf8mb4，因此猜测是排序规则不一致。

查看表结构，发现部分数据表的Collation是utf8mb4_0900_ai_ci，而另一部分是utf8mb4_general_ci

（为避免盗版问题，本文使用JetBarins系列的DataGrip，如使用NaviCat操作类似）

问题就出在这里。为什么排序规则会变呢？

Mysql8的默认排序规则

当设置表的默认字符集为utf8mb4字符集但未明确指定排序规则时：

• 在MySQL 5.7版本中，默认排序规则为utf8mb4_general_ci。
• 在MySQL 8.0版本中，默认排序规则为utf8mb4_0900_ai_ci。

我们分别在两个版本中使用show variables where Variable_name like '%collation%';验证一下

其中，5.7版本：

8.2版本：

因此，使用mysql8版本，设置字符集为utf8mb4，并且没有设置排列规则时，就会使用utf8mb4_0900_ai_ci了，而部分表在设计SQL时手动指定了排列规则，这就是为什么查看时会出现两种规则都有的情况了。

修改Mysql8的默认规则

我们把collation_connection和default_collation_for_utf8mb4改掉应该就没问题了

set global collation_connection = utf8mb4_general_ci;
set global default_collation_for_utf8mb4 = utf8mb4_general_ci;

断开会话并重新连接后，再次使用show variables where Variable_name like '%collation%';查看，已经是我们想要的结果：

此时，drop掉出问题的数据表，重新启动后端，执行flyway，启动成功。

参考资料

https://www.cnblogs.com/gaogao67/p/14721829.html
https://www.cnblogs.com/hdwang/p/16739147.html