java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_

查询视图时报错:java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=';

本地环境:mysql8.0.13

异常提示排序规则编码混乱,mysql8.0.1之后的默认COLLATE为utf8mb4_0900_ai_ci;

检查视图中所包含的表发现其中一个建表时 没有设置编码,并且其他的表设置的是 CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;因此导致混乱;

查看当前数据库的默认编码:

mysql> show variables where Variable_name like 'collation%';

java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_

查看各表编码:

mysql> show create table ‘table_name’;

 

解决方案给没有设置编码的表重新设置一下:

mysql> alter table table_name default character set utf8mb4 collate=utf8mb4_general_ci;

java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_

这样设置只针对表的,但是表中字段未修改:

mysql> ALTER TABLE table_name convert to CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

 

java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_

 

修改完成以后,重新创建视图!!!!

 

参考编码修改

上一篇:【洛谷4738】[CERC2017] Cumulative Code(Meet in Middle)


下一篇:1630 亿美元现金怎么花?苹果可能会先“干掉”三星