"明明数据库里存进去是好好的中文,读出来却变成乱码""同样的用户名,为什么 WHERE name = '张三' 查不到,但把大小写换一下又能查到了""排序的时候,为什么 'a' 和 'A' 混在一起排,中文又排得很奇怪"——这些问题背后其实都指向同一对概念:字符集(character set) 和 排序规则(collation)。前者决定"用什么编码存字符",后者决定"字符之间怎么比较和排序"。两者经常被混为一谈,导致乱码排查时改错方向,或者建表时随手选了个默认值,埋下长期隐患。
这篇文章会把这两个概念掰开揉碎讲清楚:字符集解决"存得下、取得出"的问题,排序规则解决"比得了、排得对"的问题。然后给出中文乱码的完整排查链路(从连接层到存储层,一处不对就乱),再用表格对比常见字符集和排序规则,最后给出新老项目的统一方案,以及"已经乱了怎么修"的迁移步骤。涉及改字符集、ALTER TABLE 转换这类高危操作,都会配风险提示和回滚方式。
需要提醒的是:MySQL 8.0 的默认字符集已经从历史上的 latin1 变成了 utf8mb4,默认排序规则也变成了 utf8mb4_0900_ai_ci。这意味着老项目和 8.0 新项目的行为可能完全不同,排查时第一件事就是确认版本和实际生效值,而不是想当然。
一、字符集和排序规则,先分清楚
一句话概括:字符集管"存",排序规则管"比"。
字符集是一套"字符 → 数字编码"的映射表,比如 UTF-8 把"中"映射成一串字节。它决定了数据库能用哪些字符、每个字符占多少字节。常见的字符集有 latin1、utf8(其实是不完整的 UTF-8)、utf8mb4、gbk 等。
排序规则是建立在某个字符集之上的一套比较规则,它规定了同一个字符集内的字符如何排序、如何判断相等。比如同样是 utf8mb4 字符集,可以配 utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin 等多种排序规则。排序规则名字里通常有两个后缀:_ci 表示 case insensitive(大小写不敏感),_cs 表示 case sensitive(敏感),_bin 表示按二进制值直接比较。
所以两者是"一对多"的关系:一个字符集可以有多个排序规则,但一个排序规则只属于一个字符集。设定排序规则时,同时也就确定了字符集,反过来则不一定。
二、中文乱码的经典成因
先把最容易踩的坑说清楚:MySQL 里大家常说的 utf8,最多只存 3 个字节,并不能表示所有的 Unicode 字符。所有需要 4 个字节的字符——包括大部分 emoji、部分生僻汉字、一些少数民族文字——用 utf8 存会直接报错或变成 ?。utf8mb4 才是真正完整的 UTF-8,最多 4 字节。新项目一律用 utf8mb4,这是没有争议的结论。
中文乱码的成因通常不是单一环节,而是"客户端字符集、连接字符集、服务器字符集、库表字符集"这一整条链路上有一处不一致。常见的表现有:
- 存进去时正常,读出来是
????——多半是连接字符集不对,写入时字符被替换成了问号,属于不可逆丢失。 - 读出
ä½ å¥½这类"乱入西文"——典型的把 UTF-8 字节按 latin1 解释,通常是连接层与存储层字符集错配。 - 一部分中文正常、少数生僻字或 emoji 报
Incorrect string value——用的是utf8而非utf8mb4。 - 数据库里查
LENGTH()和CHAR_LENGTH()差异异常——这是字节数与字符数不同导致的正常现象,不一定是乱码。
排查乱码,第一步是把各层实际生效的字符集全查出来:
-- 查看与字符集相关的所有变量,重点看这几项
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
-- 关键六项的含义:
-- character_set_client : 客户端发来的语句用什么字符集
-- character_set_connection : 会话层用什么字符集
-- character_set_results : 返回给客户端的结果用什么字符集
-- character_set_server : 服务器默认字符集
-- character_set_database : 当前库的默认字符集
-- character_set_system : 系统元数据字符集(通常固定 utf8)
-- 查看具体库、表、列的字符集
SHOW CREATE DATABASE your_db;
SHOW CREATE TABLE your_db.t_user;
判断规则很简单:只要这几项不完全一致,就存在乱码风险。 现代 MySQL 客户端通常会自动协商,但如果客户端(尤其是老版本的 PHP、JDBC、命令行)没设对,就会出问题。
三、常见字符集与排序规则对比
下表列出常见字符集和排序规则的差异,帮助选型:
| 字符集 | 最大字节/字符 | 能否存 emoji | 说明 |
|---|---|---|---|
| latin1 | 1 | 否 | 西欧字符,存中文必乱 |
| gbk | 2 | 否 | 中文简体,历史系统常见 |
| utf8(别名 utf8mb3) | 3 | 否 | 不完整,仅 BMP 内字符 |
| utf8mb4 | 4 | 是 | 完整 UTF-8,新项目首选 |
| 排序规则 | 大小写 | 说明与适用场景 |
|---|---|---|
| utf8mb4_general_ci | 不敏感 | 旧版默认,比较快但不完全符合 Unicode 标准 |
| utf8mb4_unicode_ci | 不敏感 | 基于较早 Unicode 标准,准确度较高 |
| utf8mb4_0900_ai_ci | 不敏感 | 8.0 默认,基于 Unicode 9.0,支持口音不敏感 |
| utf8mb4_bin | 敏感 | 按字节比较,区分大小写,适合严格比较(如令牌、哈希) |
| utf8mb4_zh_0900_as_cs | 敏感 | 中文按拼音/笔画规则排序,适合对中文排序有要求的场景 |
这里要对"不敏感"多做一点解释:general_ci 和 unicode_ci 都会把 a 和 A、ä 和 a 等视为相等,这会直接影响 WHERE 查询和 UNIQUE 约束。举个例子,如果某列用 _ci 排序规则,那么插入 'ABC' 和 'abc' 会被认为重复,UNIQUE 索引会拒绝第二条。这在用户名字段上可能正是你想要的(防大小写绕过),但在验证码、token 这类字段上就是灾难,必须用 _bin 或 _as_cs。
四、从连接层到存储层的完整配置
治理乱码要"全链路统一",下面按 MySQL 8.0 的标准做法给出一套配置。
服务器层(配置文件):
# 配置文件位置:
# RHEL/CentOS/Rocky: /etc/my.cnf
# Debian/Ubuntu: /etc/mysql/mysql.conf.d/mysqld.cnf
vi /etc/my.cnf
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
# 8.0 下这两项基本已是默认值;5.7 需要显式设置
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
改完需要重启 mysqld 生效(风险提示:重启会中断连接,请在低峰操作;改前备份配置文件,记下旧值以便回滚):
# RHEL/CentOS/Rocky 系
sudo systemctl restart mysqld
# Debian/Ubuntu 系(服务名可能是 mysql 或 mysqld)
sudo systemctl restart mysql
连接层(会话级): 应用或命令行连接时,显式指定字符集。命令行:
mysql --default-character-set=utf8mb4 -u root -p your_db
在 SQL 中也可以临时设置:
-- 会话级设置,只影响当前连接
SET NAMES utf8mb4;
-- 等价于同时设置 character_set_client/connection/results
-- 等价展开
SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;
不同语言驱动的连接串写法(示例):
# JDBC(MySQL 8 Connector/J)
jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=utf8&connectionCollation=utf8mb4_0900_ai_ci
# PHP PDO
mysql:host=127.0.0.1;dbname=db;charset=utf8mb4
# Python PyMySQL
charset='utf8mb4'
存储层(库、表、列): 建库建表时显式声明,不依赖默认值。
CREATE DATABASE your_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;
CREATE TABLE t_user (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
token VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, -- 严格区分大小写
nickname VARCHAR(64) DEFAULT NULL,
created_at DATETIME NOT NULL,
UNIQUE KEY uk_username (username),
UNIQUE KEY uk_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
注意 token 列用 utf8mb4_bin,而 username 用不敏感的排序规则——同一张表不同列可以有不同排序规则,这是处理"用户名不区分大小写、令牌区分大小写"这类需求的常用手法。
五、排查乱码的标准流程
遇到乱码,按下面的顺序一步步缩小范围,不要一上来就 ALTER DATABASE:
第一步,确认乱码发生在哪一层。 用命令行直连数据库,SET NAMES utf8mb4 后插入一条中文再读出来:
SET NAMES utf8mb4;
CREATE TABLE t_charset_test (id INT PRIMARY KEY, c VARCHAR(50)) DEFAULT CHARSET=utf8mb4;
INSERT INTO t_charset_test VALUES (1, '中文测试😀');
SELECT id, c, HEX(c), CHAR_LENGTH(c), LENGTH(c) FROM t_charset_test;
如果命令行里读写正常,说明问题在应用连接层(连接串没设对)。如果命令行里也乱,问题在服务器或存储层。
第二步,看存储层。 检查库、表、列的字符集:
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db';
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 't_user';
第三步,验证字节序列。 用 HEX() 看实际存了什么字节。UTF-8 的中文字符,HEX 出来应该是三字节或四字节的序列;如果看到的是单字节高位值(如 E4 B8 AD 之外的 latin1 形态),就说明编码链路错配。
SELECT HEX(c), CHAR_LENGTH(c) AS chars, LENGTH(c) AS bytes FROM t_charset_test;
-- 正常 utf8mb4 下 '中文测试😀' 的 LENGTH 会比 CHAR_LENGTH 大,emoji 占 4 字节
六、把已有的乱码修回来
如果确认是历史数据用了错误的字符集,需要转换。转换有风险,务必先备份:
# 先做全库逻辑备份
mysqldump -u root -p --single-transaction --databases your_db > /backup/your_db_before_charset.sql
如果只是表/列的字符集不对(但内容本身是正常编码),用 ALTER TABLE 转换:
-- 风险操作:会重建表、可能长时间锁表,大表请低峰执行
-- 在 MySQL 8.0 中改变字符集属于 InnoDB 在线 DDL 支持的范畴,但大表仍可能耗时
ALTER TABLE your_db.t_user
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- 只改某列
ALTER TABLE your_db.t_user
MODIFY username VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
但要注意:CONVERT TO CHARACTER SET 假设当前存储的字节是正确的、只是声明错了。如果数据本身是被错误写入的(比如 UTF-8 内容被当 latin1 存了两遍),就需要更复杂的"先转字节再转字符集"两步走,甚至逐条修正。这种情况建议导出数据、用脚本按正确编码重处理后再导入,不要在生产库上反复 ALTER。
风险提示:大表执行
ALTER TABLE ... CONVERT可能长时间持锁、占用大量 IO,务必:
1. 先备份(逻辑或物理);
2. 在低峰窗口执行;
3. 预判磁盘空间(重建表需要额外空间);
4. 想好回滚——备份是唯一可靠的退路,改坏了就用备份恢复。
还有一个容易忽略的坑:排序规则改了,可能影响已有的唯一索引和查询结果。比如把 _ci 改成 _bin 后,原本被认为"重复"的两行可能不再重复,反之亦然。改之前要先跑一遍数据检查:
-- 改排序规则前,检查是否存在仅大小写不同的重复值
SELECT LOWER(username), COUNT(*)
FROM t_user GROUP BY LOWER(username) HAVING COUNT(*) > 1;
常见问题
Q1:utf8 和 utf8mb4 到底选哪个?
无脑选 utf8mb4。utf8(即 utf8mb3)只支持 3 字节,存不了 emoji 和部分生僻字。8.0 默认已是 utf8mb4,5.7 及更早要手动设。
Q2:为什么我的查询对中文大小写不敏感、对英文却敏感?
取决于列的排序规则。_ci 后缀对所有字符都大小写不敏感,_bin 严格按字节比较。如果表现混乱,检查是不是不同列用了不同排序规则,或连接层 collation_connection 与列不一致导致比较时发生了隐式转换。
Q3:改了 character_set_server 重启后,旧表会自动变吗?
不会。该参数只影响新建库表的默认值,已存在的库、表、列的字符集不会自动改变,需要显式 ALTER。这也是"配置改了但乱码依旧"的常见原因。
Q4:连接串里 characterEncoding 写成 utf8 会不会有问题?
JDBC 的 characterEncoding=utf8 在较新驱动里通常映射到 utf8mb4,但为稳妥起见建议显式确认,或直接使用 utf8mb4,并保证服务端支持。
Q5:怎么避免以后新建表又用错字符集?
在服务器配置文件设好 character-set-server=utf8mb4,建表语句一律显式写 DEFAULT CHARSET=utf8mb4,并把这条纳入建表规范/代码审查。不要裸依赖默认值。
总结
- 字符集管"存"(字符到字节的映射),排序规则管"比"(比较和排序),一个字符集可有多个排序规则,二者是多对一关系。
- 新项目统一用
utf8mb4,排序规则按语义选:普通字段用_ci,需严格区分大小写用_bin/_cs。 - 乱码是"客户端—连接—服务器—库表"全链路问题,先
SHOW VARIABLES LIKE 'character_set%'定位断点,再对症处理。 - 转换已有数据的字符集属于高危操作:先全量备份、低峰执行、预留空间、检查唯一索引冲突,改坏就靠备份回滚。
- 把 "utf8mb4 + 显式字符集/排序规则" 写进建表规范,是最省心的长期方案。
企业QQ咨询




