报错

1267：不合法的混合字符集

之前写了个简单工具来维护我的一些个人记录。随着发展，这个工具无法满足我的进一步需求，就做了些改动。跟着而来的就是原本本地运行的程序，得放到云上去了。做了新环境，部好了程序。测试时发现程序未按预期执行，看程序日志，喜提报错（没错，这是个python程序脚本）：

pymysql.err.OperationalError: (1267, "Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='")

问题的产生

根据 一个字符校对规则引发的血案 这篇文章的总结，产生的原因就是在MySQL 8.0建表时，如果指定字符集是utf8mb4的话，表的默认collation是utf8mb4_0900_ai_ci。utf8mb4_0900_ai_ci与utf8mb4_general_ci是互斥的，而MySQL不允许两个互斥的校验规则的数据做对比。

那CHARSET和COLLATE又是什么鬼呢？

• MySQL的字符集(Character Set)和校对集(Collate)
• mysql 中 character set 与 collation 的点滴理解
• MYSQL中的COLLATE是什么？

通读上面这些文章并梳理后，我们可以得到以下这些平时并没有什么鸟用的知识点：

1. 字符集的层次有4个：服务器(server)，数据库(database)，数据表(table)和连接(connection) -- 注：我对这条有质疑
2. database character set, table character set, field character set 可逐级覆盖
3. 使用字符集的过程：服务器收到来自 client 的查询，将查询串从 character_set_client 转换到 character_set_connection，服务器将转换后的查询的执行结果转换到 character_set_results 字符集后发回
4. 如何用sql语句来查看client、connection、server、results的字符集设置：SHOW VARIABLES LIKE '%character_set%';
5. set names N语句是同时设置client、connection、results字符集为N的合并简写（意思就是，无论怎样，server你不能搞，别的我都不屌）
6. 如何查看系统的所有字符集：SHOW CHARACTER SET;
7. mysql有两个支持unicode的character set：1，ucs2 - 使用16 bits来表示一个unicode字符；2，utf8 - 使用1~3 bytes来表示一个unicode字符（ utf8mb4 ）
8. character set与collation有对应关系（每个character set会对应一定数量的collation），可以通过语句查看系统的Collation及对应关系：show collation;
9. collation的命名规则有两类：<character set>_<language/other>_<ci/cs>和<character set>_bin
10. 同一个character set的不同collation的区别在于排序、字符串对比的准确度（相同两个字符在不同国家的语言中的排序规则可能是不同的）以及性能
11. 凡是涉及到字符类型比较或排序的地方（ORDER BY，WHERE条件中大于小于号的筛选结果，DISTINCT、GROUP BY、HAVING），都会和COLLATE有关
12. 仅对utf8mb4_unicode_ci和utf8mb4_general_ci做对比时，utf8mb4_unicode_ci可能更正确那么一丢丢
13. 设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置
14. utf8mb4_0900_ai_ci大体上就是unicode的进一步细分，0900指代unicode比较算法的编号（Unicode Collation Algorithm version），ai表示accent insensitive（发音无关），例如e, è, é, ê和ë是一视同仁的

如果想了解更多信息，可访问：

• MySQL字符集详解

问题的验证

知道这些应该已经差不多了吧……接下来需要验证一下我遇到的报错到底发生在哪里。

查看一下报错的SQL：是一条对视图的select，有附加的where语句。放在navicat中单独执行这条sql，是没有问题的…… <- 请注意我的这句话

再看看数据库的collation：show variables like 'collation%';（根据无用知识点的举一反三）。因为我的数据库是由navicat直接同步表结构和数据的，所以表的collation我就不再看了……

mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)

问题的解决

• stackoverflow 上的高票答案列举了4种解决办法，并且强调了上面无用知识点中的第12条 UPDATE: utf8mb4/utf8mb4_unicode_ci is now the preferred character set/collation method. utf8_general_ci is advised against, as the performance improvement is negligible. See https://stackoverflow.com/a/766996/1432614
• 阿隆索逃跑了 除了解决问题，还记录了ta验证问题的更多步骤

该是终结这一切的时候了！

su -
systemctl restart mysql.service  // 目的是为了断开当前所有的连接
mysql -uroot -p  // 连接到数据库

ALTER DATABASE `dbname` COLLATE 'utf8mb4_general_ci';
^d

systemctl restart supervisor.service
^d

恩，是检验成果的时候了……WTF，报错依旧啊……

好吧，再重新看一遍报错的sql语句吧……是对一个视图进行查询。那视图的定义是什么呢？细看。

SELECT wkey AS wkey, wvalue AS wvalue, 'user_a' AS uname 
FROM config_a 
UNION ALL 
SELECT wkey, wvalue, 'user_b' 
FROM config_a
...

我是针对uname这个字段进行的查询，而这个字段中的数据并不存储在数据库中，而是直接由视图语句生成出来的，所以可以做出如下推断（？）：collation_database并不能控制sql语句产生的字段。既然这个字段无法控制，那collation_server字段总应该行了吧？

该是终结这一切的时候了！

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf  -- 这个是ubuntu上mysql的配置文件

// 在 [mysqld] 段落下添加配置项
// 配置依据 https://dev.mysql.com/doc/refman/8.0/en/charset-applications.html

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

sudo systemctl restart mysql.service
sudo systemctl restart supervisor.service

恩，又是检验成果的时候了……纳尼？玩我的是吧！摔！！

冷静。如果我上述的操作已经可以保证字段是utf8mb4_general_ci的话，那报错中的utf8mb4_0900_ai_ci，应该是指我传给服务器的数据了？（事实上，我是照着这样的思路去想的，但并没有去验证）看样子还得修改collation_connection。

该是终结这一切的时候了！

// 在 [mysqld] 段落下 再！添加配置项
// 配置说明见
// https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_init_connect
// https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html

init-connect='SET NAMES utf8mb4'
init_connect='SET collation_connection = utf8mb4_general_ci'
skip-character-set-client-handshake

// ...重启mysql和supervisor

恩，又又是检验成果的时候了……泥马！！！开发环境正常执行，线上环境你还给我报错？！日狗了么！再TM搞不定，我就把5.7给装过来！这都快成玄学了啊……

• 关闭 supervisor
• 关闭 mysql client
• 重启 mysql
• 打开 mysql client 从线上环境删除所有视图，重新向线上环境同步视图
• 启动 supervisor
• 做好挂b的准备，重新测试

问题解决了……留下懵逼的我在风中凌乱……

扩展资料（我没细读，只是好奇罢了。未来遇到问题时再细看）：

• Mysql8 和mysql 5.7 的区别
• MySQL升级8.0的新故障，utf8mb4_0900_ai_ci是啥？
• 深入Mysql字符集设置