【MySQL】Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and ...

时间:2023-01-10 14:59:12

线上遇到这个问题,详细信息如下:

SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='"

想起其中有查询用到了多表连接,查了下 DDL,连接的数据表有一个明确写了

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

而另一个表则无此语句,collations 默认为 utf8mb4_general_ci。导致了这个问题。

解决方法:
将这两个表统一改为 utf8mb4_unicode_ci