灯下哥谭
灯下哥谭
2022-03-12
目录

MySQL不同字符集之间的区别和选择原创

# MySQL不同字符集之间的区别和选择

# 0. 先说最坑的一条：MySQL 的 utf8 不是 UTF-8

这是这个话题里唯一一条必须先知道的事实，也是中文库里最经典的一次事故来源。

版本说明

本文写于 2022-03，基于 MySQL 8.0。
8.0 起默认字符集已由 latin1 改为 utf8mb4（utf8mb4_0900_ai_ci 排序规则），本文若涉及手动指定字符集的建议在新装 8.0 实例上大多已是默认值，无需额外配置；升级/迁移场景仍需按本文思路手动核对。

MySQL 里的字符集名 utf8 是 utf8mb3 的别名——每个字符最多 3 字节，而标准 UTF-8 最多 4 字节。差的那一档正好是 Unicode 基本多文种平面之外的字符：emoji、部分生僻汉字（𠮷、𡈽 这类）、部分少数民族文字。

后果是往 utf8 列里存 emoji 会直接报错：

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'nickname'
1

或者在宽松 SQL 模式下被截断——报不了错，数据静默丢一半，这个更麻烦。

结论：任何时候都用 utf8mb4，永远不要用 utf8/utf8mb3。 MySQL 8.0.28 起已经把 utf8mb3 标记为弃用，8.0 新建实例的默认值也已经是 utf8mb4；本文后面提到的所有 utf8 前缀的旧排序规则（utf8_general_ci 等），看到就应该当成待整改项。

# 常见字符集

字符集 每字符字节数 说明
utf8mb4 1~4 唯一推荐。完整 UTF-8，覆盖 emoji 与全部 Unicode
utf8 / utf8mb3 1~3 不要用。存不下 emoji 与生僻字，8.0.28 起弃用
gbk 1~2 简体中文，中文比 utf8mb4 省 1 字节；只适合对接遗留系统，不支持多语言
latin1 1 西欧语言。MySQL 5.7 及更早的默认值，是绝大多数「历史库乱码」的源头
utf16 / utf32 2~4 / 4 MySQL 内部很少用于存储列。常被说成「存亚洲文字比 utf8mb4 省」，中文场景确实是 2 字节 vs 3 字节，但它不兼容 ASCII、索引和网络传输开销都更大，实践中不选它

关于存储空间的一个常见误解：VARCHAR(N) 的 N 是字符数不是字节数，改字符集不会改变能存多少个字符。真正受字节数影响的是索引长度上限——InnoDB 单列索引键最长 3072 字节（DYNAMIC 行格式），utf8mb4 下 VARCHAR(768) 就顶到上限了，这是从 utf8 转 utf8mb4 时最常见的翻车点，见文末「坑与边界」。

# 选择字符集的考虑因素

在选择字符集时，应考虑以下因素：

  • 语言支持： 选择支持你应用中所用语言的字符集。
  • 存储空间： 不同字符集的存储空间占用不同，UTF-8相对较节省空间。
  • 性能： 一些字符集在特定应用场景下可能更高效。
  • 排序和比较： 不同字符集可能导致不同的排序和比较结果。

# 排序规则（Collation）

# 什么是排序规则

排序规则定义了字符集中的字符如何进行比较和排序。不同的排序规则会影响数据的排序和查询结果。选择合适的排序规则非常重要，因为它直接影响数据库的性能和用户体验。

# 常见排序规则

排序规则 用在哪 说明
utf8mb4_0900_ai_ci MySQL 8.0 新建库的默认值，新项目就用它 基于 Unicode 9.0，大小写与重音都不敏感（ai = accent insensitive，ci = case insensitive）。比老的 _unicode_ci 更准也更快
utf8mb4_unicode_ci 需要与 5.7 及更早的库保持一致排序时 基于 Unicode 4.0。不是"更好"，只是更老——很多文章把它当推荐值，那是 5.7 时代的结论
utf8mb4_general_ci 遗留库 5.7 时代的默认值，排序规则是简化实现，某些语言下结果不符合预期。新库不要用
utf8mb4_bin 需要严格区分大小写/重音时 按码点二进制比较，'A' != 'a'。校验码、token、区分大小写的用户名适合它
utf8mb4_0900_as_cs 同上，但要 Unicode 语义 重音敏感 + 大小写敏感，比 _bin 更符合语言直觉

