字符集与排序规则

MySQL 字符集与排序规则

字符集和 collation 是 MySQL 中最容易被忽视却影响最深远的配置。一个字符集不匹配的 JOIN，会让索引完全失效；一个错误的 collation，会让大小写区分行为让你摸不着头脑。本章彻底厘清这个知识点。

**生产事故警示：**新系统使用 utf8mb4_0900_ai_ci，老系统使用 utf8mb4_general_ci，JOIN 时触发隐式转换，索引失效，QPS 从 2 万跌到 200，引发服务雪崩。字符集问题务必在建表时统一，事后修改成本极高。

1. 字符集基础

1.1 字符集层次结构

MySQL 字符集可以在 4 个层级设置，优先级从低到高：

服务器级  → my.cnf: character_set_server = utf8mb4
数据库级  → CREATE DATABASE db DEFAULT CHARACTER SET utf8mb4
表级      → CREATE TABLE t (...) CHARACTER SET utf8mb4
列级      → name VARCHAR(100) CHARACTER SET utf8mb4

列级优先级最高，其次是表级，以此类推。没有显式指定时继承上一级。

1.2 连接字符集

-- 查看当前连接字符集
SHOW VARIABLES LIKE 'character_set%';
-- character_set_client:   客户端发来的 SQL 编码
-- character_set_connection: 连接层转换编码
-- character_set_results:   返回给客户端的结果编码

-- 建立连接后立即设置（应用侧必做）
SET NAMES utf8mb4;
-- 等价于：
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

连接字符集与表字符集不匹配会触发转换，不仅有性能损耗，还可能导致数据截断或乱码。JDBC 连接串加 ?characterEncoding=utf8mb4&useUnicode=true。

2. utf8 vs utf8mb4：一个历史遗留的陷阱

**MySQL 的 utf8 不是真正的 UTF-8！**MySQL 的 utf8 最多存 3 字节，只覆盖 BMP（基本多语言平面），无法存储 4 字节字符（Emoji、某些生僻汉字）。真正的 UTF-8 最多 4 字节，对应 MySQL 的 utf8mb4（mb4 = most bytes 4）。

结论：新建的所有库表列，统一使用 utf8mb4，不要用 utf8。

3. Collation（排序规则）详解

3.1 什么是 Collation

Collation 定义了字符串的比较和排序规则，决定：① 大小写是否敏感 ② 音调符号是否敏感 ③ 排序顺序

3.2 utf8mb4 常见 Collation 对比

-- ci vs cs vs bin 的实际差异
SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SELECT 'HELLO' = 'hello';  -- 返回 1（大小写不敏感）

SET NAMES utf8mb4 COLLATE utf8mb4_0900_as_cs;
SELECT 'HELLO' = 'hello';  -- 返回 0（大小写敏感）

SET NAMES utf8mb4 COLLATE utf8mb4_bin;
SELECT 'HELLO' = 'hello';  -- 返回 0（二进制比较）

-- 应用场景：
-- 用户名不区分大小写 → _ci
-- 密码/API Key/Token → _bin（严格区分）
-- 文件路径（大小写敏感系统）→ _bin 或 _cs

4. Collation 对索引和 JOIN 的影响

4.1 JOIN 时的隐式转换

-- 表 A：users.email 使用 utf8mb4_general_ci
-- 表 B：sessions.email 使用 utf8mb4_0900_ai_ci

-- 这个 JOIN 会导致索引失效！
SELECT u.*, s.*
FROM users u
JOIN sessions s ON u.email = s.email;  -- 字段 collation 不同，触发转换

-- EXPLAIN 会看到：
-- sessions 的 email 索引未被使用（或 Using filesort）

**解决：**在 JOIN 条件中显式转换，或（最好）统一两张表的字符集和 collation。

-- 临时解法：显式转换
SELECT u.*, s.*
FROM users u
JOIN sessions s ON u.email = s.email COLLATE utf8mb4_0900_ai_ci;

-- 根本解法：ALTER TABLE 统一 collation

4.2 Collation 影响索引长度

-- utf8mb4 字段的 EXPLAIN key_len 计算：
-- VARCHAR(50) utf8mb4 = 50 * 4 + 2 (长度前缀) = 202 字节
-- 如果 NULLABLE，再 +1 = 203 字节

-- 这影响复合索引能包含多少字段（行格式有 65535 字节行限制）
-- InnoDB 索引前缀最长 767 字节（ROW_FORMAT=COMPACT）
-- 或 3072 字节（innodb_large_prefix + ROW_FORMAT=DYNAMIC，8.0 默认）

5. Emoji 和四字节字符存储

-- 用 utf8（非 mb4）存 Emoji 会发生什么
CREATE TABLE t (content TEXT CHARACTER SET utf8);
INSERT INTO t VALUES ('Hello 😀');
-- 错误：ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column

-- 正确：使用 utf8mb4
CREATE TABLE t (content TEXT CHARACTER SET utf8mb4
                COLLATE utf8mb4_0900_ai_ci);
INSERT INTO t VALUES ('Hello 😀');  -- 正常存储

-- 验证
SELECT content, LENGTH(content), CHAR_LENGTH(content)
FROM t;
-- LENGTH = 字节数（😀 占 4 字节）
-- CHAR_LENGTH = 字符数（😀 算 1 个字符）

**注意：**Emoji 序列（如 👨‍👩‍👧‍👦 家庭表情符号）可以由多个 Unicode code point 组成（用零宽度连接符 ZWJ 拼接），在 MySQL 中占多个字符。CHAR_LENGTH('👨‍👩‍👧‍👦') 可能返回 7，而不是 1。这在限制昵称长度时需要注意。

6. 字符集迁移（utf8 → utf8mb4）

-- 步骤 1：修改 my.cnf（新连接生效）
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci

-- 步骤 2：在线修改数据库
ALTER DATABASE mydb DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- 步骤 3：修改表（⚠️ 大表注意用 pt-online-schema-change 或 gh-ost）
ALTER TABLE users
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
-- CONVERT TO 会修改表内所有字符类型列

-- 步骤 4：验证
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users';

**迁移注意：**① utf8 转 utf8mb4 通常是安全的，但 VARCHAR 字段的字节占用从 3× 变为 4×，可能导致行长度超过页限制。② 如果有前缀索引（INDEX (name(191))），需要重新检查前缀长度。③ 大表必须用 Online DDL 工具，不要直接 ALTER。

7. 最佳实践总结

-- 检查系统中是否有 utf8（非 mb4）表
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
  AND TABLE_COLLATION NOT LIKE 'utf8mb4%';