点击标题下「蓝色微信名」可快速关注
技术社群的这篇文章《技术分享 | MySQL 5.7 升级 8.0 后,排序规则问题解决方案汇总》给我们讲解了MySQL 5.7升级8.0,其中和排序规则相关的一些实际问题以及解决方案,值得我们学习和了解,并且能应用到实际工作当中。
1问题现象
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'
2问题原因
比较操作中使用不同的字符集或排序规则通常会触发此问题,MySQL 8.0 默认 COLLATE 为 utf8mb4_0900_ai_ci
和 对应列 COLLATE 的 utf8mb4_general_ci
不匹配。
3问题重现过程
创建测试表。
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'
4问题分析
查看默认排序规则。
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
。
5解决方案
- 修改参数
- 修改表 COLLATE
- 修改 SQL 语句
1. 修改参数
参数collation_connection
在客户端局部变量值和全局变量值不一致,如何改成一致?官网参考材料[1]
--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;
6因此
比较三种解决方案,每种解决方案适用场景不同,请根据实际情况选择解决方案。
-
修改参数
适用于数据库是从 5.7 或更低版本升级到 8.0,并且表数量较多、数据量加大。不适用于批量修改所有表、列字符集和排序规则。
-
修改表 COLLATE
适用于修改过程会锁表,数据量越大时间越长,使用于数据量小的场景,建议将所有表、列字符集和排序规则改成 8.0 默认值,后续新增表时不指定字符集和排序规则。
-
修改 SQL 语句
适用于临时查询,改SQL影响最小。
参考资料
[1]
修改字符集变量: https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_character-set-client-handshake
如果您认为这篇文章有些帮助,还请不吝点下文章末尾的"点赞"和"在看",或者直接转发朋友圈,
近期更新的文章:《存储IO性能优化策略、方案与瓶颈分析的场景》
《宫保鸡丁和宫爆鸡丁是一道菜么?》《图解构建知识体系的过程》
《容器数据库相较于传统数据库有什么特点?》
《SDCon 2024全球软件研发技术大会日程和参会指南》
热文鉴赏:《你不知道的C罗-Siu庆祝动作》《架构设计的15个关键概念》
《大阪环球影城避坑指南和功略》
《推荐一篇Oracle RAC Cache Fusion的经典论文》
《"红警"游戏开源代码带给我们的震撼》
文章分类和索引:《公众号1500篇文章分类和索引》