1. 升级8.0时字符集默认值的改变为什么总被忽视前阵子把一套跑了快五年的销售订单库从 MySQL 5.7 升到 8.0。升级前 binlog、密码插件、SQL 兼容性都检查过一遍应用一接进来还是出了事老数据查出来一切正常新写入的中文备注全变成问号另外有个按客户名称排序的列表返回顺序和老库明显不一样。排查了大半天最后发现根子全在字符集。MySQL 8.0 升级里很多人第一反应是看语法兼容、看账号权限字符集往往被排到最后一环甚至被跳过可它恰恰最容易在升级后变成线上故障点。这篇文章把我这次走过的弯路、定位思路和最终修复方案完整写出来给准备升 8.0 的团队做个参照。1.1 一个真实场景老库正常、新写入乱码先说现象。升级完成后查询历史订单中文地址、备注都正常但业务侧新插入一条订单数据库里存进去的中文就变成???部分 emoji 直接丢字节。一开始怀疑是代码里编码被改坏可前端传参、接口日志里显示原始中文没问题问题几乎可以确定出在数据库链路上。然后我们做了几个快速验证在命令行用mysql --default-character-setutf8mb4连接手工插入中文结果是正常的用原 JDBC 连接池插入乱码复现查看当前连接变量发现character_set_client、character_set_connection都是latin1而表字符集是utf8mb4。到这里根因清楚了升级前应用使用的老版本 MySQL 驱动在 5.7 环境下能协商出正确的连接字符集升级到 8.0 后连接层默认值变化驱动和服务端协商出来的结果不再匹配表结构。字符串从应用传到 MySQL 的过程中已经被按错误字符集解码、编码最终落库就变成了乱码。1.2 默认字符集和排序规则的版本差异MySQL 8.0 对字符集的默认值做了一次比较大的调整。很多 5.7 实例可能靠 my.cnf 显式配置了utf8mb4所以升级前感知不强但 8.0 官方默认值已经变成character_set_server默认utf8mb4collation_server默认utf8mb4_0900_ai_ci也就是说升级后即使你什么都不改新建库、新建表的默认字符集也是 utf8mb4默认排序规则也换了。这里有个容易误判的点已经存在的旧表不会自动转换。5.7 里建的utf8列升级后仍然还是旧定义utf8在 8.0 中依然指向旧的 utf8mb3官方已经明确提示后续大版本会逐步淘汰 utf8mb3。于是一个实例里就可能同时存在 utf8mb3、latin1、utf8mb4 三种字符集跨表 join、union、子查询时一旦遇到排序规则冲突轻则报错重则结果错位。另外utf8mb4_0900_ai_ci这套排序规则基于 UCA 9.0.0和 5.7 时代常用的utf8mb4_general_ci在字符比较权重上有实际差异。带重音字符、特殊扩展字符在两种排序规则下的先后关系并不一致这直接导致升级后同样的ORDER BY语句排序结果可能变掉。后面我会专门讲这个坑。2. 升级前必须盘点的存量字符集账本很多团队升级前不做字符集盘点等到升级后才发现几十张核心表还停留在 latin1或者排序规则混用严重这时候再回头改结构成本高不少。盘点这件事其实用几条 SQL 就能完成建议升级前先跑一遍。2.1 三条 SQL 找出库、表、列里的字符集现状第一看数据库层默认字符集SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA;第二看表层SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN (mysql, information_schema, performance_schema, sys);第三看列级SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN (mysql, information_schema, performance_schema, sys) AND CHARACTER_SET_NAME IS NOT NULL AND CHARACTER_SET_NAME utf8mb4 ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;注意排除系统库。8.0 的mysql系统库已经被强制转换成 utf8mb4不需要也不应该手动去改。第三条例外检查的是所有非 utf8mb4 的列如果结果集很大说明历史欠账不少需要排期处理。除了这三条还要看一眼排序规则分布重点查utf8mb4_general_ci和utf8mb4_0900_ai_ci的混用情况因为混用排序规则会在后续 SQL 执行时造成很多隐形问题。2.2 索引字节膨胀DDL 说卡就卡字符集转换最常见的“硬障碍”是索引超长。MySQL 的 InnoDB 索引键长度有上限5.7 里如果没开innodb_large_prefix最大是 767 字节8.0 默认 row_format 为 DYNAMIC支持 3072 字节但旧表如果还停留在 COMPACT 格式依然会受旧限制。举个例子一张老表里有个name varchar(255)之前用utf8一个字符最多 3 字节建索引长度是 255×3765恰好小于 767能建成功。如果升级后统一改成utf8mb4一个字符最多 4 字节同样的索引变成 255×41020超过旧限制ALTER TABLE直接报Specified key was too long。所以转换前要把大表、联合索引、主键过长的表单独列出来。处理顺序应该是先把表的 row_format 改成 DYNAMIC让索引键上限提升到 3072 字节再执行字符集转换如果仍超限就需要收缩索引列长度或者用前缀索引替代整列索引。另外一个容易忽视的连带问题varchar单行最大字节数 65535改成 utf8mb4 后原本能存 20000 多字符的字段会缩水到约 16000 多字符极端情况下建表都会失败。盘点时也要把字段长度超标的列找出来。2.3 特殊 caselatin1 列里其实存的是 GBK 或 UTF-8这是字符集升级里最容易被坑出事故的隐藏点。早期很多系统建表用的是 latin1但业务写入的数据实际是 UTF-8 或 GBK 字节只是连接层和存储层恰好一致看起来一切正常。升级时如果无脑执行ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4MySQL 会先把原列数据按 latin1 逐字节解释成字符再转成 utf8mb4。那些本来是 UTF-8 编码的字节经过这层“错误解释”会变成一堆 Moji 乱码而且很难还原。处理前必须抽样确认旧列里到底是什么编码。推荐先看字节序列SELECT name, HEX(name) FROM sales.customer LIMIT 10;如果中文字符对应的十六进制是e4 b8 ad这类说明实际存储的是 UTF-8 字节如果是d6 d0之类则有可能是 GBK。确认之后才能选对转换路径。对于“列定义是 latin1、实际字节是 UTF-8”的表可以绕一步处理先把列改成二进制类型保留原始字节再从二进制改到 utf8mb4让 MySQL 直接用目标字符集解释字节ALTER TABLE sales.customer MODIFY name VARBINARY(255); ALTER TABLE sales.customer MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;如果实际字节是 GBK直接用 MySQL 函数转也可以但更稳妥的做法是把数据导出到外部脚本用应用层逐条解码再写回。这一步不建议在生产上直接赌强烈建议先拿一个表的备份出来完整演练一遍。3. 升级后必踩的三个字符集坑附排查链路这一节把升级后最常见的三个故障现象和完整排查链路写出来。每一个现象背后都有相对固定的定位路径按顺序查基本不会被绕晕。3.1 老数据正常、新写入乱码先查连接参数现象可能有两种老数据正常、新数据乱码或者老数据乱码、新数据正常。不管哪种优先查连接层而不是急着改表。排查链路查看服务端全局字符集变量SHOW VARIABLES LIKE character_set_%; SHOW VARIABLES LIKE collation_%;查看当前连接会话的变量SHOW SESSION VARIABLES LIKE character_set_%; SHOW SESSION VARIABLES LIKE collation_%;用命令行客户端强制 utf8mb4 复测mysql --default-character-setutf8mb4 -h10.0.0.10 -uapp -p -e SELECT * FROM sales.orders LIMIT 1;如果命令行正常、应用乱码问题基本就在应用连接串或驱动版本。检查连接串里是否显式指定了编码比如 Java 场景jdbc:mysql://10.0.0.10:3306/sales?useUnicodetruecharacterEncodingUTF-8connectionCollationutf8mb4_0900_ai_ciuseSSLfalse这里要特别注意升级 8.0 后JDBC 驱动尽量同步升级到 8.0.x老驱动 5.1.x 在握手协商上和 8.0 服务端存在兼容问题。老驱动可能把characterEncodingUTF-8映射成 utf8mb3导致 emoji 和生僻字写入报错或乱码。还有一类坑来自连接池。连接池复用旧连接时可能没有重新初始化字符集变量。可以在连接池配置里加连接初始化 SQL比如 HikariCP 的connectionInitSql设置为SET NAMES utf8mb4从源头保证每个新连接都使用一致字符集。3.2 order by 结果和旧库不一致排序规则差异另一个很容易让业务方炸毛的问题是同一句SELECT ... ORDER BY name在 5.7 和 8.0 里返回顺序不一样。这不是索引失效也不是数据丢了是排序规则的比较权重变了。utf8mb4_general_ci是一套简化排序逻辑很多字符按码位直接比较速度尚可但结果不精确utf8mb4_0900_ai_ci基于 Unicode Collation Algorithm会按语言习惯处理大小写、重音和部分特殊字符所以某些字符的先后关系会发生变化。比如带重音的 é、特殊扩展字符 ß、以及部分合成字符在两种规则下的排列顺序就可能不同。排查方法很简单把排序列强制指定成旧排序规则对比结果是否跟老库一致。SELECT name FROM sales.customer ORDER BY name COLLATE utf8mb4_general_ci;如果强制旧规则后顺序和老库一致那就是排序规则差异造成的。这时候要做一个业务决策核心报表、分类列表、去重逻辑如果依赖特定排序需要在 SQL 里显式COLLATE或者把对应列的排序规则固定为业务预期值如果只是展示型排序统一到 0900 规则后让应用回归一遍即可。这个决策最好在升级前就做因为排序规则会影响唯一索引的判定。某些在 general_ci 下被认为不同的字符在 0900_ai_ci 下可能被认为相同插入数据时会多出重复键冲突。3.3 备份恢复后字符集漂移mysqldump 和驱动的双坑升级之后做备份恢复演练时还可能遇到两个新问题一个是把 8.0 的备份恢复到 5.7 或更低版本时直接报Unknown collation: utf8mb4_0900_ai_ci。因为 0900 系列排序规则是 8.0 新增的低版本根本不认识。如果业务未来还要支持降级恢复备份方案里需要把排序规则兼容性考虑进去。另一个是 mysqldump 导出文件里的字符集声明和实际数据不一致。导出时如果没显式指定字符集默认依赖连接协商结果一旦协商出 latin1导出的 SQL 文件里中文就可能已经变成乱码或转义序列。我现在的固定操作是mysqldump --default-character-setutf8mb4 --set-gtid-purgedOFF --single-transaction --routines --triggers --events sales sales_dump.sql恢复时同样显式指定mysql --default-character-setutf8mb4 -h127.0.0.1 -uroot -p sales sales_dump.sql如果是 8.0 跨版本或跨平台恢复建议先恢复到临时实例抽样对比关键表的COUNT(*)和几行典型中文/emoji 数据的HEX()确认无误后再切业务。备份工具、导入导出中间件同样要检查字符集参数不能只靠 mysqldump 一个环节。4. 逐步修复实例层、库表层、连接层一次改到位字符集问题很少是单一原因往往是实例配置、存量表结构、连接参数三处都有历史问题。只改一处短时间内可能看着正常过几天换个连接方式又会复现。所以修复方案必须分层推进。4.1 实例层my.cnf 参数和动态变量设置顺序先看当前实例配置确认character_set_server和collation_server是否已经正确落到 utf8mb4。如果是从老实例直接升上来的my.cnf 里可能还留着旧配置或者被 8.0 初始化脚本改写需要人工核对。推荐配置片段[mysqld] character-set-server utf8mb4 collation-server utf8mb4_0900_ai_ci修改后重启实例或者用动态变量临时调整SET GLOBAL character_set_server utf8mb4; SET GLOBAL collation_server utf8mb4_0900_ai_ci;注意设置顺序先设置字符集再设置排序规则而且排序规则必须与字符集匹配否则直接报错。动态设置只影响之后新建连接和新建对象存量连接还是老设置所以正式变更还是建议走配置重启。有一种说法是把skip-character-set-client-handshake打开强制所有客户端都用服务器字符集。这个参数确实能让部分老客户端乱码问题“看上去消失”但它掩盖了客户端声明的编码可能让已经正确设置 utf8mb4 的客户端反而出现乱码。我的建议是不到万不得已不要开优先修客户端。4.2 库表层ALTER TABLE CONVERT TO 的实操与留坑单表转换命令很简单ALTER TABLE sales.orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CONVERT TO CHARACTER SET会重建整张表转换所有字符型列。小表无所谓大表要特别注意锁表时间。如果是几百 G 的在线表建议用pt-online-schema-change这类工具避免直接 ALTER 把业务写阻塞。批量表转换可以先从 information_schema 生成语句再分批次执行SELECT CONCAT( ALTER TABLE , TABLE_SCHEMA, ., TABLE_NAME, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ) AS alter_sql FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN (mysql, information_schema, performance_schema, sys) AND TABLE_COLLATION utf8mb4_0900_ai_ci;生成后不要直接一股脑全跑先挑业务低峰、表较小的结构执行每跑完几张表马上用文章开头的盘点 SQL 复核确认转换生效。这里必须提醒几个容易忽略的点ALTER TABLE t DEFAULT CHARACTER SET utf8mb4不会转换已有列只会改表默认值新加列用默认旧列还是原样ALTER DATABASE db CHARACTER SET utf8mb4同样只影响后续新建表转换过程中如果遇到索引超长先按 2.2 节的步骤调整 row_format 或索引定义如果旧列实际存的是非原编码数据务必先按 2.3 节的方式处理不要直接 CONVERT。4.3 连接层JDBC 驱动、连接串、客户端的统一实例和表修好之后连接层不统一问题还会回来。这个环节最容易漏的是“只改了服务端没改客户端”。常见语言和中间件的设置方式Java JDBC使用 8.0.x 驱动连接串加useUnicodetruecharacterEncodingUTF-8connectionCollationutf8mb4_0900_ai_ciPython PyMySQL连接参数charsetutf8mb4Go go-sql-driver/mysql连接串加charsetutf8mb4collationutf8mb4_0900_ai_ci命令行客户端my.cnf 的[client]段设置default-character-setutf8mb4。改完连接参数建议在所有业务节点上跑一个验证 SQL确认会话内三个关键变量一致SELECT character_set_client, character_set_connection, character_set_results;正常状态下这三个变量应该是同一个值常见异常是character_set_client被中间件改成了 latin1、character_set_results还是 utf8mb4这种割裂配置最容易造成数据写入正常、读取乱码或者反过来。如果企业里用了数据库中间件或代理层还要在代理层单独配置字符集转发规则否则连接串改了也可能在代理层被重写。5. 修复后的验收标准、以及值得坚持的几条底线修完之后不能只看业务说“好像正常了”要有可量化的验收标准。下面这套验收动作我每次升级后都会完整跑一遍确认没问题才放量。5.1 验收 SQL 和测试数据样本先插入覆盖度足够高的测试数据至少包含中英文、emoji、生僻字、繁简体混排、特殊符号CREATE TABLE IF NOT EXISTS sales.charset_verify ( id INT PRIMARY KEY AUTO_INCREMENT, val VARCHAR(200) ); INSERT INTO sales.charset_verify (val) VALUES (中文测试), (hello, world), (Emoji ), (生僻字), (中文English混合);插入后检查十六进制确认存储字节符合预期SELECT val, HEX(val), CHAR_LENGTH(val), LENGTH(val) FROM sales.charset_verify;LENGTH(val)结果如果明显大于CHAR_LENGTH(val)说明多字节字符正常落库。随后跑一遍关键业务 SQL包括字符串等值查询、LIKE 查询、ORDER BY、GROUP BY、DISTINCT、表关联监控是否有Illegal mix of collations报错。再盘点一次实例里的字符集分布目标状态是业务库所有表、列统一为utf8mb4和utf8mb4_0900_ai_ci至少要做到全部字符集都是 utf8mb4 家族不能出现 latin1 和 utf8mb4 混用。5.2 主从复制和备份链路里的字符集校验如果是主从架构升级后必须额外验证复制链路。最稳妥的做法是先在从库上执行STOP SLAVE然后在主库插入上面那批验证数据再START SLAVE最后在从库检查数据是否与主库一致。重点不是COUNT(*)而是中文字段和 emoji 字段的HEX()是否完全一致因为只有字节一致才能说明复制过程没有发生字符集转换漂移。备份链路建议做一次完整恢复演练用生产机的备份恢复到临时实例恢复后用同样的校验 SQL 对比数据是否一致。这一步虽然耗时但比出问题后再补救成本低得多。另外不建议把 8.0 的备份直接拿去 5.7 环境恢复除非确认 dump 文件里完全没有utf8mb4_0900_ai_ci。我之前就见过有人图省事把 8.0 的 dump 在 5.7 上恢复结果 CREATE TABLE 阶段直接报排序规则不支持白白浪费一晚上。5.3 后续开发中如何防止再次混用字符集字符集问题属于“出一次事就伤筋动骨、不出事没人关心”的类型。修复完之后需要在开发流程里加几道防线新库、新表统一使用utf8mb4utf8mb4_0900_ai_ci由 DDL 审核工具或平台生成模板强制约束所有连接配置统一使用 utf8mb4禁止在代码里执行SET NAMES latin1之类的覆盖语句上线前增加一个巡检脚本扫描 information_schema找出新增的非 utf8mb4 对象直接阻断发布数据库中间件和连接池的字符集参数写进基础设施标准文档避免每次新环境都靠人肉确认。我个人感受最深的一条是升级 8.0 不能只盯着版本特性字符集这类“默认值变化”会比大多数新特性更早影响业务。升级前先跑一遍盘点 SQL把库表结构、索引长度、连接参数、备份恢复链路全部验证完再去谈新功能整个切换过程会稳很多。
