Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT)

一、概述

今天同事突然询问报错
Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

分析：
应该是连表查询，两张表的的匹配列编码格式不一致引起的

二、问题复现

1、创建两张小表
create table test1 (
name varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
);
create table test2 (
name varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
);

2、插入数据
insert into test1 values ('guanyu'),('zhangfei'),('zhaoyun');
insert into test2 values ('zhangliao'),('dianwei'),('guanyu');

3、复现报错情况
情况一
select t1.name from test1 t1,test2 t2 where t1.name=t2.name;

情况二
select name,(select name from test2 where name = (select name from test1 where name='guanyu')) name2 from test1;

由于两张表对应的name列编码格式不一样，直接报错
Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

三、问题修复

1、查看数据库的默认编码格式
show variables where Variable_name like 'collation%';
此处只是建议，并不一定非要按照默认编码格式

2、重置表的编码格式
alter table test1 default character set utf8mb4 collate=utf8mb4_0900_ai_ci;

3、修改表中字段编码格式
ALTER TABLE test1 convert to CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;