如何选择合适的字符集和排序规则（utf8 vs utf8mb4 vs utf8mb4_0900_ai_ci）？

考察要点

这道题面试官想看你是否深刻理解字符编码与数据比较规则在数据库中的实现，具体考察：

1. 核心概念辨析能力：能否清晰区分字符集（Character Set）和排序规则（Collation）的定义与作用。
2. 实践经验与避坑意识：是否知道 MySQL 中 utf8 与 utf8mb4 的关键区别，以及错误选择 utf8 带来的“天坑”（如 emoji 存储失败）。
3. 知识体系的与时俱进：是否了解 MySQL 8.0 引入的 utf8mb4_0900_ai_ci 相比旧版 utf8mb4_unicode_ci 的优势，体现了你的知识没有停留在旧版本。

核心概念

1. 字符集 (Character Set)

字符集是一套 “字符” 到 “二进制编码” 的映射规则。它决定了数据库能存储哪些字符，以及如何将这些字符以字节形式存放在磁盘上。

• utf8 (别名 utf8mb3): 在 MySQL 中，utf8 是 utf8mb3 的一个别名。它使用 1 到 3 个字节来表示一个字符。它足以编码所有“基本多文种平面”（BMP）中的字符，包括绝大部分常见的中日韩文字，但无法编码需要 4 字节的字符，例如：
  • 各种 Emoji 表情符号 (😂, 🚀)
  • 部分罕见汉字、象形文字
  • 某些数学符号
• utf8mb4: 使用 1 到 4 个字节来表示一个字符，是 真正的 UTF-8 标准 的完整实现。它兼容 utf8mb3，并能存储所有已知的 Unicode 字符。这是当前互联网应用的事实标准。

存储空间对比：utf8mb4 是变长编码。对于纯英文字符，utf8mb4 和 utf8mb3 都只占用 1 个字节；对于常见汉字，都占用 3 个字节。只有在存储 Emoji 等需要 4 字节的字符时，utf8mb4 才会使用 4 字节。因此，对于绝大多数场景，从 utf8 迁移到 utf8mb4 并不会导致存储空间显著膨胀。

2. 排序规则 (Collation)

排序规则定义了在特定字符集下，字符之间如何进行比较和排序。它决定了 ORDER BY、GROUP BY、WHERE col = '...' 以及索引如何工作。排序规则的命名通常遵循 字符集_语言_后缀 的格式。

常见后缀含义：

3. utf8mb4_general_ci vs utf8mb4_unicode_ci vs utf8mb4_0900_ai_ci

• utf8mb4_general_ci: 一种旧的、简化的排序规则。
  • 优点：性能略高（在现代 CPU 上几乎可忽略不计）。
  • 缺点：排序准确性较差，不完全遵循 Unicode 规范，在处理某些特殊语言（如德语 ß 和 ss）时会得到不符合预期的结果。
• utf8mb4_unicode_ci: 基于 Unicode Collation Algorithm (UCA) 4.0.0 实现。
  • 优点：在多种语言环境下，排序和比较结果都非常准确。
  • 缺点：性能略低于 general_ci。
• utf8mb4_0900_ai_ci: MySQL 8.0 的默认排序规则，基于 UCA 9.0.0。
  • 优点：
    1. 更准确：基于更新的 Unicode 标准，修复了旧版 unicode_ci 的一些排序 bug。
    2. 支持重音不敏感（Accent-Insensitive）：这是 ai 的含义，对多语言搜索非常友好（例如，搜索 resume 可以匹配到 résumé）。
    3. 性能优化：在 MySQL 8.0 中，其性能已经超过了 utf8mb4_general_ci，成为兼具准确性和速度的最佳选择。

结论：

• 新项目（MySQL 8.0+）：无脑选择 utf8mb4 和 utf8mb4_0900_ai_ci。
• 旧项目（MySQL 5.7 及以下）：推荐使用 utf8mb4 和 utf8mb4_unicode_ci。

常见低分回答

1. 混淆字符集和排序规则：“我给这个表设置了 utf8mb4_general_ci 字符集”。这是错误的说法，utf8mb4_general_ci 是排序规则，utf8mb4 才是字符集。
2. 推荐使用 utf8：“如果业务不涉及 Emoji，用 utf8 就够了，还省空间”。这是非常危险的短视行为。未来业务需求变更时，修改字符集成本极高。而且 utf8mb4 对非 4 字节字符的存储开销与 utf8 完全相同。直接说“始终应该用 utf8mb4”是更安全的答案。
3. 对 _ci, _cs, _bin 的影响理解不清：例如，在需要精确匹配的场景（如存储区分大小写的激活码、密码 Token）错误地使用了 _ci 排序规则的列，可能导致安全漏洞。'ABC-123' 和 'abc-123' 会被认为是相同的。
4. 忽略不同 collation 混合使用导致的问题：在 JOIN 或 UNION 操作中，如果两个关联字段的排序规则不同，MySQL 会报错 Illegal mix of collations。
   sql

   ⧉⛶

    1-- t1.name 使用 utf8mb4_general_ci
    2-- t2.name 使用 utf8mb4_unicode_ci
    3SELECT * FROM t1 JOIN t2 ON t1.name = t2.name;
    4-- >> ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

