llegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=

问题描述

从测试环境还原了一个mysql数据库，功能模块查询报错，日志如下：

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

问题排查

把sql粘贴到数据库执行，发现是临时表的字符集编码和业务表的编码不一致导致的。
数据库编码是：utf8_unicode_ci
库里的业务表编码是：utf8_general_ci
临时表编码：utf8_unicode_ci
sql:

SELECT
    t0 . * ,
    t1 . * 
FROM 
    default_dw t0 
    LEFT JOIN 
        kf69nqn8 t1 
        ON t1 . default_period = '2021N0001' 
        AND t0 . id = t1 . default_dw 
WHERE 
    EXISTS (
    SELECT
        1 
    FROM 
        tmp_kx78smir_2202 
    WHERE 
        id = t0 . id ) 
AND t0 . validtime <= str_to_date ('2021-12-31' , '%Y-%m-%d' )
AND t0 . invalidtime > str_to_date ('2021-12-31' , '%Y-%m-%d' )
AND ((t1 . default_dw <> '' 
        AND t1 . default_dw IS NOT NULL ) ) 

临时表tmp_kx78smir_2202 ：

业务表kf69nqn8:

解决办法

1.改sql

SELECT
    t0 . * ,
    t1 . * 
FROM 
    default_dw t0 
    LEFT JOIN 
        kf69nqn8 t1 
        ON t1 . default_period = '2021N0001' 
        AND t0 . id = t1 . default_dw 
WHERE 
    EXISTS (
    SELECT
        1 
    FROM 
        tmp_kx78smir_2202 
    WHERE 
        id = t0 . id COLLATE utf8_general_ci)  --这里将临时表的编码改成utf8_general_ci
AND t0 . validtime <= str_to_date ('2021-12-31' , '%Y-%m-%d' )
AND t0 . invalidtime > str_to_date ('2021-12-31' , '%Y-%m-%d' )
AND ((t1 . default_dw <> '' 
        AND t1 . default_dw IS NOT NULL ) ) 

注：不友好，改sql就意味着要改代码，未采用。

2.改临时表编码

alter table tmp_kx78smir_2202 convert to character set utf8 collate utf8_general_ci

注：也可以直接用Navicat直接打开表设计去修改。
这种方法我也没有采用，因为我这是创建的临时表，每查询一次就会随机创建一张表，不可能一直改这个字符集编码。

3.修改数据库字符集

直接将数据库字符集编码改成utf8_general_ci，这样创建的临时表和原来的业务表编码就一样了。