当前位置：首页 > article >正文

mysql 8.0.39 Caused by: java.sql.SQLException: Illegal mix of collations 异常解决

article 2024/11/5 19:30:43

java服务可以正常启动，页面发现查询报错Illegal mix of collations

报错信息：

### Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '=' ; uncategorized SQLException; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='

经查看发现是关联表排序异常，因是mysql 8.0.39

然后查看相关参数：

show variables where variable_name like '%collation%';

然后定位异常调成一直参数问题解决

发现设置utf8mb3_general_ci 会报错，所以要与参数一直改成utf8mb4_general_ci 即可生效。

mysql> set global default_collation_for_utf8mb4=utf8mb4_general_ci;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql>
mysql>
-----设置为永久参数，即可重启生效:
mysql> set persist default_collation_for_utf8mb4=utf8mb4_general_ci;
Query OK, 0 rows affected, 1 warning (0.00 sec)

(笔者原创文章，转载请注明出处：https://blog.csdn.net/LFCuiYs）谢谢！

查看全文

http://www.kler.cn/a/380533.html