MySQL utf8mb4 排序规则怎样选择大小写行为

MySQL 中，utf8mb4 负责把字符存下来，真正决定 A 和 a 是否被当成相等、排序时谁在前的，是排序规则（collation）。如果业务登录名、邮箱或标签需要忽略大小写，通常选择带 _ci 的规则；如果大小写代表不同值，则选择 _cs，或者在明确需要二进制比较时使用 _bin。

选择大小写行为时，先确定“相等”的业务定义，再看 collation 后缀；不要只把列改成 utf8mb4 就期待它自动区分大小写。MySQL 8.4 中 utf8mb4 的默认规则是 utf8mb4_0900_ai_ci，它默认不区分大小写，也不区分重音。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/charset-general.html

下面的 SQL 都是 MySQL 8.4 语义示意，重点是展示选择和排查方法，不把示例输出写成已经在本机执行的结果。

utf8mb4 和 collation 分别负责什么

utf8mb4 是字符集，决定字符串采用哪种编码并支持哪些 Unicode 字符；collation 是比较和排序规则，决定字符的权重以及大小写、重音等差异是否被保留。两者经常一起写在列定义中，但职责不能混为一谈。

CREATE TABLE account_name (
    id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    user_name VARCHAR(80)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_as_cs
        NOT NULL,
    -- 这个唯一索引会沿用 user_name 的排序规则判断是否重复。
    UNIQUE KEY uk_account_name (user_name)
) ENGINE = InnoDB;

这里的 utf8mb4 让列可以保存完整 Unicode 字符，utf8mb4_0900_as_cs 则把大小写和重音差异纳入比较。唯一索引不是“只比较字节”的开关，它会继承列的比较语义，所以排序规则的选择会直接影响哪些值可以同时插入。

先看后缀：_ci、_cs、_bin 和 _ai、_as

MySQL 的排序规则名称通常把关键行为写在后缀里。可以先用下面这张速查表缩小候选范围：

例如，utf8mb4_0900_ai_ci 同时是不区分重音和大小写；utf8mb4_0900_as_cs 同时区分重音和大小写。不要看到 utf8mb4 就直接断定行为，还要完整读取后缀。MySQL 8.4 的 0900 系列基于 Unicode Collation Algorithm 9.0.0，适合希望使用较新 Unicode 排序规则的场景。

-- 用 SHOW COLLATION 先查看当前服务器实际提供的 utf8mb4 规则。
SHOW COLLATION
WHERE Charset = 'utf8mb4'
  AND Collation IN (
      'utf8mb4_0900_ai_ci',
      'utf8mb4_0900_as_cs',
      'utf8mb4_bin',
      'utf8mb4_0900_bin'
  );

如果候选规则的名称和版本不确定，先查询服务器支持的清单。不要只凭别的环境里的默认值写迁移脚本，因为服务端版本和字符集默认规则可能不同。

默认规则、列级规则和查询级 COLLATE 怎么选

三层选择可以按“稳定语义放低层、临时例外放高层”来理解：

• 数据库或表级默认值，适合统一的基础约定，但不应代替关键业务列的明确声明。
• 列级 COLLATE，适合用户名、外部编号、邮箱等长期稳定的比较语义，也会影响排序、分组和唯一索引。
• 查询级 COLLATE，适合单次搜索或报表需要的例外，不要把它当成长期修复列定义的替代品。

-- 列默认忽略大小写，但某一次精确查询临时要求区分大小写。
SELECT id, user_name
FROM account_name
WHERE user_name COLLATE utf8mb4_0900_as_cs = 'Alice';

-- 排序也可以显式指定规则，避免连接默认值改变结果。
SELECT user_name
FROM account_name
ORDER BY user_name COLLATE utf8mb4_0900_as_cs;

查询级覆盖只改变这次表达式的比较方式，不会改变列已有的唯一约束或索引语义。如果“大小写是否相等”是这个字段的永久业务规则，应优先在列定义中表达，并为迁移和索引变更安排回归检查。

用最小对照确认大小写和重音行为

选定候选后，至少对照大小写、重音和尾随空格三个维度。下面只展示比较思路，输出中的布尔值应以目标实例实际执行为准：

