25.3 字符集与排序规则引发的异常

字符集（Charset）和排序规则（Collation）属于“平时不注意，一出问题就是疑难杂症”的类型。它们决定了数据如何编码存储、如何比较、如何排序。配置不当会导致乱码、查询结果异常、索引失效，甚至主从复制中断。本节梳理最常踩的几个坑，以及对应的排查和解决方案。

25.3.1 乱码：存储正常，显示全是问号或方块

现象：通过应用程序查看数据库内容，中文全部变成了 ??? 或 �。用命令行工具直接查表，也可能看到乱码。

根因：任何字符集转换的链条中，只要有一环不一致，就会产生乱码。常见的链条是：

客户端字符集 → 连接字符集 → 服务器字符集 → 库字符集 → 表字符集 → 列字符集

任何一个环节使用了 latin1（不支持中文）而其他环节是 utf8（或 utf8mb4），数据在转换过程中就会丢失字节。典型的场景：

客户端连接时没有设置字符集，默认使用了 latin1，但列是 utf8，存入时发生截断。
服务器默认字符集是 latin1，建库时没有显式指定字符集，新建库继承了服务器的 latin1，导致表创建在 latin1 中，中文根本存不进去。

排查步骤：

通过 SHOW CREATE DATABASE your_db; 查看库的字符集。
通过 SHOW CREATE TABLE your_table; 查看表的字符集。
查看全局变量：

   SHOW VARIABLES LIKE 'character_set_server';
   SHOW VARIABLES LIKE 'character_set_database';
   SHOW VARIABLES LIKE 'character_set_client';
   SHOW VARIABLES LIKE 'character_set_connection';
   SHOW VARIABLES LIKE 'character_set_results';

确认客户端程序或连接池的连接字符串中是否指定了 useUnicode=true&characterEncoding=UTF-8（Java）或 charset=utf8mb4（Python 等）。

解决方案：

