摘要：字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。
本文分享自华为云社区《一个字符校对规则引发的血案》，原文作者：DRS技术快客 。
问题现场
我们先看一个建表语句
当我们在MySQL5.7和MySQL8.0上建表，都能建成功，但是当建成功之后，我们都执行SQL：SELECT * FROM collate_test WHERE val1=val2的时候：
在5.7上执行
在8.0上执行
mysql>SELECT * FROM collate_test WHERE val1=val2;ERROR1267 (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上执行
showcreatetablecollate_test;+--------------+--------------------------------------------------------------------------------------------------------------------------------------+| Table |CreateTable|+--------------+--------------------------------------------------------------------------------------------------------------------------------------+| collate_test |CREATETABLE`collate_test`(`val1`char(32)DEFAULTNULL,`val2`char(32)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 |+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
在8.0上执行
showcreatetablecollate_test;+--------------+--------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+--------------+--------------------------------------------------------------------------------------------------------------------------------------+|collate_test|CREATETABLE`collate_test`(`val1`char(32)DEFAULTNULL,`val2`char(32)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4|+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
看出来差别了，8.0上建表的时候，被加上了collate属性
在8.0执行
原来8.0中建表的时候，当指定字符集为utf8mb4的时候，它的默认collation就是utf8mb4_0900_ai_ci，而mysql不允许两个互斥的校验规则的数据做对比，而utf8mb4_0900_ai_ci与utf8mb4_general_ci是互斥的
扩展问题
这里面问题比较简单，因为一般我们不会对同一个表的不同字段设置相同字符集不同校对规则，但是在不同的表结构之前，我们有可能不经意之间就犯了这个错误，例如，联表，触发器等。
联表查询
比如下面两个表
当我们联表查询的时候
mysql>select*fromcollate_general,collate_0900whereval1=val2;ERROR1267(HY000):Illegal mix ofcollations(utf8mb4_general_ci,IMPLICIT)and(utf8mb4_0900_ai_ci,IMPLICIT)foroperation '='
触发器
比如我们先建一个表和触发器(为举例需要，触发器并无实际意义)
当我们向表中插入数据的时候
mysql>insertintocollate_triggervalues('abc');ERROR1267(HY000):Illegal mix ofcollations(utf8mb4_general_ci,IMPLICIT)and(utf8mb4_0900_ai_ci,IMPLICIT)foroperation '='
然后我们看一下建表语句
mysql>showcreatetablecollate_trigger;+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|Create Table|+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+|collate_trigger|CREATETABLE`collate_trigger`(`val1`char(32)COLLATEutf8mb4_general_ciDEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=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_SUBSTITUTIONSQLOriginal Statement:CREATEDEFINER=`root`@`localhost`TRIGGER`trigger_0900`AFTERINSERTON`collate_trigger`FOREACHROWBEGIN DECLARE val2 VARCHAR(32); SET val2=new.val1; SELECT val1 into val2 from collate_trigger WHERE val1=val2;ENDcharacter_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版本的时候，字符集校验规则一定要注意了
网友评论