字符集与排序规则

导学

你刚创建的数据库，插入中文后查询出来是乱码？ORDER BY 排序结果和预期不一致？WHERE 条件匹配时大小写敏感行为飘忽不定？这些问题的根源都在于字符集（Character Set）和排序规则（Collation）。理解 utf8mb4 与 utf8mb3 的区别、_bin 与 _ci 后缀的含义，是避免中文乱码和排序陷阱的必修课。

定义

字符集（Character Set）：定义了数据库可以存储哪些字符（如 ASCII、中文、emoji），以及每个字符对应的二进制编码。排序规则（Collation）：定义了字符之间如何比较和排序的规则，包括是否区分大小写、是否区分重音符号等。

核心概念

MySQL 5.7 中的字符集层级

utf8mb4 vs utf8（utf8mb3）

重要：MySQL 5.7 中 utf8 实际上是 utf8mb3 的别名，最多 3 字节，不支持 emoji 和某些生僻汉字。生产环境必须显式使用 utf8mb4。

排序规则后缀含义

SQL 示例

场景一：查看当前字符集设置

执行语句：

-- 查看服务器字符集
SHOW VARIABLES LIKE 'character_set%';

-- 查看当前连接的字符集
SHOW VARIABLES LIKE 'collation%';

操作后结果（MySQL 5.7 默认安装示例）：

结果解读：

• character_set_client/connection/results：客户端与服务器通信的三件套，通常应保持一致
• character_set_database/server：数据库/服务器默认字符集，MySQL 5.7 旧版本默认可能是 latin1，不支持中文
• 如果 character_set_database = latin1，创建数据库不指定字符集时，表默认用 latin1，插入中文会乱码

场景二：创建 utf8mb4 数据库和表

执行语句：

-- 创建数据库时显式指定字符集和排序规则
CREATE DATABASE IF NOT EXISTS company
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE company;

-- 创建表（继承数据库字符集）
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
) ENGINE=InnoDB;

-- 查看表的字符集
SHOW CREATE TABLE employees;

操作后结果（SHOW CREATE TABLE 节选）：

CREATE TABLE `employees` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dept` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

结果解读：

• 数据库指定 utf8mb4 后，表和列自动继承该字符集
• COLLATE utf8mb4_unicode_ci 表示使用 Unicode 排序规则，不区分大小写
• 生产环境创建数据库必须显式指定 utf8mb4，不能依赖默认值

场景三：不同排序规则的比较行为

演示数据准备：

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    dept VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('DaXiang', '技术部', 100),
('daXiang', '产品部', NULL);

当前 employees 表中的完整数据如下：

执行语句：

-- emp_name 使用 _general_ci（不区分大小写）
SELECT * FROM employees WHERE emp_name = 'daxiang';

-- dept 使用 _bin（区分大小写，按字节比较）
SELECT * FROM employees WHERE dept = '技术部';

操作后结果：

emp_name = 'daxiang'（不区分大小写）：

结果解读：

• utf8mb4_general_ci 不区分大小写，'daxiang' 匹配 'DaXiang' 和 'daXiang'
• utf8mb4_bin 按字节二进制比较，区分大小写，但中文字符在 bin 和 ci 下行为一致（因为 Unicode 中文字符没有大小写概念）
• 如果业务要求用户名大小写敏感（如 "Admin" ≠ "admin"），应使用 _bin 排序规则

场景四：连接字符集不一致导致乱码

执行语句：

-- 模拟客户端用 latin1 发送中文
SET NAMES latin1;

-- 插入中文（此时客户端按 latin1 编码发送）
INSERT INTO employees (emp_name, dept, score) VALUES ('白歌', '技术部', NULL);

-- 查询（连接字符集是 latin1，MySQL 会按 latin1 解码 utf8mb4 存储的数据）
SELECT emp_id, emp_name FROM employees WHERE emp_id = 3;

操作后结果（乱码示例）：

修复方法：

-- 设置正确的连接字符集
SET NAMES utf8mb4;

-- 重新查询
SELECT emp_id, emp_name FROM employees WHERE emp_id = 3;

结果解读：

• SET NAMES latin1 告诉 MySQL"客户端发送的是 latin1 编码"，但客户端实际发送的是 UTF-8 编码的中文
• MySQL 将 UTF-8 字节流当作 latin1 解析，导致存储时编码错乱，查询时显示为乱码
• 解决方案：客户端连接后始终执行 SET NAMES utf8mb4;，或在连接字符串中指定 charset=utf8mb4
• 如果数据已经乱码存储，修复需要知道原始编码转换路径，可能无法完全恢复

场景五：修改已有表的字符集

当前数据状态：假设某表误用 latin1 创建。

-- 模拟 latin1 表
CREATE TABLE employees_latin1 (
    id INT PRIMARY KEY,
    name VARCHAR(20)
) CHARACTER SET latin1;

-- 查看当前字符集
SHOW CREATE TABLE employees_latin1;

执行语句：

-- 修改表字符集（会转换已有数据）
ALTER TABLE employees_latin1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 验证
SHOW CREATE TABLE employees_latin1;

操作后结果（SHOW CREATE TABLE 节选）：

CREATE TABLE `employees_latin1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

