1. 字符集与校对规则的真实分工一个管“存”一个管“比”先抛个很多人在面试或排查乱码时都会卡住的经典问题为什么我给表指定了utf8mb4也存进去了一些中文可一查出来全是“???”或者搜索a和A的结果总是对不上这时候分不清字符集character set和校对规则collate谁是凶手就很难下手。1.1 字符集决定“字怎么写进硬盘”校对规则决定“字怎么比较”字符集做的就是一件事把人类能看懂的字比如“中”“A”也包括 emoji映射成计算机能存能读的字节序列。比如utf8mb4下“中”这个字会被编码成E4 B8 AD三个字节而latin1下可能查无此字。简单说字符集是编码表是翻译器。校对规则则是针对某一种字符集制定的“比较顺序”。同一个“中”用utf8mb4_general_ci和utf8mb4_bin去比不仅结果可能不同排序输出的位置也可能不同。collate 管的是谁大谁小、谁等于谁、索引该怎么排。一个关键认知是MySQL 里设置一种字符集时通常同时指定配套的 collate如果只指定字符集而不写 collateMySQL 会默默填上默认校对规则。比如CREATE TABLE demo ( name VARCHAR(32) CHARACTER SET utf8mb4 ); -- 等价于 CREATE TABLE demo ( name VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci );在 MySQL 5.7 里utf8mb4默认是utf8mb4_general_ciMySQL 8.0 里则变成了utf8mb4_0900_ai_ci。这个默认值差异稍后说迁移时会继续踩到。1.2 为什么每个字符集都有一堆配套的 collate拿最常见的一组对比看utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin和 8.0 新增的utf8mb4_0900_ai_ci。后缀的拆解方式是_ci表示 case insensitive大小写不敏感_bin表示二进制直接比字节_ai表示重音不敏感accent insensitive_as标记重音敏感accent sensitive。为什么要整这么多套本质上是不同年代、不同精度诉求下的产物_general_ci历史遗留排序精度低但执行快适合英文和简单中文场景。_unicode_ci按 Unicode 标准算法做更精确的排序能正确处理大多数语言的“字母等价”规则比如知道é和e是近似同一级别的字符。_0900_ai_ci是 MySQL 8.0 参照 Unicode 9.0 之后的排序规则做的比旧的_unicode_ci更贴合新标准而且性能也优化过。画个最简单的对照表方便收藏collate 后缀大小写敏感重音敏感排序准确度备注_bin敏感敏感按字节适合唯一性校验、区分关键场景_general_ci不敏感不敏感粗糙旧默认值性能尚可_unicode_ci不敏感不敏感更准确5.7 时推荐_0900_ai_ci不敏感不敏感最准确MySQL 8 默认性能优于 unicode_ci另外utf8_bin这种写法也常看到它本质是utf8mb3的二进制比较模式凡是涉及 emoji 的场景都别用它。1.3 字符集决定“字怎么写进硬盘”校对规则决定“字怎么比较”展开这一层是为了堵住另一个老问题为什么我从老库导出的 SQL 带了一堆CHARSETutf8明明我建库用的utf8mb4在 MySQL 里如果看不到 MySQL 版本上下文utf8是个历史包袱——它其实是utf8mb3只支持最多三字节的字符。中文全部在 3 字节范围内所以很多人用utf8存中文一直没事可一旦遇到 emoji、部分冷门汉字或特殊符号4 字节就会直接报错甚至写库失败。因此 5.7 后的实践共识是一切新建项目直接用utf8mb4别再用utf8。你只是在迁就老数据而不是在做一个合理决策。至于latin1和gbk在跨语言系统中基本可以告别了。2. 乱码往往不在字段上而在连接上character_set_client 的连环跳我见过太多人发现乱码后的第一反应是ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4。改完一看还是乱码。这不是表结构没改对而是连接层面的字符集设置根本没跟上。2.1 一条数据从客户端到硬盘要经历三次“翻译”MySQL 内部处理 SQL 和数据时并不假定客户端送来的字节就是数据库表的字符集。它有一套链路核心参数是这几个SHOW VARIABLES LIKE character_set%;你会看到类似输出| character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_database | utf8mb4 |角色大概是character_set_client客户端发送过来的字符按什么编码解读。character_set_connectionSQL 语句在服务器内部剥离语法后字符串比对按什么编码走。character_set_results结果集返回给客户端时用什么编码。character_set_server新建库不指定字符集时的默认值。character_set_database当前库的默认值。如果client是latin1而表是utf8mb4你传进去的“中文”链接串在到达表以前就被错误解读哪怕表结构再对存进去的也是错字节。2.2 SET NAMES 的本质一口气改三个变量多数人在 MySQL 命令行里看到乱码第一反应是执行一句SET NAMES utf8mb4;这条命令等价于同时设置SET character_set_client utf8mb4; SET character_set_connection utf8mb4; SET character_set_results utf8mb4;这三项一旦对齐从客户端到服务器存储基本不会出现“传输出错”。注意SET NAMES不修改数据库、表字段的任何字符集它只影响当前会话的连接行为。所以用命令行玩没问题但用 Java/PHP 连数据库时这条命令未必能被你的连接池自动带上。2.3 驱动层不设置连接编码等于临时工干活Java 侧典型的连接串参数jdbc:mysql://127.0.0.1:3306/demo?useUnicodetruecharacterEncodingutf8老实说characterEncodingutf8在 JDBC 驱动里通常会被映射到utf8mb4但旧版驱动和某些中间件会踩坑。最稳的做法是在连接建立后顺手执行一次SET NAMES utf8mb4或者使用连接初始化 SQLspring: datasource: hikari: connection-init-sql: SET NAMES utf8mb4PHP 侧旧式写法是mysql_query(SET NAMES utf8mb4)或者 PDO DSN 中设置charsetutf8mb4。不管用什么语言只要连接层没对齐乱码大概率无法从表结构层面根治。一个典型自查思路如果你的实验结果总是“同一个 SQL命令行正常程序里乱码”先别怀疑数据库表第一步查程序连接串和数据库驱动版本。3. 五级设置与优先级改完库表还是乱码问题出在“默认”两个字上MySQL 字符集设置不是只有表和库两层它实际上覆盖了server、database、table、column、connection五个作用域。很多人只改了自己记得的那层其他层偷偷用旧默认值结果就是看起来全改了实际只有部分生效。3.1 各层默认值如何继承先看两条命令SHOW CREATE DATABASE demo; SHOW CREATE TABLE demo_table\GSHOW CREATE DATABASE会显示库的默认字符集和校验规则没特殊指定通常继承自serverSHOW CREATE TABLE则显示表本身以及每个字段的 charset 选项。如果建表时字段没单独写字符集字段会继承表的默认值表没写就继承库的默认值库没写就继承服务器全局配置。一句话总结这五个作用域不是平行并列而是按“server → database → table → column → connection”逐层向下覆盖但 connection 是独立的会话状态不参与继承链条。实际排查时光看information_schema就够了SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema demo;可以看到每张表实际生效的 collate这比看一堆建表语句要快得多。3.2 “只改默认值”与“真正改写存量数据”不是一回事这是最容易被误解的操作差异。假设原来库是utf8mb3里面已有大量中文数据你执行ALTER DATABASE demo CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ALTER TABLE demo_table DEFAULT CHARACTER SET utf8mb4;注意这里只改了“默认值”未来新建表或新插入不带 charset 字段时会用utf8mb4但 demo_table 里已有的字段定义以及已存的数据完全没有被动过。真正要把存量字段的编码和数据字节都转过来应该用ALTER TABLE demo_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;这条会重写表里的每一行数据把原本按utf8mb3或latin1解读的字节转换成utf8mb4的字节。代价是加锁、占用大量 IO、复制延迟生产环境务必放到低峰期或使用在线变更工具。还有一条容易踩的路ALTER TABLE demo_table MODIFY name VARCHAR(64) CHARACTER SET utf8mb4;MODIFY只改字段定义并不负责“修改已有行里那些字节”。如果你只是把列类型从VARCHAR(32)改成VARCHAR(64)顺便带上字符集那存量数据的字节其实没转码可能出现“原来正常改完变乱码”的诡异现象。3.3 一个完整迁移顺序的参考如果真要从老编码迁到utf8mb4我习惯按这个顺序走先把连接层和数据库全局默认改掉SET NAMES utf8mb4; SET GLOBAL character_set_server utf8mb4; SET GLOBAL character_set_client utf8mb4;把库默认改掉只影响未来新建表。对每张表执行ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;检查字段新定义确认是否所有varchar/text都跟随库表新字符集。把连接串参数、JDBC/PDO 驱动版本同步检查一遍。这样可以避免“库是新的表是旧的连接是乱的”三头不一致。4. 为什么老项目宁可拆库也要上 utf8mb4emoji 与四字节字符的真实教训很多人觉得utf8mb4只是“为了存 emoji”这是低估了它。实际上utf8mb4对应的编码规则允许最多 4 字节表示一个字符不仅能存 emoji 和一些特殊符号也解决了部分生僻字、异体字在utf8下存不进去的问题。4.1 不是所有“中文字符”都只有 3 字节现代中文常用字在 UTF-8 中一般是 3 字节但某些 CJK 扩展区字符、部分古字、少量竖排和标点组合就可能需要 4 字节。如果你经历过用户上传昵称带一个生僻字导致写库直接报错大概率就是utf8mb3在作怪。经常看到的报错长这样Incorrect string value: \xF0\x9F\x98\x8A... for column nickname这说明客户端已经用 utf8mb4 编码发送了 4 字节字符目标列却是 utf8mb3没法容纳。字面意思就是你给了一个口袋人家塞不进四叠漫画。4.2 迁移时最容易忽略的复制延迟与锁表回到迁移本身。对大表执行CONVERT TO CHARACTER SETMySQL 需要扫描重写整表数据。在 5.7 的单主复制架构里这个操作会因为带来大量 binlog 而被复制到从库逐条执行导致从库长时间落后。生产环境做过一次 300GB 表的字符集迁移我的经验是要么先用在线变更工具分批处理要么干脆预留窗口、接受只读同时把从库切换优先级提到前面。一个更聪明的做法是先在测试库脚本化跑一遍统计每张表行数和转换耗时挑出 top 10 大表单独处理。4.3 迁移后的隐性检查项迁移完不是看一眼中文显示正常就算完还有几个很多人不查的点information_schema.columns里的character_set_name是否全部变成utf8mb4。存储过程、函数的character_set是否还是老值。表的collation是否和业务期望一致比如需要区分大小写就不能用_ci。旧备份文件里如果带SET NAMES utf8恢复到新库后连接层和实际表字符集不一致会再次出现乱码。建议恢复后跑一轮SELECT character_set_database, collation_database; SHOW TABLE STATUS LIKE 你的大表;确认库层和表层两种字符集、两种 collate 都符合预期。5. collate 选错会踩的坑排序、索引失效以及“大小写不敏感”的另一面很多时候我们只关注字符集是否统一却忽略了 collate 直接影响搜索、排序和唯一约束。等出现问题再回头调成本比一开始选错字符集还高。5.1_ci带来的“同键不同值”隐患utf8mb4_general_ci这种大小写不敏感规则下MySQL、mysql、MYSQL被认为是同一个字。如果你建了唯一索引在用户名字段上就会出现“我想注册 Mysql系统提示名称已存在哪怕我没注册过”的尴尬。这是特性不是 bug。但问题在于一旦你确定业务需要区分大小写比如密保标识、激活码、某些编号应该从一开始就把字段定义为nickname VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_binutf8mb4_bin比较的是字节本身a和A就是两个不同值完全不掺合“语言习惯”。代价是你在查询时如果想做大小写不敏感匹配需要额外加LOWER()或在条件里动态指定 collateWHERE nickname admin COLLATE utf8mb4_general_ci5.2 排序结果可能不按“中文拼音”走很多业务希望按中文拼音排序比如城市列表。但 MySQL 的utf8mb4_general_ci和utf8mb4_unicode_ci对中文拼音的支持并不理想它们的排序是按 Unicode 码位或通用权重比不是按普通话拼音表。用这些 collate 直接ORDER BY name你大概率会遇到“北京”排在“上海”前后不稳定、甚至夹在英文中间的情况。要可靠的拼音排序通常需要额外存一列拼音首字母或者把中文转换成拼音列再排序。这是 collate 自带规则解决不了的问题别硬扛。5.3 索引利用率与隐式转换collate 还会影响 JOIN 和索引匹配。当两个表的字段字符集或 collate 不一致时MySQL 往往需要做隐式转换这时索引很可能失效。举例SELECT * FROM a JOIN b ON a.code b.code; -- a.code 是 utf8mb4_general_ci -- b.code 是 utf8mb4_bin执行计划里可能蹦出Using where; Using index之外的状态或者额外多一个Using temporary。这里的问题不是字符集能不能转而是比较规则不一致导致无法直接走索引匹配。一个来回折腾过多次的经验同一业务维度用户名、订单号、编号在所有表里的字符集和 collate 必须完全一致否则等着后续慢慢踩索引失效和 JOIN 慢的坑。5.4 别被utf8mb4_0900_ai_ci的“新”骗了MySQL 8.0 默认的utf8mb4_0900_ai_ci确实又快又准但如果你涉及老库同步、做过逻辑备份再恢复或者使用某些 5.7 架构下的中间件老客户端可能不认识这个 collate 名称从而报错“Unknown collation”。如果你看到这种报错要么把表 collate 降级到utf8mb4_general_ci要么统一升级基础设施后转换。另一点0900系列不是简单替代unicode_ci它基于 Unicode 9.0 的权重算法和旧_unicode_ci的实现机制不同。如果团队多个项目强依赖排序稳定迁移前最好先压测一轮业务查询结果确认整体排序变化可接受。6. 诊断工具三板斧不重启、不猜谜、三分钟定位字符集问题经验多了以后我排查字符集问题几乎不靠“到底哪种组合看起来正常”这种玄学。下面三个动作能覆盖九成场景。6.1 SHOW 语句 information_schema把会话变量列出来SHOW VARIABLES LIKE character_set%; SHOW VARIABLES LIKE collation%;查库、表SELECT DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAMEdemo; SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMAdemo;查具体列SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMAdemo AND TABLE_NAMEdemo_table;这套组合拳打完你基本能说出“哪个层是 utf8哪个层是 utf8mb4哪个层是 latin1”。剩下的就是对症下药。6.2 HEX() 直接看字节验证是不是“看起来乱、其实没乱”排查乱码时不要只凭肉眼判断。用HEX()看真实字节SELECT HEX(name), name FROM demo_table WHERE id1;如果结果显示E6B5A3E58D97“河南”二字就是 utf8mb4 编码。如果结果显示C4E3BAC3那就是 classic 的gbk/latin1字节混进去了。字节层面的判断比“看着像不是中文”要可靠得多。6.3 全局设置落盘my.cnf 的终极大招一些老项目使用的 MySQL 服务器全局默认还是latin1这种情况下每次新建库都要显式写 charset非常容易被遗漏。干脆在配置文件中钉死[mysqld] character-set-serverutf8mb4 collation-serverutf8mb4_general_ci注意collation-server不一定非得写如果不写MySQL 会根据character-set-server自动选择默认校对规则。但如果你的运维规范要求固定 collate最好显式写明白别让不同环境的默认值各自放飞。改完配置需要重启实例重启前记得SHOW VARIABLES LIKE character%;记录老值方便出问题回滚。生产环境改完重启属于高危操作务必有回滚方案再动。7. 最后再分享几个字符集协作的小技巧以上更多是“排障导向”的内容最后聊一点平时建表、维护的顺手经验。建新表时我几乎都会显式写出字符集和校验规则不依赖任何默认值CREATE TABLE new_table ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(64) NOT NULL, info TEXT ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;这样将来任何人看SHOW CREATE TABLE都清楚这张表是按什么规则建的不会默默继承某个环境变量。老库导出导入时建议别用mysqldump --default-character-setutf8这种死写法因为它在 5.7/8.0 下很容易把新库的 utf8mb4 导出成 utf8mb3 的 SET NAMES 语句。换成mysqldump --default-character-setutf8mb4 -u user -p demo demo.sql倒入新库之前检查 dump 文件的头几行head -20 demo.sql如果里面出现SET NAMES utf8而你的新库统一使用 utf8mb4建议把这一行改成SET NAMES utf8mb4再导入。从小到大都被“字符集问题”困扰过的开发最后都会形成一个共同习惯代码里不写死编码连接层统一 utf8mb4表字段显式确认 collate。这套组合看上去很简单但基本能消灭八成乱码。真正复杂的是存量系统改造那时千万别只改表不改连接也别只改库不改驱动每一步都要用SHOW和HEX()验证结果而不是凭肉眼判断“好像对了”。