【本期推荐】8岁小朋友的儿童节，有点硬核，一起来认识这些小小程序员，看他们如何coding出一个与众不同的童年。
摘要： 字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。
本文分享自华为云社区《一个字符校对规则引发的血案》，原文作者：DRS 技术快客 。
问题现场
我们先看一个建表语句
CREATE TABLE collate_test (val1 char(32) COLLATE utf8mb4_general_ci,val2 char(32)) CHARACTER SET utf8mb4;
复制代码
当我们在 MySQL5.7 和 MySQL8.0 上建表，都能建成功，但是当建成功之后，我们都执行 SQL：SELECT * FROM collate_test WHEREval1=val2 的时候：
在 5.7 上执行
mysql> SELECT * FROM collate_test WHERE val1=val2;Empty set (0.00 sec)
复制代码
在 8.0 上执行
mysql> SELECT * FROM collate_test WHERE val1=val2;ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
复制代码
很奇怪，为什么会出现 utf8mb4_0900_ai_ci 呢
我们查看 MySQL 的资料https://dev.mysql.com/doc/refman/8.0/en/charset-mysql.html发现，原来 MySQL8.0 在 UTF8mb4 字符集下面的默认排序规则为 utf8mb4_0900_ai_ci
现场分析
然后我们再分别来看一下建表语句：SHOW CREATE TABLE collate_test
在 5.7 上执行
show create table collate_test;+--------------+--------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------------+--------------------------------------------------------------------------------------------------------------------------------------+| collate_test | CREATE TABLE `collate_test` ( `val1` char(32) DEFAULT NULL, `val2` char(32) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
复制代码
在 8.0 上执行
show create table `collate_test`;+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| collate_test | CREATE TABLE `collate_test` ( `val1` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `val2` char(32) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
复制代码
看出来差别了，8.0 上建表的时候，被加上了 collate 属性
在 8.0 执行
mysql> SHOW CHARACTER SET WHERE Charset="utf8mb4";+---------+---------------+--------------------+--------+| Charset | Description | Default collation | Maxlen |+---------+---------------+--------------------+--------+| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |+---------+---------------+--------------------+--------+1 row in set (0.01 sec)
复制代码
原来 8.0 中建表的时候，当指定字符集为 utf8mb4 的时候，它的默认 collation 就是 utf8mb4_0900_ai_ci，而 mysql 不允许两个互斥的校验规则的数据做对比，而 utf8mb4_0900_ai_ci 与 utf8mb4_general_ci 是互斥的
扩展问题
这里面问题比较简单，因为一般我们不会对同一个表的不同字段设置相同字符集不同校对规则，但是在不同的表结构之前，我们有可能不经意之间就犯了这个错误，例如，联表，触发器等
联表查询
比如下面两个表
CREATE TABLE collate_general(val1 char(32)) COLLATE utf8mb4_general_ci;
CREATE TABLE collate_0900 (val2 char(32)) COLLATE utf8mb4_0900_ai_ci;
复制代码
当我们联表查询的时候
mysql> select * from collate_general,collate_0900 where val1=val2;ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
复制代码
触发器
比如我们先建一个表和触发器(为举例需要，触发器并无实际意义)
CREATE TABLE collate_trigger(val1 char(32)) COLLATE utf8mb4_general_ci;
DELIMITER ||CREATE TRIGGER trigger_0900 AFTER INSERT ON collate_trigger FOR EACH ROWBEGIN DECLARE val2 VARCHAR(32); SET val2=new.val1; SELECT val1 into val2 from collate_trigger WHERE val1=val2;END||DELIMITER ;
复制代码
当我们向表中插入数据的时候
mysql> insert into collate_trigger values ('abc');ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
复制代码
然后我们看一下建表语句
mysql> show create table collate_trigger;+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+| collate_trigger | CREATE TABLE `collate_trigger` ( `val1` char(32) COLLATE utf8mb4_general_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
复制代码
我们再看一下触发器
mysql> show create trigger trigger_0900\G*************************** 1. row *************************** Trigger: trigger_0900 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONSQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `trigger_0900` AFTER INSERT ON `collate_trigger` FOR EACH ROW BEGIN DECLARE val2 VARCHAR(32); SET val2=new.val1; SELECT val1 into val2 from collate_trigger WHERE val1=val2;END character_set_client: gbk collation_connection: gbk_chinese_ci Database Collation: utf8mb4_0900_ai_ci Created: 2021-05-31 15:24:44.40
复制代码
发现没有，触发器的 Database collation 为 utf8mb4_0900_ai_ci，在触发器的比较语句中 val1 为 collate_trigger 的字段，collation 为 utf8mb4_general_ci，val2 为触发器 trigger_0900 的自有字段，collation 为 utf8mb4_0900_ai_ci
本文中举例都比较简单直接，客户真实业务场景可能都比较复杂，但是所遇问题的原因都是一样的由此可见，在处理 MySQL 之前的版本升级到 8.0 版本的时候，字符集校验规则一定要注意了
点击关注，第一时间了解华为云新鲜技术~
评论