MySQL 字符串排序结果为什么不符合预期：utf8mb4 校对规则与大小写敏感

同一张商品表里，ORDER BY title 排出来的大小写顺序和预期不一样，往往不是 MySQL 把排序打乱了，而是 title 所属的校对规则决定了哪些字符被视为相等、哪些字符拥有更高权重。先查列级规则，再用 COLLATE 在查询边界明确覆盖，通常比改全库配置更安全。

字符串排序首先由字符集和校对规则决定；想要大小写敏感，优先在需要该语义的表达式上显式使用合适的 COLLATE，并用小样本验证结果。

先用最小数据复现排序差异

不要直接拿生产商品表试错，先建一个只包含四行的临时表。这里故意放入大小写混排的英文和一组带重音的字符，观察规则差异：

CREATE TEMPORARY TABLE sort_demo (
  title VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
);

INSERT INTO sort_demo (title) VALUES ('apple'), ('Apple'), ('ábaco'), ('abaco');

SELECT title
FROM sort_demo
ORDER BY title;

在 utf8mb4_0900_ai_ci 下，比较会忽略大小写和重音差异；当两行的权重相同，不能把它们在结果中的相对位置当成业务排序规则。这个结果先别下结论，先把实际列规则查出来。

校对名称里的 ci、cs 和 ai 到底表示什么

MySQL 的校对名称通常以字符集开头，再用后缀描述比较特征。utf8mb4_0900_ai_ci 的 ci 是 case-insensitive，表示大小写不敏感；ai 是 accent-insensitive，表示重音不敏感。相对地，带有 cs 的规则强调大小写敏感，_bin 则按二进制权重比较。

这解释了一个常见误会：ORDER BY 不是“按字符串的字节从小到大”这么简单。它会先依据表达式的校对规则计算排序权重，再输出结果。列定义、连接会话和显式字符串表达式都可能参与规则选择。

排查顺序：从列级定义追到查询表达式

先看最接近数据的定义，再看查询有没有覆盖：

SHOW FULL COLUMNS FROM products LIKE 'title';

SELECT
  @@character_set_connection AS connection_charset,
  @@collation_connection AS connection_collation,
  CHARSET(title) AS column_charset,
  COLLATION(title) AS column_collation
FROM products
LIMIT 1;

SHOW FULL COLUMNS 能确认列的字符集和校对规则；会话变量用于发现连接初始化是否带来了另一套默认值。应用刚执行过 SET NAMES 时，collation_connection 也值得一起记录。

如果查询里写了字符串字面量、函数或多个字符列，继续检查这些表达式的校对规则。不要只根据数据库默认值推断结果，因为 MySQL 支持在服务器、数据库、表、列和字符串字面量层级分别指定字符集与校对规则。

只让当前查询大小写敏感

如果业务只是后台检索需要区分大小写，不建议为了一个页面改整张表。可以把规则收窄到排序表达式：

SELECT id, title
FROM products
ORDER BY title COLLATE utf8mb4_bin, id;

这里的关键节点是 COLLATE：它让这一次 ORDER BY 使用 utf8mb4_bin。最后追加 id 是为了让同权重行获得稳定的次序，尤其适合分页查询。若还要让筛选本身大小写敏感，应在 WHERE 的比较表达式上也明确写出规则，不能只改排序。

什么时候应该改列定义

当“大小写是否相同”是整个字段的稳定业务语义，例如外部系统代码、区分大小写的标识符或需要唯一约束的短码，才考虑把列定义固定为明确的校对规则：

ALTER TABLE product_codes
  MODIFY code VARCHAR(64)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
  NOT NULL;

改动前先核对现有重复值、索引长度、写入端连接字符集和应用层比较。若原规则把 ABC 与 abc 看成相等，切换为二进制规则后，唯一索引可能允许两者同时存在；反过来也可能在重建约束时发现冲突。

分页、索引和连接时的三个坑

同权重记录会让游标分页不稳定

只用 title 做游标条件时，大小写不敏感规则可能让多行拥有相同排序权重。使用 ORDER BY title COLLATE utf8mb4_bin, id，并让下一页条件与这两个键保持一致。

表达式上的规则可能改变索引收益

查询前用 EXPLAIN 检查访问路径。临时在排序表达式上覆盖规则很直观，但是否能复用已有索引要以实际计划为准；不要只看到结果正确就认为成本没有变化。

连接字符集不一致会把问题带到比较阶段

应用连接建立后要确认 character_set_client、character_set_connection 和 character_set_results。统一使用 utf8mb4 并在需要时显式指定 COLLATE，比依赖驱动默认值更容易维护。

一段可重复的验收脚本

最后把列规则、会话规则和两种排序结果放在一次检查里，便于在开发、预发布和生产连接上对比：

SELECT
  @@collation_connection AS connection_collation,
  COLLATION(title) AS title_collation
FROM products
LIMIT 1;

SELECT title FROM sort_demo ORDER BY title;
SELECT title FROM sort_demo ORDER BY title COLLATE utf8mb4_bin;

验收重点不是某一行必须排在第一，而是规则是否和需求一致：普通展示可接受大小写不敏感；代码、外部编号或唯一键则必须明确是否区分大小写，并让筛选、排序、唯一性和分页采用同一语义。

相关问题

utf8mb4 和 utf8mb4_0900_ai_ci 是一回事吗？

不是。utf8mb4 是字符集，utf8mb4_0900_ai_ci 是该字符集的一种校对规则；前者决定可表示的编码范围，后者决定比较和排序方式。

只在 ORDER BY 中使用 COLLATE 可以吗？

可以，但它只影响排序表达式。若 WHERE、唯一约束或应用层也需要区分大小写，应分别核对并统一语义。

为什么同样的中文数据看不出规则差异？

不同规则的差别不一定在每组中文字符上都明显，应该用业务中真实的大小写、重音、符号和边界样本测试，而不是凭一两行结果判断。

小结

遇到 MySQL 字符串排序异常，先查看列级字符集和校对规则，再检查会话与表达式是否覆盖了默认值。临时需求用 COLLATE 收窄影响范围，稳定业务语义才考虑改列定义；无论采用哪种方式，都给分页补上稳定次键，并用 EXPLAIN 和真实样本复核成本与结果。