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

1、报错信息

 Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

2、原因

用=连接的两个列的排序规则不一致

例：

SELECT t1.* FROM table1 t1, table2 t2 WHERE t1.col = t2.col

其中table1的col列的排序规则是utf8mb4_0900_ai_ci，table2的col列的排序规则是utf8mb4_general_ci

3、解决办法

(1)修改表的排序规则，使其保持统一

(2)用关键字COLLATE统一排序规则

例：

SELECT t1.* FROM table1 t1, table2 t2 WHERE t1.col = t2.col COLLATE utf8mb4_0900_ai_ci