当前位置： 代码网 > it编程>数据库>Mysql > MySQL 5.7升级8.0后出现排序规则问题的解决方案汇总

MySQL 5.7升级8.0后出现排序规则问题的解决方案汇总

2024年07月04日 • Mysql •我要评论
比较操作中使用不同的字符集或排序规则通常会触发此问题，mysql 8.0 默认 collate 为utf8mb4_0900_ai_ci和 对应列 collate 的utf8mb4_general_ci

比较操作中使用不同的字符集或排序规则通常会触发此问题，mysql 8.0 默认 collate 为 utf8mb4_0900_ai_ci 和 对应列 collate 的 utf8mb4_general_ci 不匹配。

问题现象

mysql 5.7.34 升级到 8.0.32 后部分查询语句报错如下：

error 1267 (hy000): illegal mix of collations (utf8mb4_general_ci,implicit) and (utf8mb4_0900_ai_ci,implicit) for operation 'find_in_set'

问题原因

比较操作中使用不同的字符集或排序规则通常会触发此问题，mysql 8.0 默认 collate 为 utf8mb4_0900_ai_ci 和 对应列 collate 的 utf8mb4_general_ci 不匹配。

问题重现过程

创建测试表。

create table `t01` (
  `id` varchar(30) character set utf8mb4 collate utf8mb4_general_ci not null,
  `a_code` varchar(10) character set utf8mb4 collate utf8mb4_general_ci default null,
  `b_code` varchar(10) character set utf8mb4 collate utf8mb4_general_ci default null,
  primary key (`id`) using btree
) engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci row_format=dynamic;

执行查询语句。

select aaa.* from(
select 
@xxx as _xxx,
( select @xxx := group_concat( a_code ) from t01 where find_in_set( b_code, @xxx ) ) as cxxx
from
t01,( select @xxx := 'xxx') b
where @xxx is not null) id,t01 aaa
where
find_in_set( aaa.a_code, id._xxx )
order by a_code;

报错。

error 1267 (hy000): illegal mix of collations (utf8mb4_general_ci,implicit) and (utf8mb4_0900_ai_ci,implicit) for operation 'find_in_set'

问题分析

查看默认排序规则。

mysql> show collation like 'utf8mb4_0900_ai_ci';
+--------------------+---------+-----+---------+----------+---------+---------------+
| collation          | charset | id  | default | compiled | sortlen | pad_attribute |
+--------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | yes     | yes      |       0 | no pad        |
+--------------------+---------+-----+---------+----------+---------+---------------+
1 row in set (0.00 sec)

mysql> show collation like 'utf8mb4_general_ci';
+--------------------+---------+----+---------+----------+---------+---------------+
| collation          | charset | id | default | compiled | sortlen | pad_attribute |
+--------------------+---------+----+---------+----------+---------+---------------+
| utf8mb4_general_ci | utf8mb4 | 45 |         | yes      |       1 | pad space     |
+--------------------+---------+----+---------+----------+---------+---------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.collations where is_default='yes' and character_set_name='utf8mb4';
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| collation_name     | character_set_name | id  | is_default | is_compiled | sortlen | pad_attribute |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4            | 255 | yes        | yes         |       0 | no pad        |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
1 row in set (0.00 sec)

查看相关参数。

mysql> show variables like '%collation%';
+-------------------------------+--------------------+
| variable_name                 | value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

其中:
mysql> show global variables like '%collation%';
+-------------------------------+--------------------+
| variable_name                 | value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

查看配置文件参数。

mysql@cjc-db-01:/home/mysql$cat /etc/my.cnf 
......
[mysqld]
collation_server = utf8mb4_general_ci

可以看到，客户端局部会话变量 collation_connection 的值为 utf8mb4_0900_ai_ci，而全局变量值为 utf8mb4_general_ci，两者不一致。

这是由于服务端在客户端连接时，获取了客户端对字符集和排序规则的缺省设置，也就是 utf8mb4_0900_ai_ci。

解决方案

  • 修改参数
  • 修改表 collate
  • 修改 sql 语句

1. 修改参数

参数collation_connection 在客户端局部变量值和全局变量值不一致，如何改成一致？官网参考材料

--character-set-client-handshake
command-line format:--character-set-client-handshake[={off|on}]
deprecated:8.0.35
type:boolean
default value:on

参数说明

  • 不忽略客户端发送的字符集信息
  • 为了忽略客户端信息并使用默认的服务器字符集
  • 使用参数：--skip-character-set-client-handshake

此选项在 mysql 8.0.35 及更高版本的 mysql 8.0 中已被弃用。在该版本中，无论何时使用此选项，都会发出警告，并将在未来版本的 mysql 中删除。

依赖此选项的应用程序应该尽快开始迁移。

添加 my.cnf 参数。

[mysqld]
skip-character-set-client-handshake

重启 mysql。

mysqladmin -uroot -p****** shutdown
mysqld --defaults-file=/etc/my.cnf --user=mysql &