5. 认为 utf8mb4 总是占用 4 字节：“用 utf8mb4 会让 VARCHAR(100) 占用 400 字节，太浪费空间了”。这是对变长编码的误解。实际占用的空间取决于存储的字符内容，而不是字符集本身。

工程实践与优化

1. 统一配置：在 my.cnf 中将服务器、客户端、连接的默认字符集和排序规则统一设置为 utf8mb4 和 utf8mb4_0900_ai_ci (MySQL 8.0+) 或 utf8mb4_unicode_ci (MySQL 5.7)，从源头避免混乱。

   ini

   ⧉⛶

    1[mysqld]
    2character-set-server=utf8mb4
    3collation-server=utf8mb4_0900_ai_ci
    4
    5[client]
    6default-character-set=utf8mb4

2. 索引长度限制：utf8mb4 字符集下，一个字符最多占用 4 字节。InnoDB 的索引前缀长度限制（旧版 767 字节，新版开启 innodb_large_prefix 后为 3072 字节）需要特别注意。

   • 例如，在旧版 InnoDB 上，一个 VARCHAR(255) 的列，如果完全由 4 字节字符组成，其最大长度为 255 * 4 = 1020 字节，超过了 767 字节的限制，无法直接创建完整索引。
   • 解决方案：
     • 升级 MySQL 并开启 innodb_large_prefix。
     • 使用前缀索引：CREATE INDEX idx_name ON t1(name(191)); (191 * 4 ≈ 764)。
     • 对长文本字段，考虑使用 HASH 索引或全文索引。

3. 存量数据迁移：从 utf8 迁移到 utf8mb4 是一个高危操作，需要谨慎。

   • 命令: ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   • 风险: 该操作会锁表，对于大表可能导致长时间的服务中断。
   • 生产方案: 使用 pt-online-schema-change 或 gh-ost 等在线 DDL 工具进行平滑迁移。

4. 查看执行计划：当遇到因排序规则不匹配导致的性能问题时（例如无法使用索引），通过 EXPLAIN 查看执行计划。ref 列可能会显示 func，表示进行了隐式类型或字符集转换，导致索引失效。

常见追问与延伸题目

1. 追问：utf8mb4_general_ci 和 utf8mb4_unicode_ci（或 _0900_ai_ci）到底选哪个？性能差异真的那么大吗？

   • 回答要点 1 (准确性)：unicode_ci 和 0900_ai_ci 基于官方 Unicode 排序算法，准确性更高。例如，general_ci 无法正确处理德语 ß 和 ss 的等价关系，而 unicode_ci 可以。0900_ai_ci 则更进一步，支持不区分重音，对国际化应用更友好。
   • 回答要点 2 (性能)：在现代服务器上，这点性能差异几乎可以忽略不计。MySQL 8.0 中，utf8mb4_0900_ai_ci 经过深度优化，性能甚至反超 general_ci。因此，为了数据正确性和未来的可扩展性，应该始终选择更准确的 unicode_ci 或 0900_ai_ci。牺牲正确性换取微不足道的性能提升是得不偿失的。

2. 追问：如果我有一个 VARCHAR(255) CHARACTER SET utf8mb4 的列，在上面建索引会发生什么？

   • 回答要点 1 (索引长度计算)：你需要向面试官确认 MySQL 版本和 innodb_large_prefix 配置。一个 utf8mb4 字符最多占用 4 字节，所以索引的最大潜在长度是 255 * 4 = 1020 字节。
   • 回答要点 2 (场景分析)：
     • MySQL 5.7+ 且 innodb_large_prefix=ON (默认)：行格式为 DYNAMIC 或 COMPRESSED 时，索引键前缀最大长度为 3072 字节。1020 字节小于 3072，所以可以成功创建完整索引。
     • MySQL 5.6 或 innodb_large_prefix=OFF：最大长度为 767 字节。1020 字节大于 767，直接创建索引会报错 ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes。此时必须使用前缀索引，如 INDEX(col(191))。
   • 回答要点 3 (性能影响)：即使可以创建完整索引，过长的索引键会降低 B+ 树效率，因为每个页能存放的索引记录更少，可能导致树的高度增加和更多的磁盘 I/O。

3. 追问：如果换成 PostgreSQL，字符集和排序规则的处理有什么不同？

   • 回答要点 1 (字符集)：PostgreSQL 的 UTF8 编码就是标准的全功能 UTF-8，等同于 MySQL 的 utf8mb4。它从一开始就正确实现了 UTF-8，没有 utf8mb3 这样的历史遗留问题。
   • 回答要点 2 (排序规则)：PostgreSQL 的 Collation 管理更为灵活和独立。它由操作系统（通常是 glibc）提供支持。你可以在数据库、表、列级别指定，甚至可以在查询中动态指定。
     sql

     ⧉⛶

      1-- 在查询中临时指定排序规则
      2SELECT * FROM my_table ORDER BY my_col COLLATE "fr_FR";

   • 回答要点 3 (总结)：PostgreSQL 在设计上更符合 SQL 标准，将字符集和排序规则解耦得更彻底。MySQL 则将两者紧密绑定，且有 utf8mb3 这样的历史包袱，但在 MySQL 8.0 之后，其默认配置已经向最佳实践靠拢。