MySQL 字符集迁移旧表时统一字符集排序规则的实现方法

旧表字符集迁移，关键不是把表名后面加上一句 DEFAULT CHARACTER SET utf8mb4 就结束，而是同时处理表默认值、显式字符列和应用连接。建议先以 utf8mb4 为目标，再明确一套排序规则；对现有数据做备份和抽样比对后，用 ALTER TABLE 完成表级转换，最后检查仍然覆盖表默认值的列。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/charset.html

先分清表、列和连接三个字符集层级

MySQL 的字符集设置不是一个全局开关。数据库可以提供默认值，表可以继续覆盖，CHAR、VARCHAR、TEXT 等字符列还可以写自己的 CHARACTER SET 与 COLLATE。应用连接则决定客户端与服务端如何解释传输中的字符串。

先用元数据找出真实状态，重点看表默认值和列级覆盖：

-- 查看表级默认字符集和排序规则
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop'
  AND TABLE_NAME = 'orders';

-- 查看字符列是否显式覆盖了表默认值
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'shop'
  AND TABLE_NAME = 'orders'
  AND CHARACTER_SET_NAME IS NOT NULL;

-- 查看当前连接的三类字符集
SELECT @@character_set_client,
       @@character_set_connection,
       @@character_set_results,
       @@collation_connection;

确定 utf8mb4 和目标排序规则

目标组合要同时回答两个问题：数据是否需要完整 Unicode，以及字符串比较、排序和唯一索引希望采用什么语义。MySQL 8.4 手册建议尽可能使用 utf8mb4；utf8 在 MySQL 中是已弃用的 utf8mb3 别名，新迁移不要把它当作四字节 UTF-8。

排序规则不要只看名字长度。可以先列出当前实例支持的候选，再结合业务规则选择，例如旧系统需要稳定兼容时使用既有的 utf8mb4_unicode_ci，新系统则应确认目标版本支持的默认排序规则和大小写、重音比较行为。

用 ALTER TABLE 迁移并保留回滚边界

表级迁移可以把字符列转换到目标字符集，并把表默认值设为同一组合。执行前先保存建表语句和备份；生产环境还应确认变更窗口、锁表影响与长索引限制。下面的 CONVERT TO CHARACTER SET 会处理字符列，COLLATE 明确指定表和列默认比较规则：

-- 迁移前保存结构，便于核对和回滚
SHOW CREATE TABLE shop.orders;

-- 将表内字符列转换为目标字符集并统一默认排序规则
ALTER TABLE shop.orders
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- 只有明确需要例外语义时，才对单列保留显式定义
ALTER TABLE shop.orders
  MODIFY customer_name VARCHAR(120)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci
  NOT NULL;

如果某列实际保存的字节编码与列元数据不一致，直接转换可能无法得到正确文字；官方文档也提示，不兼容字符集之间的转换可能有数据损失。因此乱码数据不能靠重复执行 ALTER TABLE 修复，必须先确认原始字节含义，必要时走单独的数据清洗方案。

同步连接设置并完成迁移后的核对

表已经统一，连接仍可能沿用旧配置。应用连接池初始化时要明确使用 utf8mb4，并让驱动负责设置会话参数；不要只在某个请求里临时执行一次 SET NAMES，否则连接复用时容易出现状态不一致。

-- 在专用迁移连接中确认会话字符集；生产应用优先使用驱动配置
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 迁移后核对表和字符列的最终状态
SELECT TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders';

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders'
  AND CHARACTER_SET_NAME IS NOT NULL;

最后做三类抽样：中文、四字节字符和边界长度字符串；再检查唯一索引涉及的字符列，确认排序规则变化没有把原本不同的值视为相同。若索引定义很长，还要结合实例的索引长度能力评估是否需要调整列长度或索引前缀。

相关问题

只修改表的 COLLATE，列会全部同步吗？

表级设置主要是默认值。显式写过字符集或排序规则的列仍应单独检查，必要时使用 MODIFY。

utf8 和 utf8mb4 可以直接当成一回事吗？

不可以。MySQL 中的 utf8 是 utf8mb3 的弃用别名，不能代表完整的四字节 UTF-8 能力。

迁移前为什么要看连接字符集？

客户端与服务端的编码协商属于通信边界。表结构正确，但连接仍按旧字符集解释数据，读写时仍可能产生乱码或隐式转换。

字符集迁移后发现少了字符怎么办？

先停止继续改表，保留原备份和失败日志，确认原列字节编码及目标字符集覆盖范围，再从备份恢复或按数据清洗方案重新迁移。