⚠️ 两条不同的排序规则不能直接比较。JOIN 两张排序规则不一致的表时会报：

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

而且即便侥幸没报错，关联列排序规则不一致会让索引失效（需要隐式转换）——这是「明明有索引却全表扫描」的一个隐蔽成因。所以库内统一比选哪一个更重要。

# 示例

在创建数据库或表时，你可以指定字符集和排序规则。例如：

查看SQL示例 
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
1

这会创建一个使用UTF-8字符集和utf8mb4_unicode_ci排序规则的数据库。确保选择适合你应用需求的字符集和排序规则。

# 检查和修改字符集与排序规则

下面这组巡检 SQL 的基准值要按你自己的库定。 原文以 utf8mb4_unicode_ci 为准，但 MySQL 8.0 新建库的默认值是 utf8mb4_0900_ai_ci——直接照抄会把所有 8.0 默认建的库判成「不合规」，然后生成一堆没必要的 ALTER。

先确认你这个实例的既有基准是什么：

SELECT DEFAULT_COLLATION_NAME, COUNT(*)
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('sys','mysql','performance_schema','information_schema')
GROUP BY DEFAULT_COLLATION_NAME;
1
2
3
4

哪个占多数就以哪个为基准，把下面 SQL 里的 utf8mb4_unicode_ci 换成它。目标是「库内统一」，不是「换成某个特定值」——为了统一去动一个正在跑的大库，收益远小于风险。

# 查看当前MySQL实例中不符合字符集和排序规则规范的库名

查看SQL示例 
SELECT
SCHEMA_NAME '数据库',
DEFAULT_CHARACTER_SET_NAME '库字符集',
DEFAULT_COLLATION_NAME '库排序规则'
FROM
information_schema.SCHEMATA
WHERE
(DEFAULT_CHARACTER_SET_NAME != 'utf8mb4' OR DEFAULT_COLLATION_NAME != 'utf8mb4_unicode_ci')
AND SCHEMA_NAME NOT IN ( 'sys', 'mysql', 'performance_schema', 'information_schema' );
1
2
3
4
5
6
7
8
9

# 查看当前MySQL实例中不符合排序规范的表

查看SQL示例 
SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '表排序规则',TABLE_ROWS '表行数' 
FROM information_schema.TABLES 
WHERE TABLE_COLLATION != 'utf8mb4_unicode_ci' 
AND TABLE_SCHEMA NOT IN ('sys','mysql','performance_schema','information_schema');
1
2
3
4

# 修改数据库的字符集和排序规则

在修改表及表字段的字符集和排序规则之前，需要先修改库的字符集和排序规则：

查看SQL示例 
SELECT
SCHEMA_NAME '数据库',
DEFAULT_CHARACTER_SET_NAME '库字符集',
DEFAULT_COLLATION_NAME '库排序规则',
CONCAT( 'ALTER DATABASE ', SCHEMA_NAME, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) '修正库规则SQL'
FROM
information_schema.SCHEMATA
WHERE
(DEFAULT_CHARACTER_SET_NAME != 'utf8mb4' OR DEFAULT_COLLATION_NAME != 'utf8mb4_unicode_ci')
AND SCHEMA_NAME NOT IN ( 'sys', 'mysql', 'performance_schema', 'information_schema' );
1
2
3
4
5
6
7
8
9
10

# 修改表及表字段的字符集和排序规则

查看SQL示例 
SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '排序规则',
CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') '修正表规则SQL' 
FROM information_schema.TABLES 
WHERE TABLE_COLLATION != 'utf8mb4_unicode_ci' 
AND TABLE_SCHEMA NOT IN ('sys','mysql','performance_schema','information_schema');
1
2
3
4
5

上面几条 SQL 只生成 ALTER 语句，不执行。这是刻意的——生成出来先看一遍、评估代价，再决定跑哪些。原因见下一节。