-- 用显式字符集和排序规则隔离连接默认值的影响。
SELECT
    _utf8mb4'A' = _utf8mb4'a' COLLATE utf8mb4_0900_ai_ci AS ci_same_case,
    _utf8mb4'A' = _utf8mb4'a' COLLATE utf8mb4_0900_as_cs AS cs_same_case,
    _utf8mb4'e' = _utf8mb4'é' COLLATE utf8mb4_0900_ai_ci AS ai_same_accent,
    _utf8mb4'e' = _utf8mb4'é' COLLATE utf8mb4_0900_as_cs AS as_same_accent;

-- 查看排序规则自身的 PAD 属性，避免把尾随空格差异误判成大小写差异。
SELECT COLLATION_NAME, PAD_ATTRIBUTE
FROM INFORMATION_SCHEMA.COLLATIONS
WHERE COLLATION_NAME IN ('utf8mb4_bin', 'utf8mb4_0900_bin');

MySQL 8.4 文档说明，utf8mb4_0900_ai_ci 属于 NO PAD 系列，而传统的 utf8mb4_bin 是 PAD SPACE；utf8mb4_0900_bin 也是 NO PAD。因此，二进制规则之间也不能只凭“都是 bin”推断所有字符串比较完全一样，尤其要注意尾随空格。

改排序规则时，索引和唯一性会一起变化

把一列从 _ci 改成 _cs，不只是改变某个查询的显示顺序，还可能改变以下结果：

• 等值条件：Alice 与 alice 是否匹配。
• 排序和分组：大小写或重音不同的值是否拥有相同的排序权重。
• 唯一约束：原本被认为重复的两行，改规则后可能同时合法；反过来也可能出现迁移冲突。
• 索引计划：列排序规则变化会触发索引重建或影响已有索引的使用方式，迁移前要按实例容量和停机要求安排。

-- 迁移前先确认列当前的字符集、排序规则和索引定义。
SHOW FULL COLUMNS FROM account_name;
SHOW CREATE TABLE account_name;

-- 只有确认重复数据、索引重建和应用连接规则后，才执行列变更。
ALTER TABLE account_name
    MODIFY user_name VARCHAR(80)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_as_cs
    NOT NULL;

这类 ALTER TABLE 不是可以无条件复制到生产环境的“快捷修复”。先用代表性数据找出大小写不同但当前被视为相等的值，再决定是保留两条、合并数据还是调整业务规则。应用连接的 character_set_connection 与 collation_connection 也要纳入排查，否则代码中的字面量和列可能发生隐式协商。

几个容易选错的边界

• 把 utf8mb4 当成大小写开关：字符集和比较规则是两个层次，必须看完整的 collation 名称。
• 所有搜索都改成 _cs：严格区分大小写可能让用户搜索体验变差，搜索字段与身份标识应分别建模。
• 把 _bin 当成“更安全的 _cs”：二进制排序还涉及字符权重和 PAD 属性，只有明确需要时才使用。
• 只测试 A/a：重音、语言特定排序、尾随空格和特殊 Unicode 字符可能有不同结果，应使用业务代表值。
• 只改查询不改唯一约束：查询级 COLLATE 不会回溯改变列级索引已经采用的比较语义。

相关问题

utf8mb4_0900_ai_ci 和 utf8mb4_0900_as_cs 有什么区别？

前者不区分重音和大小写，后者同时区分重音和大小写。它们不仅影响 WHERE 等值比较，也影响排序、分组和唯一索引。

只想让一次查询区分大小写，必须改表吗？

不必。可以在表达式上使用兼容字符集的 COLLATE utf8mb4_0900_as_cs。但如果这个规则是字段的长期业务含义，仍应评估列级定义和索引，而不是让每个调用方自行拼接。

utf8mb4_bin 和 utf8mb4_0900_bin 应该怎么选？

两者都属于二进制排序，但 PAD 属性不同；应在目标 MySQL 版本中查看 INFORMATION_SCHEMA.COLLATIONS，并用包含尾随空格的代表值做对照。不要只因为名称相似就认为它们完全等价。

最终可以把选择压缩成一句话：普通自然语言搜索先考虑 _ci，身份标识或确实区分大小写的业务考虑 _cs，只有需要二进制排序语义时再考虑 _bin；无论选哪一种，都要把重音、尾随空格、唯一索引和应用连接规则一起纳入对照。