最近在给一套跑了快六年的存量库做规范化升级目标是把所有业务表的字符集从原来的latin1和utf8mb3统一收敛到utf8mb4排序规则也一并理顺。这活儿看着不大真动手才发现是个需要细心的批量工程——MySQL 8.4里CHARACTER SET和COLLATION这两个参数牵一发而动全身表多了以后一个个手工改根本不现实。这篇就把我这次批量修改的完整思路、脚本写法和踩过的坑整理出来给同样要做库表字符集切换的朋友留一份能直接照着做的作业。适合运维、DBA以及需要维护存量项目的后端开发参考尤其是建库比较早、字符集五花八门的系统。1. 为什么要给MySQL表改字符集和排序规则1.1 字符集和排序规则到底是什么先把这个基础问题说透。字符集决定了一串二进制数据在MySQL里被解释成什么文字排序规则决定了这些文字用什么规则去比较大小、做排序和去重。你可以把字符集想成一本字典把排序规则想成字典里单词是怎么编排的——按字母、按拼音、还是按笔画不同排法对同一组词会得出不同的先后顺序。MySQL 8.4里最常见的字符集是utf8mb4它用最多4个字节表示一个字符覆盖了emoji、生僻字、各种语言的符号。而老项目里常见的utf8mb3就是大家常说的utf8它最多只用3个字节存不了四字节的emoji一遇到特殊字符就报错或者存成问号。latin1就更古老了只覆盖西欧字符中文进去基本都是乱码。排序规则里utf8mb4_0900_ai_ci是MySQL 8.0以后默认的一套规则基于Unicode 9.0的UCA算法对多语言排序更准确性能也更好。utf8mb4_unicode_ci是上一代的通用规则兼容性好。utf8mb4_general_ci则是最老的简化规则排序精度低一些但速度快一点点。三者不能混着用否则关联两张表时可能会因为排序规则不一致而报错或者干脆走不了索引。1.2 什么时候必须动这批表实践中触发批量修改的场景我总结下来就几类。第一类是线上出现乱码。页面显示问号、控制台里全是菱形块排查下来十有八九是某一层的字符集设置和表定义不一致。比如表是latin1客户端连接用的utf8mb4写入时MySQL做了隐式转换数据转一圈就花了。第二类是业务要支持emoji和生僻字。现在很多应用要存表情符号如果表还是utf8mb3插入emoji时会报Incorrect string value只能改表。第三类是旧库升级。系统从MySQL 5.7升到8.4库和表还是老字符集虽然8.4也能跑但默认值已经变了后续新建表、排序、索引都会出现隐性不一致时间长了就是个雷。第四类是关联查询时出现Illegal mix of collations报错。两张表join的字段字符集或排序规则不一致MySQL直接拒绝执行这种问题在业务扩容后特别常见。1.3 8.4版本和旧版本在字符集上的差异MySQL 8.4是LTS版本字符集行为延续了8.0的规则。从8.0开始MySQL服务端默认字符集就是utf8mb4默认排序规则是utf8mb4_0900_ai_ci这和5.7时代默认latin1、默认latin1_swedish_ci是完全不同的两套逻辑。还有一个容易踩的坑8.0.28开始MySQL官方已经标记utf8mb3为弃用状态8.4里虽然还能用但升级日志会一直提醒你尽快迁走。这个信号很明确存量表只要用了utf8mb3早晚得挪到utf8mb4。另外要区分utf8mb3和utf8mb4在MySQL里写utf8实际指的还是utf8mb3这是个历史遗留的坑写SQL时要特别注意别把utf8当utf8mb4用了。2. 动手前的准备工作2.1 摸清当前库表的字符集家底改之前必须先把现状摸清楚不能拍脑袋直接生成ALTER语句。我一般在服务器上先跑一套查询把库、表、列三级信息全部拉出来。查看数据库默认字符集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) ORDER BY TABLE_SCHEMA, TABLE_NAME;查看具体列的字符集SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE CHARACTER_SET_NAME IS NOT NULL AND TABLE_SCHEMA NOT IN (mysql,information_schema,performance_schema,sys) ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;这几条SQL跑完整个库的字符集画像就出来了。我习惯把结果导成CSV用Excel透视一下看看哪些表是latin1、哪些是utf8mb3、哪些已经混用。混用的情况最难搞同一张表里不同列字符集都不一样说明以前改列的时候没有统一规划这种表后面要重点盯。2.2 确定目标字符集和排序规则目标字符集基本没什么可纠结的就是utf8mb4。排序规则要花点心思选如果库是新建的、没有历史包袱直接用utf8mb4_0900_ai_ci这是8.4的默认规则也是官方推荐方向。如果要做跨版本兼容比如从5.7迁移、还会和异构系统交换数据选utf8mb4_unicode_ci更稳它兼容性好和旧系统的排序行为接近。如果只是想尽快消除报错、对排序精度不敏感可以先用utf8mb4_general_ci过渡但我不建议长期停留。选排序规则不只是改一个参数它会直接影响业务查询结果。比如中文按拼音排序还是按Unicode码点排序不同排序规则得出的顺序是不同的。线上业务如果依赖ORDER BY中文的特定顺序改完规则后必须回归测试。2.3 备份与评估影响范围字符集转换本质上是重写列里的数据属于高风险操作。我在任何生产环境动手前都会有完整的备份方案不是简单一句“有备份”就算数。具体来说对于要改的表至少保留一份逻辑备份mysqldump -u用户 -p密码 --single-transaction --set-gtid-purgedOFF \ --default-character-setutf8mb4 库名 库名_$(date %Y%m%d_%H%M%S).sql--single-transaction保证备份期间不锁表--default-character-setutf8mb4确保导出时的字符集是统一的目标值这样恢复时不会发生二次转换。另外还要确认一下表的大小。一张几百万行的表跑一次全表重写可能需要几分钟到几十分钟这个时间窗口里表的读写性能会受影响。我一般会先看表数据量SELECT TABLE_SCHEMA, TABLE_NAME, ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb, ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA 目标库 ORDER BY data_mb DESC;如果线上有主从复制还要提前确认从库延迟情况DDL在主库执行完会在从库重放大表转换会让从库延迟飙高必须避开业务高峰。3. 批量改字符集的三种可行方案3.1 方案一information_schema生成ALTER语句这是最直接、最可控的方式先用SQL从information_schema里拼出所有ALTER语句人工过一遍确认无误后再执行。生成库级修改语句SELECT CONCAT(ALTER DATABASE , SCHEMA_NAME, CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN (mysql,information_schema,performance_schema,sys);生成表级修改语句SELECT CONCAT(ALTER TABLE , TABLE_SCHEMA, ., TABLE_NAME, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN (mysql,information_schema,performance_schema,sys) AND TABLE_COLLATION utf8mb4_0900_ai_ci;有人会问改表为什么要用CONVERT而不是直接写DEFAULT CHARACTER SET这两者的区别很关键。ALTER TABLE ... DEFAULT CHARACTER SET只改表的默认字符集已经存在的列不会动属于“改壳不改芯”ALTER TABLE ... CONVERT TO CHARACTER SET会把所有字符型列的数据真正转换到新字符集这才是我们需要的。生成的语句先不要急着执行存到SQL文件里人工扫一遍。表特别多的时候可以按库拆分一个库一个文件方便分批跑。3.2 方案二用Shell脚本循环执行如果表太多生成的SQL文件就会很大一次喂给mysql客户端执行压力大也不方便观察进度我习惯拆成小批量循环跑。先把需要执行的表清单生成出来mysql -u用户 -p密码 -N -e SELECT CONCAT(TABLE_SCHEMA, ., TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA 目标库 AND TABLE_COLLATION utf8mb4_0900_ai_ci; table_list.txt然后写个循环脚本#!/bin/bash DB_USER用户 DB_PASS密码 DB_NAME目标库 while read tbl; do echo 开始处理: $tbl $(date %H:%M:%S) mysql -u${DB_USER} -p${DB_PASS} \ --default-character-setutf8mb4 \ ${DB_NAME} -e ALTER TABLE \${tbl}\ CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; if [ $? -eq 0 ]; then echo 完成: $tbl else echo 失败: $tbl请检查错误日志 error.log fi done table_list.txt这里要注意表名如果带库前缀在MySQL里要写成库名.表名反引号不能丢。还有一点客户端连接默认字符集要指定为utf8mb4否则你在终端里输入的SQL中文或特殊字符可能又被转码产生新的乱码。3.3 方案三用存储过程实现自动化不想写Shell或者想在MySQL内部完全自治可以用存储过程遍历。这个方案适合那种表特别多、又希望把过程记录下来的场景。DELIMITER $$ CREATE PROCEDURE convert_tables_charset(IN db_name VARCHAR(64)) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE tbl_name VARCHAR(64); DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA db_name AND TABLE_COLLATION utf8mb4_0900_ai_ci; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO tbl_name; IF done THEN LEAVE read_loop; END IF; SET sql CONCAT(ALTER TABLE , db_name, ., tbl_name, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; INSERT INTO charset_change_log(db_name, table_name, changed_at) VALUES (db_name, tbl_name, NOW()); END LOOP; CLOSE cur; END$$ DELIMITER ;存储过程里有个必须注意的点动态SQL只能用PREPARE加EXECUTE来执行不能直接放到普通SQL里。循环里每改一张表就写一条日志后续要追踪哪些表改过、哪些没改一目了然。3.4 三种方案的对比与选型建议方案优点缺点适合场景生成SQL文件批量执行直观、可控、方便审查表多时文件巨大不好观察进度表数量几十到几百张需要先人工确认Shell脚本循环执行进度清晰、单表失败不影响后续环境依赖bash跨平台要改造表数量多、需要分批跑、要记录失败日志存储过程自动化全库内自治、日志完整调试麻烦、出错时状态不可见表极多、希望流程可重复执行我实际用的最多的是第一种加第二种的组合先用SQL生成全部ALTER语句人工审查一遍后拆成小文件再用shell循环一批批喂给MySQL。这样既有审查的安全性又有执行进度的可见性。存储过程适合那种定期要执行、表结构频繁变化的场景写一次能反复用。4. 实操记录从确认到执行的完整过程4.1 生成完整ALTER语句清单这次我处理的库有200多张表跑了一遍2.1里的查询发现latin1的表有60多张utf8mb3的有150多张还有几张大表的个别列是utf8mb4整体是个大杂烩。我先跑生成库级语句的SQL把每个库的默认字符集改成utf8mb4SELECT CONCAT(ALTER DATABASE , SCHEMA_NAME, CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME target_db;然后生成表级转换语句SELECT CONCAT(ALTER TABLE , TABLE_SCHEMA, ., TABLE_NAME, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;) FROM information_schema.TABLES WHERE TABLE_SCHEMA target_db AND TABLE_COLLATION utf8mb4_0900_ai_ci ORDER BY TABLE_NAME;生成的结果我保存成alter_tables.sql用文本编辑器打开抽查了十几条主要看有没有生成系统库的表、有没有已改过的表重复出现、列类型有没有特殊的比如enum、set。enum和set它们底层也是字符串CONVERT会自动处理但你心里要有数。抽查没问题后我先挑了一张几万行的小表单独执行观察执行时间和数据变化确认没问题再铺开。4.2 分批执行与进度观察200多张表不可能一把梭。我按照表大小把清单分成了四批每批50张左右大小表穿插着跑避免把小表集中在第一批、大表全堆在最后造成尾端压力。执行的时候我打开了两个窗口一个跑改动脚本一个持续观察数据库状态。主要看三项指标进程列表里有没有长时间运行的DDL、主从延时是否飙升、错误日志有没有新的报错。监控SQL长这样SHOW PROCESSLIST;如果发现某个ALTER已经跑了几分钟还没结束在低峰期还能接受但高峰期必须立刻评估要不要终止。MySQL 8.4支持在线DDL但CONVERT TO CHARACTER SET这种操作在某些版本和行格式下并不能完全做到LOCKNONE表会有短暂元数据锁窗口这就是为什么我坚持选低峰期执行。实际执行时每批中间休息30秒让主从中继日志消化一下别把从库压垮。这个习惯在从库比较弱的场景下特别重要节奏放慢一点整体反而更稳。4.3 验证修改结果全部执行完验证才是重头戏。我先复查一遍表的字符集是否全部收敛SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA target_db AND TABLE_COLLATION utf8mb4_0900_ai_ci;返回结果应该是0行只要还剩1行说明有表没改到或者改失败了。接着验证列级字符集SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA target_db AND CHARACTER_SET_NAME IS NOT NULL AND CHARACTER_SET_NAME utf8mb4;这步很重要因为有些表之前的列级字符集被单独设置过CONVERT TO CHARACTER SET虽然会统一列但如果有触发器或者生成列干扰偶尔会有漏网之鱼。然后做数据验证。我往每张抽样表里插入一条包含emoji和中文的记录确认能正常写入和读出。排序方面跑一个典型的ORDER BY中文查询和改动前的顺序做对比确认业务可接受。5. 常见问题与排查技巧5.1 数据乱码问题乱码是字符集改动中最多见的问题但它的根源往往不在表定义而在于连接层。天底下乱码的套路就那么几种表是latin1、客户端是utf8mb4、连接参数是utf8、或者应用代码里没用utf8mb4。排查的时候先别急着改表先确认连接状态SHOW VARIABLES LIKE character_set_client; SHOW VARIABLES LIKE character_set_connection; SHOW VARIABLES LIKE character_set_results;这三项如果和表定义不一致数据写入时就会发生隐式转换。最快的解决办法是在应用连接初始化时执行一次SET NAMES utf8mb4或者在连接串里显式指定characterEncodingutf8mb4。我处理过很多所谓“改完字符集还乱码”的case九成都是连接层没对齐。5.2 索引长度超限utf8mb3转utf8mb4有个特别容易爆的坑每个字符从3字节变成4字节而InnoDB的索引键是有长度上限的。默认16KB页、DYNAMIC行格式下单个索引键最大3072字节。一个VARCHAR(255)在utf8mb3下是765字节没问题转成utf8mb4后变成1020字节如果几个这样的列组合成复合索引轻松超过3072字节。转换时会报错ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes遇到这种情况要么把列改成前缀索引比如index(col(191))要么调整列长度把VARCHAR(255)收敛到VARCHAR(191)以内。为什么是191因为191乘以4等于764加两个字节的变长前缀刚好在767字节内这是旧版InnoDB的限制毕竟MySQL老版本默认页是8KB或16KB时索引键限制更严格。升级到8.4后放宽到3072但单个列不要超过768字节是比较稳妥的经验值。5.3 DDL锁表与性能问题大表改字符集最怕的就是锁。MySQL 8.4虽然支持在线DDL但CONVERT TO CHARACTER SET在不少场景下还是会触发全表重建重建期间表上的写操作会被阻塞或延迟。MySQL会自动选择最优的执行策略但表很大的时候耗时是绕不过去的。我的建议是三条第一一定要在业务低峰执行。第二拆成多批每批之间留观察窗口别一口气跑完。第三如果表特别大、超过千万行优先考虑用pt-online-schema-change这类工具它通过触发器把变更做成在线增量同步能极大降低锁表时间。工具虽好但使用前要在测试环境完整演练一遍别上来直接搞生产。5.4 已有数据的转换风险这是最隐蔽的一个坑。如果表里本来就已经存了乱码数据——比如latin1表里被写过utf8字节——直接在二进制层面做转换乱码可能从一串变成另一串不会自动恢复。我遇到过的典型场景一张表是latin1但历史上有段时间应用连接用了utf8写入的中文在表里被错误存储读出时已经是乱码。对这种表直接CONVERT TO CHARACTER SET utf8mb4MySQL只是把存储的latin1字节按latin1解释成Unicode已经错掉的字节并不会修复。正确的姿势是先把坏列转成二进制中间态再转成目标字符集ALTER TABLE t MODIFY col VARBINARY(255); ALTER TABLE t MODIFY col VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;这样MySQL会拿到最原始的字节重新按utf8mb4解释如果原始字节本来就是utf8编码数据就能救回来。当然这招不能保证100%恢复所有数据所以我在执行前都会强调必须先备份原始数据转换后逐表抽查做不了就不碰历史脏数据。还有一点要提醒如果你的表上有触发器、外键转换字符集时它们可能会跟着受影响或者出现不兼容报错。改动前先查一遍触发器依赖做好预案。最后再分享一个我个人的操作习惯整个改动过程里我在所有生成的SQL和脚本里都强制加了库名过滤绝不把系统库和业务库混在一起改。这个习惯救过我太多次——information_schema里能看到mysql库的表如果不加过滤条件批处理脚本可能会把系统表的字符集也改了那种事故恢复起来极其痛苦。如果你现在也要做类似的批量修改我的建议是先拿一个测试库把整个流程走通确认查询结果、脚本逻辑都符合预期再动生产。字符集这事慢就是快。
MySQL 8.4批量修改表字符集与排序规则实战方案
最近在给一套跑了快六年的存量库做规范化升级目标是把所有业务表的字符集从原来的latin1和utf8mb3统一收敛到utf8mb4排序规则也一并理顺。这活儿看着不大真动手才发现是个需要细心的批量工程——MySQL 8.4里CHARACTER SET和COLLATION这两个参数牵一发而动全身表多了以后一个个手工改根本不现实。这篇就把我这次批量修改的完整思路、脚本写法和踩过的坑整理出来给同样要做库表字符集切换的朋友留一份能直接照着做的作业。适合运维、DBA以及需要维护存量项目的后端开发参考尤其是建库比较早、字符集五花八门的系统。1. 为什么要给MySQL表改字符集和排序规则1.1 字符集和排序规则到底是什么先把这个基础问题说透。字符集决定了一串二进制数据在MySQL里被解释成什么文字排序规则决定了这些文字用什么规则去比较大小、做排序和去重。你可以把字符集想成一本字典把排序规则想成字典里单词是怎么编排的——按字母、按拼音、还是按笔画不同排法对同一组词会得出不同的先后顺序。MySQL 8.4里最常见的字符集是utf8mb4它用最多4个字节表示一个字符覆盖了emoji、生僻字、各种语言的符号。而老项目里常见的utf8mb3就是大家常说的utf8它最多只用3个字节存不了四字节的emoji一遇到特殊字符就报错或者存成问号。latin1就更古老了只覆盖西欧字符中文进去基本都是乱码。排序规则里utf8mb4_0900_ai_ci是MySQL 8.0以后默认的一套规则基于Unicode 9.0的UCA算法对多语言排序更准确性能也更好。utf8mb4_unicode_ci是上一代的通用规则兼容性好。utf8mb4_general_ci则是最老的简化规则排序精度低一些但速度快一点点。三者不能混着用否则关联两张表时可能会因为排序规则不一致而报错或者干脆走不了索引。1.2 什么时候必须动这批表实践中触发批量修改的场景我总结下来就几类。第一类是线上出现乱码。页面显示问号、控制台里全是菱形块排查下来十有八九是某一层的字符集设置和表定义不一致。比如表是latin1客户端连接用的utf8mb4写入时MySQL做了隐式转换数据转一圈就花了。第二类是业务要支持emoji和生僻字。现在很多应用要存表情符号如果表还是utf8mb3插入emoji时会报Incorrect string value只能改表。第三类是旧库升级。系统从MySQL 5.7升到8.4库和表还是老字符集虽然8.4也能跑但默认值已经变了后续新建表、排序、索引都会出现隐性不一致时间长了就是个雷。第四类是关联查询时出现Illegal mix of collations报错。两张表join的字段字符集或排序规则不一致MySQL直接拒绝执行这种问题在业务扩容后特别常见。1.3 8.4版本和旧版本在字符集上的差异MySQL 8.4是LTS版本字符集行为延续了8.0的规则。从8.0开始MySQL服务端默认字符集就是utf8mb4默认排序规则是utf8mb4_0900_ai_ci这和5.7时代默认latin1、默认latin1_swedish_ci是完全不同的两套逻辑。还有一个容易踩的坑8.0.28开始MySQL官方已经标记utf8mb3为弃用状态8.4里虽然还能用但升级日志会一直提醒你尽快迁走。这个信号很明确存量表只要用了utf8mb3早晚得挪到utf8mb4。另外要区分utf8mb3和utf8mb4在MySQL里写utf8实际指的还是utf8mb3这是个历史遗留的坑写SQL时要特别注意别把utf8当utf8mb4用了。2. 动手前的准备工作2.1 摸清当前库表的字符集家底改之前必须先把现状摸清楚不能拍脑袋直接生成ALTER语句。我一般在服务器上先跑一套查询把库、表、列三级信息全部拉出来。查看数据库默认字符集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) ORDER BY TABLE_SCHEMA, TABLE_NAME;查看具体列的字符集SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE CHARACTER_SET_NAME IS NOT NULL AND TABLE_SCHEMA NOT IN (mysql,information_schema,performance_schema,sys) ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;这几条SQL跑完整个库的字符集画像就出来了。我习惯把结果导成CSV用Excel透视一下看看哪些表是latin1、哪些是utf8mb3、哪些已经混用。混用的情况最难搞同一张表里不同列字符集都不一样说明以前改列的时候没有统一规划这种表后面要重点盯。2.2 确定目标字符集和排序规则目标字符集基本没什么可纠结的就是utf8mb4。排序规则要花点心思选如果库是新建的、没有历史包袱直接用utf8mb4_0900_ai_ci这是8.4的默认规则也是官方推荐方向。如果要做跨版本兼容比如从5.7迁移、还会和异构系统交换数据选utf8mb4_unicode_ci更稳它兼容性好和旧系统的排序行为接近。如果只是想尽快消除报错、对排序精度不敏感可以先用utf8mb4_general_ci过渡但我不建议长期停留。选排序规则不只是改一个参数它会直接影响业务查询结果。比如中文按拼音排序还是按Unicode码点排序不同排序规则得出的顺序是不同的。线上业务如果依赖ORDER BY中文的特定顺序改完规则后必须回归测试。2.3 备份与评估影响范围字符集转换本质上是重写列里的数据属于高风险操作。我在任何生产环境动手前都会有完整的备份方案不是简单一句“有备份”就算数。具体来说对于要改的表至少保留一份逻辑备份mysqldump -u用户 -p密码 --single-transaction --set-gtid-purgedOFF \ --default-character-setutf8mb4 库名 库名_$(date %Y%m%d_%H%M%S).sql--single-transaction保证备份期间不锁表--default-character-setutf8mb4确保导出时的字符集是统一的目标值这样恢复时不会发生二次转换。另外还要确认一下表的大小。一张几百万行的表跑一次全表重写可能需要几分钟到几十分钟这个时间窗口里表的读写性能会受影响。我一般会先看表数据量SELECT TABLE_SCHEMA, TABLE_NAME, ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb, ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA 目标库 ORDER BY data_mb DESC;如果线上有主从复制还要提前确认从库延迟情况DDL在主库执行完会在从库重放大表转换会让从库延迟飙高必须避开业务高峰。3. 批量改字符集的三种可行方案3.1 方案一information_schema生成ALTER语句这是最直接、最可控的方式先用SQL从information_schema里拼出所有ALTER语句人工过一遍确认无误后再执行。生成库级修改语句SELECT CONCAT(ALTER DATABASE , SCHEMA_NAME, CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN (mysql,information_schema,performance_schema,sys);生成表级修改语句SELECT CONCAT(ALTER TABLE , TABLE_SCHEMA, ., TABLE_NAME, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN (mysql,information_schema,performance_schema,sys) AND TABLE_COLLATION utf8mb4_0900_ai_ci;有人会问改表为什么要用CONVERT而不是直接写DEFAULT CHARACTER SET这两者的区别很关键。ALTER TABLE ... DEFAULT CHARACTER SET只改表的默认字符集已经存在的列不会动属于“改壳不改芯”ALTER TABLE ... CONVERT TO CHARACTER SET会把所有字符型列的数据真正转换到新字符集这才是我们需要的。生成的语句先不要急着执行存到SQL文件里人工扫一遍。表特别多的时候可以按库拆分一个库一个文件方便分批跑。3.2 方案二用Shell脚本循环执行如果表太多生成的SQL文件就会很大一次喂给mysql客户端执行压力大也不方便观察进度我习惯拆成小批量循环跑。先把需要执行的表清单生成出来mysql -u用户 -p密码 -N -e SELECT CONCAT(TABLE_SCHEMA, ., TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA 目标库 AND TABLE_COLLATION utf8mb4_0900_ai_ci; table_list.txt然后写个循环脚本#!/bin/bash DB_USER用户 DB_PASS密码 DB_NAME目标库 while read tbl; do echo 开始处理: $tbl $(date %H:%M:%S) mysql -u${DB_USER} -p${DB_PASS} \ --default-character-setutf8mb4 \ ${DB_NAME} -e ALTER TABLE \${tbl}\ CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; if [ $? -eq 0 ]; then echo 完成: $tbl else echo 失败: $tbl请检查错误日志 error.log fi done table_list.txt这里要注意表名如果带库前缀在MySQL里要写成库名.表名反引号不能丢。还有一点客户端连接默认字符集要指定为utf8mb4否则你在终端里输入的SQL中文或特殊字符可能又被转码产生新的乱码。3.3 方案三用存储过程实现自动化不想写Shell或者想在MySQL内部完全自治可以用存储过程遍历。这个方案适合那种表特别多、又希望把过程记录下来的场景。DELIMITER $$ CREATE PROCEDURE convert_tables_charset(IN db_name VARCHAR(64)) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE tbl_name VARCHAR(64); DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA db_name AND TABLE_COLLATION utf8mb4_0900_ai_ci; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO tbl_name; IF done THEN LEAVE read_loop; END IF; SET sql CONCAT(ALTER TABLE , db_name, ., tbl_name, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; INSERT INTO charset_change_log(db_name, table_name, changed_at) VALUES (db_name, tbl_name, NOW()); END LOOP; CLOSE cur; END$$ DELIMITER ;存储过程里有个必须注意的点动态SQL只能用PREPARE加EXECUTE来执行不能直接放到普通SQL里。循环里每改一张表就写一条日志后续要追踪哪些表改过、哪些没改一目了然。3.4 三种方案的对比与选型建议方案优点缺点适合场景生成SQL文件批量执行直观、可控、方便审查表多时文件巨大不好观察进度表数量几十到几百张需要先人工确认Shell脚本循环执行进度清晰、单表失败不影响后续环境依赖bash跨平台要改造表数量多、需要分批跑、要记录失败日志存储过程自动化全库内自治、日志完整调试麻烦、出错时状态不可见表极多、希望流程可重复执行我实际用的最多的是第一种加第二种的组合先用SQL生成全部ALTER语句人工审查一遍后拆成小文件再用shell循环一批批喂给MySQL。这样既有审查的安全性又有执行进度的可见性。存储过程适合那种定期要执行、表结构频繁变化的场景写一次能反复用。4. 实操记录从确认到执行的完整过程4.1 生成完整ALTER语句清单这次我处理的库有200多张表跑了一遍2.1里的查询发现latin1的表有60多张utf8mb3的有150多张还有几张大表的个别列是utf8mb4整体是个大杂烩。我先跑生成库级语句的SQL把每个库的默认字符集改成utf8mb4SELECT CONCAT(ALTER DATABASE , SCHEMA_NAME, CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME target_db;然后生成表级转换语句SELECT CONCAT(ALTER TABLE , TABLE_SCHEMA, ., TABLE_NAME, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;) FROM information_schema.TABLES WHERE TABLE_SCHEMA target_db AND TABLE_COLLATION utf8mb4_0900_ai_ci ORDER BY TABLE_NAME;生成的结果我保存成alter_tables.sql用文本编辑器打开抽查了十几条主要看有没有生成系统库的表、有没有已改过的表重复出现、列类型有没有特殊的比如enum、set。enum和set它们底层也是字符串CONVERT会自动处理但你心里要有数。抽查没问题后我先挑了一张几万行的小表单独执行观察执行时间和数据变化确认没问题再铺开。4.2 分批执行与进度观察200多张表不可能一把梭。我按照表大小把清单分成了四批每批50张左右大小表穿插着跑避免把小表集中在第一批、大表全堆在最后造成尾端压力。执行的时候我打开了两个窗口一个跑改动脚本一个持续观察数据库状态。主要看三项指标进程列表里有没有长时间运行的DDL、主从延时是否飙升、错误日志有没有新的报错。监控SQL长这样SHOW PROCESSLIST;如果发现某个ALTER已经跑了几分钟还没结束在低峰期还能接受但高峰期必须立刻评估要不要终止。MySQL 8.4支持在线DDL但CONVERT TO CHARACTER SET这种操作在某些版本和行格式下并不能完全做到LOCKNONE表会有短暂元数据锁窗口这就是为什么我坚持选低峰期执行。实际执行时每批中间休息30秒让主从中继日志消化一下别把从库压垮。这个习惯在从库比较弱的场景下特别重要节奏放慢一点整体反而更稳。4.3 验证修改结果全部执行完验证才是重头戏。我先复查一遍表的字符集是否全部收敛SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA target_db AND TABLE_COLLATION utf8mb4_0900_ai_ci;返回结果应该是0行只要还剩1行说明有表没改到或者改失败了。接着验证列级字符集SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA target_db AND CHARACTER_SET_NAME IS NOT NULL AND CHARACTER_SET_NAME utf8mb4;这步很重要因为有些表之前的列级字符集被单独设置过CONVERT TO CHARACTER SET虽然会统一列但如果有触发器或者生成列干扰偶尔会有漏网之鱼。然后做数据验证。我往每张抽样表里插入一条包含emoji和中文的记录确认能正常写入和读出。排序方面跑一个典型的ORDER BY中文查询和改动前的顺序做对比确认业务可接受。5. 常见问题与排查技巧5.1 数据乱码问题乱码是字符集改动中最多见的问题但它的根源往往不在表定义而在于连接层。天底下乱码的套路就那么几种表是latin1、客户端是utf8mb4、连接参数是utf8、或者应用代码里没用utf8mb4。排查的时候先别急着改表先确认连接状态SHOW VARIABLES LIKE character_set_client; SHOW VARIABLES LIKE character_set_connection; SHOW VARIABLES LIKE character_set_results;这三项如果和表定义不一致数据写入时就会发生隐式转换。最快的解决办法是在应用连接初始化时执行一次SET NAMES utf8mb4或者在连接串里显式指定characterEncodingutf8mb4。我处理过很多所谓“改完字符集还乱码”的case九成都是连接层没对齐。5.2 索引长度超限utf8mb3转utf8mb4有个特别容易爆的坑每个字符从3字节变成4字节而InnoDB的索引键是有长度上限的。默认16KB页、DYNAMIC行格式下单个索引键最大3072字节。一个VARCHAR(255)在utf8mb3下是765字节没问题转成utf8mb4后变成1020字节如果几个这样的列组合成复合索引轻松超过3072字节。转换时会报错ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes遇到这种情况要么把列改成前缀索引比如index(col(191))要么调整列长度把VARCHAR(255)收敛到VARCHAR(191)以内。为什么是191因为191乘以4等于764加两个字节的变长前缀刚好在767字节内这是旧版InnoDB的限制毕竟MySQL老版本默认页是8KB或16KB时索引键限制更严格。升级到8.4后放宽到3072但单个列不要超过768字节是比较稳妥的经验值。5.3 DDL锁表与性能问题大表改字符集最怕的就是锁。MySQL 8.4虽然支持在线DDL但CONVERT TO CHARACTER SET在不少场景下还是会触发全表重建重建期间表上的写操作会被阻塞或延迟。MySQL会自动选择最优的执行策略但表很大的时候耗时是绕不过去的。我的建议是三条第一一定要在业务低峰执行。第二拆成多批每批之间留观察窗口别一口气跑完。第三如果表特别大、超过千万行优先考虑用pt-online-schema-change这类工具它通过触发器把变更做成在线增量同步能极大降低锁表时间。工具虽好但使用前要在测试环境完整演练一遍别上来直接搞生产。5.4 已有数据的转换风险这是最隐蔽的一个坑。如果表里本来就已经存了乱码数据——比如latin1表里被写过utf8字节——直接在二进制层面做转换乱码可能从一串变成另一串不会自动恢复。我遇到过的典型场景一张表是latin1但历史上有段时间应用连接用了utf8写入的中文在表里被错误存储读出时已经是乱码。对这种表直接CONVERT TO CHARACTER SET utf8mb4MySQL只是把存储的latin1字节按latin1解释成Unicode已经错掉的字节并不会修复。正确的姿势是先把坏列转成二进制中间态再转成目标字符集ALTER TABLE t MODIFY col VARBINARY(255); ALTER TABLE t MODIFY col VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;这样MySQL会拿到最原始的字节重新按utf8mb4解释如果原始字节本来就是utf8编码数据就能救回来。当然这招不能保证100%恢复所有数据所以我在执行前都会强调必须先备份原始数据转换后逐表抽查做不了就不碰历史脏数据。还有一点要提醒如果你的表上有触发器、外键转换字符集时它们可能会跟着受影响或者出现不兼容报错。改动前先查一遍触发器依赖做好预案。最后再分享一个我个人的操作习惯整个改动过程里我在所有生成的SQL和脚本里都强制加了库名过滤绝不把系统库和业务库混在一起改。这个习惯救过我太多次——information_schema里能看到mysql库的表如果不加过滤条件批处理脚本可能会把系统表的字符集也改了那种事故恢复起来极其痛苦。如果你现在也要做类似的批量修改我的建议是先拿一个测试库把整个流程走通确认查询结果、脚本逻辑都符合预期再动生产。字符集这事慢就是快。
设计模式)
