mysql字段编码不一致导致Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLIC

在多表关联查询时出现错误信息：

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

error code 1627

原因：

=号两边的字段使用了不同的排序规则。

关联查询sql在测试库没问题，在生产库执行报错，说明包错还与字段保存的实际值有关系，即使两个字段排序规则不一样，但是字段值都是数字或字母，依然可正常执行，但是俩字段实际值存在不较常见的中文汉字或特殊符号时，就会引发该异常。

解决方案：将数据库所有的所有表、字段修改统一的编码、排序规则，下面以将编码统一为utf8mb4，排序规则utf8mb4_general_ci为例

步骤1：查询目标库中所有表使用的编码方式

select count(1),原排序规则 from (SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '原排序规则',CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;') '修正SQL'FROM information_schema.`TABLES`) as tab where  数据库='db_name' group by 原排序规则

查询结果

步骤2：获取非utf8mb4_general_ci编码的表，where条件为上面步骤1查询的非utf8mb4_general_ci编码，执行后获取最后面一列修正sql，执行该sql即可将对应的表修改为utf8mb4编码

select * from (SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '原排序规则',CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;') '修正SQL'FROM information_schema.`TABLES`WHERE    TABLE_COLLATION RLIKE 'utf8mb4_unicode_ci'    or TABLE_COLLATION RLIKE 'utf8mb4_0900_ai_ci'    or TABLE_COLLATION RLIKE 'utf8_general_ci'    or TABLE_COLLATION RLIKE 'utf8mb4_bin') as tab where  数据库='db_name'

步骤3：获取非utf8mb4_general_ci排序规则的字段，where条件为上面步骤1查询的非utf8mb4_general_ci编码，执行后获取最后面一列修正sql，执行该sql即可(注意：如有视图需去掉后面视图中的字段，否则执行时会报table does not exist异常)

select * from(SELECT    TABLE_SCHEMA '数据库',    TABLE_NAME '表',    COLUMN_NAME '字段',    CHARACTER_SET_NAME '原字符集',    COLLATION_NAME '原排序规则',    CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN ', COLUMN_NAME, ' ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT ', '\'',COLUMN_COMMENT,'\';' ) '修正SQL'FROM    information_schema.`COLUMNS`WHERE    COLLATION_NAME RLIKE 'utf8mb4_unicode_ci'    or COLLATION_NAME RLIKE 'utf8mb4_0900_ai_ci'    or COLLATION_NAME RLIKE 'utf8_general_ci'    or COLLATION_NAME RLIKE 'utf8mb4_bin'    ) as tab    where 数据库='db_name'

 

步骤四：查看数据库默认字符校对，将所有的默认编码规则修改一致（可选操作）

show variables where Variable_name like 'collation%';

修改方法自行搜索了。。。

写在最后：

这是亡羊补牢的方法，从源头上解决，还得团队建立统一的数据库设计规范，提前确定好数据库的编码、排序规则，严格执行，防患于未然。

来源地址：https://blog.csdn.net/chen417980762/article/details/127068037