mysql报错Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT)

1.现象

mysql 表的关联或者子查询是报错
1267 - Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘=’

2.原因

sql语句各个表的默认校队字符集不一致导致的报警。
通过 show create table table_name语句查看各个表的默认校队字符集定位是那张表的原因；

3.解决方案

执行语句

ALTER TABLE dim_date_tcl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
1

将dim_date_tcl 表的校队字符集由 utf8mb4_unicode_ci 改为 utf8mb4_general_ci

4.常用语句

# 查询所有跟字符集相关的信息
show variables like '%char%';
# 查看字符集对应的校对规则
show collation where charset = 'utf8mb4';

#更改数据库编码：
ALTER DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

#更改表的编码：
ALTER DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
1
2
3
4
5
6
7
8
9
10