# 坑与边界

  • ALTER TABLE ... CONVERT TO CHARACTER SET 是整表重建，不是改元数据。 它会重写全部数据和索引，大表上耗时以小时计、占用双倍磁盘空间，且是 ALGORITHM=COPY（不支持 Online DDL）。生产环境必须用 gh-ost 之类工具做（见 Gh-ost重建表，清除表碎片率），不要直接执行。

  • 从 utf8 转 utf8mb4 可能因索引超长直接失败：

    ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
    1

    单列索引键上限 3072 字节，utf8 下 VARCHAR(1024) 是 3072 字节刚好合法，转成 utf8mb4 变成 4096 字节就超了。转换前先扫一遍有没有这种列：

    SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME, s.COLUMN_NAME, c.CHARACTER_MAXIMUM_LENGTH
FROM information_schema.STATISTICS s
JOIN information_schema.COLUMNS c
  ON c.TABLE_SCHEMA = s.TABLE_SCHEMA AND c.TABLE_NAME = s.TABLE_NAME AND c.COLUMN_NAME = s.COLUMN_NAME
WHERE c.CHARACTER_MAXIMUM_LENGTH * 4 > 3072
  AND s.TABLE_SCHEMA NOT IN ('sys','mysql','performance_schema','information_schema');
    1
    2
    3
    4
    5
    6

    处置办法是给索引加前缀长度（KEY idx (col(191))）或缩短列定义，不要指望改行格式绕过去。

  • CONVERT TO 对 BLOB/BINARY 列不生效，对 VARBINARY 也不生效。如果历史上有人把中文塞进了 VARBINARY 列（迁移脚本里很常见），转换会静默跳过它们，转完仍然乱码。

  • 改了库/表的默认字符集，已有的列不会变。ALTER DATABASE ... DEFAULT CHARACTER SET 只影响之后新建的表，ALTER TABLE ... DEFAULT CHARACTER SET 只影响之后新增的列。要动存量列必须用 CONVERT TO——这是「我明明改了怎么还是乱码」的头号原因。

  • 服务端字符集对了，链路上任何一环错了照样乱码。完整链路是「表定义 → character_set_client / _connection / _results → 应用驱动的编码设置 → 展示端」，客户端连接字符集要显式指定（JDBC 的 characterEncoding、mysql --default-character-set=utf8mb4）。排查方法见 MySQL 导出 CSV 中文乱码：字符集链路从头讲一遍。

  • gbk 库转 utf8mb4 不能只改字符集声明。如果历史上是「latin1 的壳里装 gbk 的字节」（早年最常见的错误存法），直接 CONVERT TO 会把已经错乱的字节再错一次，必须走 CONVERT(BINARY(col) USING utf8mb4) 的两步法，且转换前必须有可回滚的备份。

# 验证

改完之后逐层确认，不要只看库的默认值：

-- 1. 列级（真正决定数据怎么存的一层）
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND CHARACTER_SET_NAME IS NOT NULL
  AND CHARACTER_SET_NAME <> 'utf8mb4';
1
2
3
4
5

预期输出：空集（没有任何列还留在非 utf8mb4 上）。

-- 2. 连接层
SHOW VARIABLES LIKE 'character_set%';
1
2

预期 character_set_client / character_set_connection / character_set_results / character_set_server 全是 utf8mb4（character_set_filesystem 是 binary，正常）。

-- 3. 端到端：存一个 4 字节字符再读回来
INSERT INTO your_table(nickname) VALUES ('测试😀');
SELECT nickname, LENGTH(nickname), CHAR_LENGTH(nickname) FROM your_table WHERE nickname LIKE '测试%';
1
2
3

预期输出（LENGTH 是字节数、CHAR_LENGTH 是字符数，emoji 完整存下来才算通过）：

+-----------+--------+-------------+
| nickname  | LENGTH | CHAR_LENGTH |
+-----------+--------+-------------+
| 测试😀     |     10 |           3 |
+-----------+--------+-------------+
1
2
3
4
5

如果 emoji 变成 ? 或报 Incorrect string value，说明链路上还有一环是 utf8mb3——按上面第 1、2 步逐层找。

#架构设计#性能优化
上次更新: 9/11/2026

← MySQL为什么有时候会选错索引→

最近更新
01
DeepSeek Harness 实战 06｜学习笔记：插件、工具、技能不在同一个维度上 原创
09-11
02
DeepSeek Harness 实战 05｜让两个编码 Agent 共用一份长期记忆 原创
09-09
03
DeepSeek Harness 实战 04｜学习笔记：从「已知限制」里读出三处设计张力 原创
09-08
更多文章>
Theme by Vdoing