结果解读：

• ALTER TABLE ... CONVERT TO CHARACTER SET 会转换已有数据的编码
• 如果原表已有中文数据且存储时编码正确（只是表标记为 latin1），转换后正常
• 如果原表数据已经乱码（如 UTF-8 字节被当作 latin1 存储），CONVERT TO 会按 latin1 解码再编码为 utf8mb4，可能更乱
• 大表执行 CONVERT TO 会重建整张表，需要 Online DDL 或维护窗口

常见误区

面试考点

Q：MySQL 中 utf8 和 utf8mb4 的区别？

utf8 是 utf8mb3 的别名，最多 3 字节/字符，不支持 emoji（4 字节）和部分生僻汉字。utf8mb4 最多 4 字节/字符，支持完整 Unicode（包括 emoji）。MySQL 5.7 生产环境必须显式使用 utf8mb4。

Q：排序规则 _ci、_cs、_bin 的区别？

_ci（Case Insensitive）不区分大小写，如 'A' = 'a'；_cs（Case Sensitive）区分大小写；_bin 按字节二进制比较，区分大小写且按字节码排序。_bin 性能通常最好，_ci 适合用户名搜索等不区分大小写的场景。

Q：中文乱码怎么排查？

1. 查存储字符集：SHOW CREATE TABLE 看表/列字符集是否为 utf8mb4；2. 查连接字符集：SHOW VARIABLES LIKE 'character_set%' 看 client/connection/results 是否一致且为 utf8mb4；3. 查客户端编码：确保客户端（程序、命令行）实际发送的是 UTF-8；4. 修复：统一为 utf8mb4，必要时 CONVERT TO CHARACTER SET 转换。

Q：utf8mb4_general_ci 和 utf8mb4_unicode_ci 的区别？

两者都不区分大小写。general_ci 是 MySQL 自己实现的简化排序规则，比较速度快但精度低（如某些德语字符排序不准确）；unicode_ci 基于 Unicode Collation Algorithm，排序更准确但稍慢。一般场景用 general_ci 足够，多语言精确排序用 unicode_ci。

Q：如何修改已有表的字符集？

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;。这会重建表并转换数据编码。大表应在低峰期执行，或使用 pt-online-schema-change 工具避免锁表。

小结

• 字符集决定存储什么字符，排序规则决定如何比较和排序
• MySQL 5.7 中 utf8 = utf8mb3（3 字节），**必须用 utf8mb4（4 字节）**才能支持 emoji 和完整 Unicode
• 排序规则 _ci 不区分大小写，_bin 按字节比较区分大小写
• 乱码三要素：存储字符集、连接字符集（SET NAMES）、客户端实际编码，三者必须一致且为 utf8mb4
• 创建数据库/表时显式指定 CHARACTER SET utf8mb4，不要依赖默认值

下一章引子：字符集解决了数据存储的编码问题，但当单表数据量达到千万级时，查询性能会急剧下降。分区表（Partitioning）通过将大表拆分为多个物理分区，让查询只扫描必要的数据片段。