关于MySQL数据库版本不同导致表进行比较的时候报错illegal mix of collations...的问题

SYKMI

已于 2023-08-30 14:24:49 修改

阅读量986

点赞数

分类专栏： SQL 文章标签：数据库 mysql oracle

于 2023-08-30 13:22:45 首次发布

本文链接：https://blog.csdn.net/weixin_44001317/article/details/132574242

版权

SQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

问题发生的原委

之前在项目开发的时候，我本地也建立了数据库用作开发库，我本地的数据库版本是5.7的，但是测试和生产库都是8.0的版本，我们定义的数据库字符集是utf8mb4，排序规则是utf8mb4_general_ci，前段时间一直都没有什么问题，直到有一天发测试的时候，有个sql语句报错了，我这里是在连接查询的时候报错的，错误信息为：(utf8mb4_general_ci,IMPLICIT)and (utf8mb4_0900_ai_ciMysql...，大致意思是作为连接条件的A和B字段的排序规则不同导致的，由于我在测试数据库建表的时候设定的排序规则是utf8mb4_general_ci，但是测试服是8.0版本以上的，默认的排序规则是utf8mb4_0900_ai_ci，所以导致连接查询的时候出错误了，这里需要更改建表时的字段的排序规则为utf8mb4_0900_ai_ci或者更改数据库实例的默认排序规则为utf8mb4_general_ci。

解决方式和教训

千万记住以后建表时统一规则，规定的什么字符集和排序规则就用什么字符集和排序规则，不要A表使用utf8然后B表使用latin等…，这样后面连接查询可能会出现问题，并且无法使用到索引，修改还很麻烦！！！

对于MySQL5.7版本：
建议建表时字符集设置为utf8mb4，排序规则设置为utf8mb4_general_ci，因为5.7版本的数据库实例默认排序规则就是utf8mb4_general_ci，所以会方便很多，也没什么问题

#设置数据库字符集和排序规则
alter database database default character set utf8mb4 COLLATE utf8mb4_general_ci;
# 配置字符集
 /*数据库客户端的编码*/
 set character_set_client = utf8mb4;
 /*建立连接使用的编码*/
 set character_set_connection = utf8mb4;
 /*数据库的编码*/
 set character_set_database = utf8mb4;
 /*结果集的编码*/
 set character_set_results = utf8mb4;
 /*数据库服务器的编码*/
 set character_set_server=utf8mb4;

对于MySQL8.0版本
建议建表时字符集设置为utf8mb4，排序规则设置为utf8mb4_0900_ai_ci，因为8.0版本的数据库实例默认排序规则就是utf8mb4_0900_ai_ci

#设置数据库字符集和排序规则
alter database database default character set utf8mb4 COLLATE utf8mb4_0900_ai_ci;
# 配置字符集
 /*数据库客户端的编码*/
 set character_set_client = utf8mb4;
 /*建立连接使用的编码*/
 set character_set_connection = utf8mb4;
 /*数据库的编码*/
 set character_set_database = utf8mb4;
 /*结果集的编码*/
 set character_set_results = utf8mb4;
 /*数据库服务器的编码*/
 set character_set_server=utf8mb4;

如果像我一样，在MySQL8.0实例上的数据库统一使用5.7的标准，也就是字符集设置为utf8mb4，排序规则设置为utf8mb4_general_ci，那么请执行以下语句（不建议在8.0上使用5.7的标准，不知道还有啥坑）

字符集统一设定为utf8mb4
对于已经建好的表来说，那么需要修改其中的字段的字符集和表的排序规则
参考该博客批量修改MySQL的字符集、排序规则以及保证默认值不会发生修改
设定排序规则为utf8mb4_general_ci

#设置数据库级别默认排序规则和以后建表的默认字符集
alter database database default character set utf8mb4 COLLATE utf8mb4_general_ci;
# 设置会话级别排序规则(默认排序规则)
set session default_collation_for_utf8mb4 = utf8mb4_general_ci;
#设置连接级别排序规则
set collation_connection = utf8mb4_general_ci;
#设置全局级别
set global default_collation_for_utf8mb4 = utf8mb4_general_ci;

#设置完查看是否正确
show VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
show VARIABLES WHERE value LIKE 'utf8%';
#上面的show语句的结果可以不用关注【character_set_system】和【collation_server】，基本不影响