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%';

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

写在最后：

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