建库建表时显式指定字符集，这是最可靠的方式：

  CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  CREATE TABLE t1 (...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

在服务端配置文件中设置默认字符集：

  [mysqld]
  character-set-server=utf8mb4
  collation-server=utf8mb4_unicode_ci

客户端连接时设置字符集：对于命令行工具，可以在连接后执行 SET NAMES utf8mb4;；对于应用程序，在连接池中配置好字符编码参数。

特别提醒：MySQL 的 utf8 是一个阉割版本，只支持最长 3 字节的字符，无法存储表情符号（emoji）等 4 字节字符。一定要使用 utf8mb4，它是真正的 UTF-8 完整实现。

25.3.2 隐式类型转换导致索引失效

现象：一个简单的等值查询，明明在条件列上建立了索引，但 EXPLAIN 显示 type=ALL 全表扫描。检查 SQL 和表结构，发现列字符集或排序规则不一致。

根因：MySQL 在执行比较或连接操作时，如果两端字符集或排序规则不一致，会触发隐式类型转换，将一端的值转换为另一端的格式。这会导致：

索引列被函数或转换包裹，优化器无法使用索引。
转换发生在每一行上，性能急剧下降。

示例：

表 user 中 name 列字符集是 utf8mb4，而应用程序发送的参数是字符串常量，默认继承当前连接的字符集。如果连接的字符集是 utf8 （非 utf8mb4），MySQL 可能会认为需要转换。更常见的场景是两个表的关联字段字符集不同：

CREATE TABLE a (id INT, name VARCHAR(50) CHARSET utf8mb4);
CREATE TABLE b (id INT, name VARCHAR(50) CHARSET utf8);

SELECT * FROM a JOIN b ON a.name = b.name;

这时 MySQL 必须将 utf8mb4 降级为 utf8 或反之，从而导致 name 上的索引无法使用。

排查：使用 EXPLAIN 观察 Extra 列，若出现 Using where 且 key 列为空，同时结合表结构确认两边的字符集。或使用 SHOW WARNINGS 查看优化器重写的 SQL，其中可能会暴露隐式转换函数。

解决方案：

保持数据库中所有关联列（以及表、库）的字符集和排序规则完全一致。推荐统一使用 utf8mb4 + utf8mb4_unicode_ci。
若已存在不一致，需要 ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 将表转换。注意该操作会重写表数据，大表需在低峰期在线操作（可使用 pt-online-schema-change 等工具）。
避免在连接时不设置或误设置字符集。

25.3.3 排序规则冲突：`Illegal mix of collations` 错误

现象：执行查询时直接报错：

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

根因：当两个具有不同排序规则的字符串进行 =、LIKE、CONCAT 等操作，而 MySQL 认为无法自动选择合适的排序规则来解决冲突时，就会抛出此错误。

排序规则决定字符如何比较。比如 utf8mb4_general_ci 与 utf8mb4_unicode_ci 虽然都支持 UTF-8 多字节字符，但比较算法不同，general_ci 速度快但精度低，unicode_ci 基于 Unicode 标准更准确。MySQL 不允许在同一个操作中混合使用。

常见原因：

显式地在 SELECT 中对字符串常量指定了不同的排序规则，比如 SELECT name COLLATE utf8mb4_bin FROM t1; 与另一列排序规则不同。
视图或存储过程中使用了与表列不同的排序规则。
临时表创建时继承了数据库默认的排序规则，而数据库默认与表不一致。

解决方案：

在查询中显式通过 COLLATE 关键字统一排序规则，如 ON a.name COLLATE utf8mb4_unicode_ci = b.name。但这会阻碍索引使用，不推荐作为最终方案。
从源头解决：确保数据库、表、列统一使用同一种排序规则，比如 utf8mb4_unicode_ci。检查所有表的排序规则：

  SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION 
  FROM information_schema.TABLES 
  WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys');

发现有差异的，通过 ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 统一。

查看数据库默认排序规则：

  SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA WHERE schema_name='your_db';

若与表不一致，可修改库默认值：

  ALTER DATABASE your_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

25.3.4 主从复制中字符集差异导致的幻读或中断

现象：主库执行某种字符集操作正常，从库同步 SQL 线程报错或出现数据不一致。

根因：主从库的 character_set_server 或 collation_server 不一致，或者在主库的上下文（连接字符集等）与从库中继日志执行环境不同，导致某些依赖当前会话字符集的函数（如 CHAR()）产生的值在主从上有差异。更危险的是，LOAD DATA 操作中主库的客户端字符集与从库回放时的环境不同，导致数据损坏。

典型场景：

主库用 utf8mb4，从库 character_set_server 是 latin1，Binlog 中的 DDL 语句转换为从库默认字符集，导致列定义被更改。
执行 INSERT INTO t VALUES (CHAR(128512))，CHAR() 根据当前连接字符集生成字符，如果主从连接字符集不同，结果可能不一致。

排查：

比较主从全局变量：

  SHOW GLOBAL VARIABLES LIKE 'character_set_%';
  SHOW GLOBAL VARIABLES LIKE 'collation_%';

开启 binlog_rows_query_log_events 观察 binlog 中具体的执行语句。
检查从库 SQL 线程错误日志。

解决方案：

主从所有字符集相关配置保持完全一致，特别是 character_set_server、collation_server，以及 character_set_client、character_set_connection、character_set_results 在应用连接和复制连接中的表现也要尽量统一。
避免在 SQL 中使用依赖会话字符集的函数，或显式指定字符集避免歧义，如 CHAR(128512 USING utf8mb4)。
若已有不一致，建议通过调整配置文件统一从库字符集，并重建复制（或对已有差异的表进行校验修复）。

25.3.5 字符串比较的“意外”行为

现象：两个看起来相同的字符串，在 WHERE col = 'a' 时匹配不到，或者排序结果不符合直觉。

根因：这可能依赖于列的排序规则。例如：

大小写不敏感：utf8mb4_general_ci（ci 代表 case insensitive），'a' 和 'A' 相等。如果列使用了 utf8mb4_bin（二进制比较），则 'a' 和 'A' 不相等。这可能导致查询漏掉数据或排序诡异。
尾部空格：大多数排序规则会将尾部空格视为相等（如 'abc' 和 'abc ' 在比较时相等），但 utf8mb4_bin 在某些情况下会对尾部空格敏感（实际上 MySQL 8.0 的 PAD SPACE 特性会忽略尾部空格，需要具体验证）。可能导致唯一键冲突判断异常。
德语字母 ß 在 utf8mb4_unicode_ci 下可能与 ss 相等，这是 Unicode 的标准行为，可能引起无意中的重复键冲突。

排查：查看列的排序规则：

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_NAME='your_table';

解决方案：

如果你的业务需要严格区分大小写，使用 utf8mb4_bin 或 utf8mb4_0900_as_cs（MySQL 8.0 支持）。但要注意，二进制比较会影响排序（基于码点），可能与自然语言顺序不一致。
通常建议在系统内部使用 utf8mb4_unicode_ci（无重音、大小写不敏感），这最接近普通业务的期望。个别列如邮箱，可考虑 utf8mb4_bin 以确保唯一性，但逻辑需保持一致。
在比较时明确预期：如果希望大小写敏感，可以用 BINARY 关键字或 COLLATE 转换。

25.3.6 最佳实践总结

为避免字符集与排序规则的相关问题，建议遵循以下原则：

统一使用 utf8mb4 字符集，搭配 utf8mb4_unicode_ci（或 utf8mb4_0900_ai_ci）作为默认排序规则。这能满足绝大多数国际化和多语言需求，并且避免因多字节字符（如 emoji）引发的存储障碍。
从库到表到列，全部对齐：在服务器、数据库、表、列层面都设置同样的字符集和排序规则，减少隐式转换。
连接配置显式指定：在应用程序的连接池或数据库驱动中明确配置连接字符集，例如 Django 的 CHARSET: 'utf8mb4'。
检查并统一已有环境：对存量数据库运行诊断脚本，找出不一致的表，利用 ALTER TABLE ... CONVERT TO 逐步修正。
迁移或新建项目时，将字符集配置写入配置文件并固化，避免手动操作忽略。

字符集的问题像潜伏的杂草，一根根拔掉可能费力，但修复后在系统稳定性上的回报是长期的。一旦发现问题，就彻底治理，不要用临时方案掩盖。

25.3 字符集与排序规则引发的异常

25.3.1 乱码：存储正常，显示全是问号或方块

25.3.2 隐式类型转换导致索引失效

25.3.3 排序规则冲突：Illegal mix of collations 错误

25.3.4 主从复制中字符集差异导致的幻读或中断

25.3.5 字符串比较的“意外”行为

25.3.6 最佳实践总结

25.3.3 排序规则冲突：`Illegal mix of collations` 错误