字符集与排序规则冲突

问题现象

报 Illegal mix of collations，表情写入失败，或者大小写比较结果与预期不同。

适用环境

MySQL 8，跨表 JOIN、旧 utf8/utf8mb3 表与 utf8mb4 表混用。

本文是可复用的排障示例，不代表某次个人生产事故。命令中的名称、账号和数据均为示例，执行前请替换为已确认的目标。

复现或诊断步骤

1. 查询数据库、表、列与连接的字符集和排序规则；列级配置可能覆盖表默认值。
2. 找到具体比较表达式，检查参数连接编码、字面量和临时表。
3. 在样本数据上测试大小写、重音、中文和表情，确认业务期望的相等语义。

原因分析

字符集决定可编码字符，排序规则决定比较与排序。连接 utf8mb4 不会自动修改历史列；不同 collation 的优先级和强制转换可能造成冲突。改变规则还可能让原本不同的值在唯一索引中相等。

修复示例

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SHOW FULL COLUMNS FROM customer;

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND CHARACTER_SET_NAME IS NOT NULL;

统一新建表和连接的编码策略。历史转换前备份结构与数据，在克隆库检查唯一键碰撞、索引长度、执行耗时和锁影响，再制定迁移。临时 COLLATE 只能使用兼容字符集，并需复测索引使用；失败时恢复备份或原列，不能只改默认值。

验证方法

端到端写入读取中文、表情和大小写样本，核对字节与排序结果。查看 JOIN 计划确认修复没有引入全表转换；检查所有应用连接而非只检查管理终端。

常见误区

ALTER DATABASE 只改变未来对象默认值。CONVERT TO CHARACTER SET 不是修复乱码的万能手段，历史字节错误应先确定原始编码，避免二次损坏。

官方参考链接

• MySQL 字符集与排序规则

相关分类：数据库与缓存 · 全部笔记