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)谢谢!