登录

mysql -uroot -p cjc

查看参数，collation_connection 参数值修改成功

mysql> show global variables like '%collation%';
+-------------------------------+--------------------+
| variable_name                 | value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

mysql> show variables like '%collation%';
+-------------------------------+--------------------+
| variable_name                 | value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_general_ci |
| collation_server              | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.01 sec)

再次执行，问题解决。

select aaa.* from(
select 
@xxx as _xxx,
( select @xxx := group_concat( a_code ) from t01 where find_in_set( b_code, @xxx ) ) as cxxx
from
t01,( select @xxx := 'xxx') b
where @xxx is not null) id,t01 aaa
where
find_in_set( aaa.a_code, id._xxx )
order by a_code;
empty set, 2 warnings (0.00 sec)

2. 修改表 collate

先改回原参数，查询报错。

select aaa.* from(
select 
@xxx as _xxx,
( select @xxx := group_concat( a_code ) from t01 where find_in_set( b_code, @xxx ) ) as cxxx
from
t01,( select @xxx := 'xxx') b
where @xxx is not null) id,t01 aaa
where
find_in_set( aaa.a_code, id._xxx )
order by a_code;
error 1267 (hy000): illegal mix of collations (utf8mb4_general_ci,implicit) and (utf8mb4_0900_ai_ci,implicit) for operation 'find_in_set'

修改表排序规则。

mysql> show create table t01\g;
*************************** 1. row ***************************
       table: t01
create table: create table `t01` (
  `id` varchar(30) character set utf8mb4 collate utf8mb4_general_ci not null,
  `a_code` varchar(10) character set utf8mb4 collate utf8mb4_general_ci default null,
  `b_code` varchar(10) character set utf8mb4 collate utf8mb4_general_ci default null,
  primary key (`id`) using btree
) engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci row_format=dynamic
1 row in set (0.00 sec)

error: 
no query specified

修改所有列 collate，实际上只修改 a_code、b_code 列 collate 也可解决此问题。

alter table cjc.t01 default character set utf8mb4 collate utf8mb4_0900_ai_ci;
alter table cjc.t01 modify column `id` varchar(30) character set utf8mb4 collate utf8mb4_0900_ai_ci not null;
alter table cjc.t01 modify column `a_code` varchar(10) character set utf8mb4 collate utf8mb4_0900_ai_ci default null;
alter table cjc.t01 modify column `b_code` varchar(10) character set utf8mb4 collate utf8mb4_0900_ai_ci default null;

再次执行，问题解决。

select aaa.* from(
select 
@xxx as _xxx,
( select @xxx := group_concat( a_code ) from t01 where find_in_set( b_code, @xxx ) ) as cxxx
from
t01,( select @xxx := 'xxx') b
where @xxx is not null) id,t01 aaa
where
find_in_set( aaa.a_code, id._xxx )
order by a_code;

empty set, 2 warnings (0.00 sec)

查看表结构。

mysql> show create table t01\g;
*************************** 1. row ***************************
       table: t01
create table: create table `t01` (
  `id` varchar(30) character set utf8mb4 collate utf8mb4_0900_ai_ci not null,
  `a_code` varchar(10) character set utf8mb4 collate utf8mb4_0900_ai_ci default null,
  `b_code` varchar(10) character set utf8mb4 collate utf8mb4_0900_ai_ci default null,
  primary key (`id`) using btree
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci row_format=dynamic
1 row in set (0.00 sec)

3. 修改 sql 语句

将 a_code，b_code 列的 collate 在 sql 语句中转换为 utf8mb4_0900_ai_ci。

改写后的sql如下：

select aaa.* from(
select 
@xxx as _xxx,
( select @xxx := group_concat( a_code collate utf8mb4_0900_ai_ci ) from t01 where find_in_set( b_code collate utf8mb4_0900_ai_ci, @xxx ) ) as cxxx
from
t01,( select @xxx := 'xxx') b
where @xxx is not null) id,t01 aaa
where
find_in_set( aaa.a_code collate utf8mb4_0900_ai_ci, id._xxx )
order by a_code;

总结

比较三种解决方案，每种解决方案适用场景不同，请根据实际情况选择解决方案。

  • 修改参数

    适用于数据库是从 5.7 或更低版本升级到 8.0，并且表数量较多、数据量加大。不适用于批量修改所有表、列字符集和排序规则。

  • 修改表 collate

    适用于修改过程会锁表，数据量越大时间越长，使用于数据量小的场景，建议将所有表、列字符集和排序规则改成 8.0 默认值，后续新增表时不指定字符集和排序规则。

  • 修改 sql 语句

    适用于临时查询，改sql影响最小。

以上就是mysql 5.7升级8.0后出现排序规则问题的解决方案汇总的详细内容，更多关于mysql 5.7升级8.0排序规则问题的资料请关注代码网其它相关文章！

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com