MySQL报错：Illegal mix of collations

背景

最近项目上MySQL库运行过程中遇到SQL执行报错：java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation ‘=’

分析

询问DBA后，被告知是关联查询的表排序规则不一致导致，如下图：

解决办法

要解决这个问题很简单，就是将两张表的排序规则调整一致就好。但是以哪个为准呢？
首先，我们要确定数据库使用的排序规则，一般情况下，建表的排序规则尽量与数据默认配置保持一致

show variables where variable_name like '%collation%';
 

结果如下图：

剩下就简单了，如果有工具连数据库，那么直接在工具里操作就好，当然我们也可以执行SQL处理；
调整表字符集SQL如下:

alter table TabelName default character set utf8mb4 collate=utf8mb4_general_ci;

最后呢，就是统一整库所有表字符集、排序规则的SQL,其中$DATABASE_NAME需要根据实际情况调整

/**修改数据库的默认字符集及排序规则**/alterdatabase $DATABASE_NAME charset=utf8mb4 collate=utf8mb4_general_ci;/**修改表的字符集**//**将以下sql的执行结果在命令窗口中执行**/SELECT CONCAT('ALTER TABLE ', TABLE_NAME,' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')'修正SQL'FROM information_schema.`TABLES`WHERE  TABLE_SCHEMA ='$DATABASE_NAME$';/**修改表中的字段的字符集**//**将以下sql的执行结果在命令窗口中执行**/SELECT  CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY COLUMN ', COLUMN_NAME,' ', COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT \'',COLUMN_DEFAULT,'\';')'修正SQL'FROM information_schema.`COLUMNS`WHERE COLUMN_DEFAULT isnotnullAND TABLE_SCHEMA ='$DATABASE_NAME'and  COLLATION_name!='utf8mb4_general_ci';SELECT  CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY COLUMN ', COLUMN_NAME,' ', COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;')'修正SQL'FROM information_schema.`COLUMNS`WHERE COLUMN_DEFAULT isnullAND TABLE_SCHEMA ='$DATABASE_NAME'and  COLLATION_name!='utf8mb4_general_ci';