前段时间， 项目中经常会出现报错， 显示
pymysql.err.OperationalError: (1267, "Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='")
之前没有遇到过的问题，所以做了一下了解， 那为什么会出现这种问题呢？ 本文详解
1. 报错原因
最浅显直观的原因：项目规定使用5.x数据库，有的同事使用了8.x数据库， 导致同样是utf8mb4的字符集， 但是collations 不同，数据之间无法进行比较
2. 不同字符集与collations之间有啥区别
1. utf8 与 utf8mb4
utf: 全名是Unicode Transformation Format, 翻译过来就是 Unicode变换格式
utf8: 又名utf8mb3, 一个字符最多使用3个字节来存储， 存储范围为0x000到0xFFFF之间的字符
utf8mb4: utf8mb3的超集，可以存储更多字符， 包括中文生僻字，以及比较著名的 emoji表情
378CCF38.png
详细解释：
Unicode诞生之初， 是为了解决各国直接编码各成一体的问题，促进计算机界的 "书同文，车同轨"，有了Unicode, 理论上地球上的所有文字都有了自己独一无二的编码(Code Point,身份证)。
但是，Unicode只给了独一无二的编码， 至于如何存储以及传输解析这些编码，那就要引出今天的另一个主角 UTF-8，它是解决存储以及传输问题的实际方案，它会标明，存储以及传输读取时， 几个字节为一组， 是Unicode的编码规则之一。
既然是之一， 那肯定还有其他的编码规则， 对的， 除了UTF-8之外， 还有UTF-16,UTF-32等， 但是UTF-8编码规则是使用最广泛的编码规则，因为它是变长编码，兼容ASCII码字符，如果只传输ASCII字符，则每个字符只需要一个字节。因此，如果数据中包含大量的ASCII字符，那么UTF-8可以节省很多存储空间。
在mysql中， 字符集设置时为character set utf8， 没有中划线，并且，使用的是utf8 而不是Unicode, 因为Unicode只是一个字符集
2. utf8mb4_general_ci 与 utf8mb4_0900_ai_ci
utf8mb4_0900_ai_ci： 以下划线分割， utf8mb4上文做了解释，0900是Unicode9.0规范， ai是accent insensitivity的缩写也就是“不区分音调”， ci是case insensitivity，也就是“不区分大小写”
Unicode规范是在不断更新的，每次更新既包括扩充，也包括修正。比如6.0版新加入了222个中日韩统一表义字符(CJK Unified Ideographs)，7.0版加入了俄国货币卢布的符号等，如果支持新的Unicode规范，就可以直接享受好处，像对待普通字符那样对待这些新字符，当然是好事
但是，虽然MySQL也会跟随Unicode的更新，但速度太慢了。MySQL 5.7的第一个发行版MySQL 5.7.1是2013年4月23日面世的，它包含的最新的Unicode规范是Unicode 5.2(发布于2009年10月)。即便是2020年1月13日发布的MySQL 5.7.29，仍然是这样。
然而Unicode规范早已升级了很多版，即使是mysql 8.0使用的Unicode9.0版本，也发布于2016年6月，过去了好多年了。到目前为止，最新的版本已经到了12.1，发布于2019年5月。所以从5.2更新到9.0，看起来是一大进步，其实也只是补课而已
Unicode版本.png
所以，utf8mb4_0900_ai_ci到底是个什么东西呢？其实，它是个collation
Unicode是字符集， utf8是编码格式， 而collation 是一个处理字符与字符之间关系的定义
collation定义了哪个字符和哪个字符是“等价”的。所以如果指定“不区分大小写”，那么a和A，e和E就是等价的，这样查找时就会方便很多。但这还不够，世界上的文字很多，所以才会有“不区分音调”的要求，这时候e、ē、é、ě、è就是等价的，那么假设我们要进行拼音查找，只要按e去找就可以全部列出来，很方便。甚至，它们也和ê、ë也是等价的，这样就更方便了。
collation也定义了字符的排序规则，如果按照“字符顺序(而不是简单的‘字母顺序’)”来排序，哪个字符应当排在哪个字符前面。所以，尽管“啊”、“副”、“德”三个字的拼音开头分别为A、F、D，但直接选定collation为utf8mb4，它们并不会按照“啊”、“德”、“副”的顺序排序，而是会排成“副”、“啊”、“德”。如果你希望把中文字符按照拼音来排序，指定使用gb18030_chinese_ci作为collation就可以了。
当然，要补充的是，collation依赖于字符集(character set)，所以把gb18030_chinese_ci作为collation，就要求字符集是gb18030，而不能是utf8mb4。
到这里，我的疑惑就解开了。MySQL 8.0之后，默认collation不再像之前版本一样是是utf8mb4_general_ci(这个名字也确实取得有问题，话说得太满，有点自负了)，而是统一更新成了utf8mb4_0900_ai_ci。
不幸的是，mysql 5.x 建的各种数据表，它们的collation仍然是utf8mb4_general_ci，而mysql8.x建的表是utf8mb4_0900_ai_ci。如果恰好遇到包含字符串相等或者大小比较的联表查询语句，而关联的表又使用了不同的collation，MySQL就无法决策到底应当使用哪个，就会报错。
既然如此，解决办法也很简单，用alter table table_name collate utf8mb4_0900_ai_ci显式统一所有表的collation，问题就解决了。
把character set和collation分开，到底有什么好处？其实好处很多。如果把字符看作个人，character set就相当于验明正身，给每个字符发张身份证，而collation相当于告诉大家，排队的时候谁在前谁在后。collation有多套，就相当于可以灵活按身高、体重、年龄、出身地等等因素来排序，却完全不会受到身份证号的干扰。
思考
- unicode, utf8, collation 分开， 有什么好处， 我们在平时做代码设计时， 是否也可以沿用这种思想
网友评论