开发者日记

外观

utf8mb4 踩坑实录：emoji 乱码与索引长度超限 ​

问题背景：一条 emoji 评论为什么写进 MySQL 就报错 ​

业务上线用户昵称和评论功能后，测试同学提交了一条包含“😀”的反馈，应用日志立刻抛出异常：Incorrect string value: '\xF0\x9F\x98\x80' for column 'content'。表面看是字段内容非法，本质却是字符集配置问题：MySQL 里历史上的 utf8 实际是 utf8mb3，最多只支持 3 字节字符；而 emoji 中的大量字符位于 Unicode 辅助平面，UTF-8 编码后需要 4 字节，只有 utf8mb4 才能完整保存。

更麻烦的是，问题不只出现在表字段。客户端连接字符集、服务端字符集、库默认字符集、表字符集、列字符集、JDBC/PDO 连接参数、历史索引长度都会影响最终结果。很多团队直接执行 ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4，随后又遇到 Specified key was too long; max key length is 767 bytes，迁移被迫中断。

本文按一次真实迁移路径，梳理从定位、方案选择到落地验证的全过程，并重点解释 utf8mb4 下索引为什么更容易超长。

方案对比：只改表还不够，三层字符集要统一 ​

MySQL 字符集可以粗略分成三层：连接层、库表层、列层。连接层负责“客户端发来的字节如何解释”，库表层提供默认值，列层决定实际存储和比较规则。只改其中一层，往往会出现“表是 utf8mb4，但写入仍然乱码”或“本地正常、线上失败”的现象。

应用连接参数characterEncoding会话字符集SET NAMES utf8mb4库表列字符集CONVERT 迁移写入 emoji 验证检查 HEX 与长度

常见方案有三种：

  1. 只在应用里设置 UTF-8：成本最低，但如果 MySQL 驱动或会话仍按 utf8mb3 解码，4 字节字符会被转成 ? 或直接报错。
  2. 只改单表字符集：能解决当前表的问题，但新表仍可能继承数据库的旧默认值；历史列如果显式指定了 utf8，也不会随表默认值自动变化。
  3. 连接、库、表、列统一迁移到 utf8mb4：推荐方案。排序规则建议统一使用 utf8mb4_0900_ai_ci（MySQL 8.0）或 utf8mb4_unicode_ci（5.7 兼容性优先），避免跨表 JOIN 时出现排序规则冲突。

实操步骤：先诊断，再迁移，最后验证 emoji 写入 ​

以下 SQL 可在测试库直接运行。先构造一个典型的旧表：

sql
-- 建库：模拟历史 utf8mb3 环境
CREATE DATABASE IF NOT EXISTS demo_charset
  DEFAULT CHARACTER SET utf8
  COLLATE utf8_general_ci;

USE demo_charset;

-- 旧表：VARCHAR(255) 上有联合索引，后续迁移会触发索引长度问题
DROP TABLE IF EXISTS comment_old;
CREATE TABLE comment_old (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(64) NOT NULL,
  email VARCHAR(255) NOT NULL,
  content VARCHAR(500) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_name_email (user_name, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- 典型失败：4 字节 emoji 无法写入 utf8mb3
INSERT INTO comment_old(user_name, email, content)
VALUES ('小明', 'test@example.com', '反馈一个 emoji：😀');

先检查实例和会话字符集：

sql
-- 查看全局和会话字符集
SHOW VARIABLES WHERE Variable_name IN (
  'character_set_client',
  'character_set_connection',
  'character_set_database',
  'character_set_results',
  'character_set_server'
);

-- 查看建表语句，确认表和列的真实字符集
SHOW CREATE TABLE comment_old;

-- 查询所有仍是 utf8mb3/utf8 的业务表
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'demo_charset'
  AND TABLE_COLLATION LIKE 'utf8%';

连接层需要在应用侧显式声明。例如 JDBC URL 推荐使用：

text
jdbc:mysql://127.0.0.1:3306/demo_charset?useUnicode=true&characterEncoding=utf8mb4&connectionCollation=utf8mb4_0900_ai_ci

如果驱动版本较老，不识别 utf8mb4 这个 Java 编码名，应使用 Java 标准名称 UTF-8，并让 MySQL 服务端协商到 utf8mb4；不要因此继续写 characterEncoding=utf8。命令行临时验证可以执行：

sql
SET NAMES utf8mb4;

索引长度限制：为什么 utf8mb4 会把 255 变成临界点 ​

字符集迁移时最容易忽略索引长度。InnoDB 索引有一个关键历史限制：在较老的 MySQL 版本或未开启 innodb_large_prefix 时，DYNAMIC/COMPRESSED 行格式未生效，单列索引最大约 767 字节；MySQL 5.7.7 之后配合 Barracuda 文件格式、DYNAMIC 行格式和 innodb_large_prefix=ON，最大可提升到 3072 字节。MySQL 8.0 默认参数通常已经满足长索引要求。

字符集从 utf8mb3 变为 utf8mb4 后：

  • VARCHAR(255) 在 utf8mb3 下最多约占 255 × 3 = 765 字节，刚好能放进旧限制；
  • 同样的 VARCHAR(255) 在 utf8mb4 下最多约占 255 × 4 = 1020 字节，超过 767；
  • 联合索引会累加索引列长度，例如 VARCHAR(64) + VARCHAR(255) 在 utf8mb4 下最坏可达 (64 + 255) × 4 = 1276 字节。

因此，下面的迁移在旧参数环境中可能失败：

sql
-- 可能报错：
-- ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
ALTER TABLE comment_old
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

更稳妥的做法是先确认行格式和参数，再处理长索引。

sql
-- MySQL 5.7 需要确认；MySQL 8.0 可查看当前行格式
SHOW VARIABLES WHERE Variable_name IN (
  'innodb_version',
  'innodb_file_format',
  'innodb_file_per_table',
  'innodb_large_prefix',
  'innodb_default_row_format'
);

-- 5.7 推荐配置（写入 my.cnf 后重启）：
-- innodb_file_per_table=1
-- innodb_file_format=Barracuda
-- innodb_large_prefix=1
-- innodb_default_row_format=DYNAMIC

如果短期不能修改全局参数，或者业务并不需要索引完整 255 个字符，可以使用前缀索引：

sql
-- 先删除旧联合索引
ALTER TABLE comment_old DROP KEY idx_name_email;

-- 表迁移到 utf8mb4，并显式使用 DYNAMIC 行格式
ALTER TABLE comment_old
  ROW_FORMAT=DYNAMIC,
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- 对长邮箱使用前缀索引，兼顾唯一性查询与索引长度
ALTER TABLE comment_old
  ADD KEY idx_name_email (user_name, email(100));

但要注意：前缀索引不能直接承担完整唯一约束。若邮箱必须全局唯一，建议新增一个生成列保存哈希值，或在应用层校验完整邮箱：

sql
-- MySQL 8.0：用哈希列实现完整邮箱唯一性
ALTER TABLE comment_old
  ADD COLUMN email_hash BINARY(32) GENERATED ALWAYS AS
    (UNHEX(SHA2(email, 256))) STORED,
  ADD UNIQUE KEY uk_email_hash (email_hash);

迁移完成后执行写入和回读验证：

sql
SET NAMES utf8mb4;

INSERT INTO comment_old(user_name, email, content)
VALUES ('爱😀的小明', 'emoji@example.com', 'utf8mb4 存储测试：🚀🎉');

SELECT id, user_name, content,
       CHAR_LENGTH(content) AS char_len,
       HEX(content) AS content_hex
FROM comment_old
WHERE email = 'emoji@example.com';

CHAR_LENGTH() 返回字符数，LENGTH() 返回字节数。一个 emoji 可能是 4 字节，也可能由多个码点组成；不要用字节长度判断显示长度。

大表迁移注意：DDL 锁等待与主从延迟 ​

小表可以直接 ALTER，但百万级以上表不能只关注字符集是否成功。CONVERT TO CHARACTER SET 会重写字段数据，InnoDB 通常需要重建表。MySQL 5.7 虽然支持 Online DDL，但仍可能在开始和结束阶段持有短暂元数据锁；长事务、未提交 SELECT 都可能让 DDL 排队，进而阻塞后续业务 SQL，形成锁等待。

迁移前建议先查看长事务：

sql
-- 检查长时间运行的事务
SELECT trx_id, trx_started, trx_mysql_thread_id,
       trx_tables_in_use, trx_rows_locked, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

主从架构下，大量数据重建会产生明显 binlog，导致从库回放延迟。不要在业务高峰一次性全库转换。更稳妥的方式是：

  1. 先修改数据库默认字符集，保证新表不再使用旧字符集；
  2. 按表分批迁移，优先处理确实有 emoji 写入的核心表；
  3. 每批迁移后观察复制延迟、慢查询、磁盘空间和连接池错误；
  4. 使用 pt-online-schema-change 或 gh-ost 时，同样要评估触发器负载、唯一键要求和 binlog 量。

修改库默认值的语句如下：

sql
ALTER DATABASE demo_charset
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

注意：修改库默认字符集不会自动转换历史表，历史表仍需逐个执行转换。

避坑总结：至少要检查这 5 个细节 ​

1. 只改表字符集，应用连接仍是 utf8mb3 ​

错误现象：表结构显示 utf8mb4，但 emoji 写入仍报 Incorrect string value，或入库后变成 ?。

修正方法：同时检查驱动连接串、服务端默认字符集和当前会话，用 SHOW VARIABLES LIKE 'character_set%'; 确认 client/connection/results 都是 utf8mb4，并在应用启动后打印一次连接会话变量。

2. 忽略列级字符集，导致 CONVERT 后仍有旧列 ​

错误现象：SHOW CREATE TABLE 中表默认已是 utf8mb4，但个别列仍显示 CHARACTER SET utf8，跨表 JOIN 报 Illegal mix of collations。

修正方法：使用 ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 ... 转换所有字符列，而不是只改表默认值；迁移后查询 information_schema.COLUMNS 核对 CHARACTER_SET_NAME。

3. VARCHAR(255) 习惯性建索引，触发 767 字节限制 ​

错误现象：迁移时报 Specified key was too long; max key length is 767 bytes。

修正方法：确认 MySQL 版本、innodb_large_prefix 和 ROW_FORMAT=DYNAMIC；无法升级时使用合理长度的前缀索引；需要唯一约束时改用哈希列或业务唯一键，不要以为 email(100) 能保证完整邮箱唯一。

4. 排序规则混用，JOIN 和 WHERE 突然报校对规则冲突 ​

错误现象：两个字段都能存中文，但关联查询报 Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_0900_ai_ci,IMPLICIT)。

修正方法：同一实例内统一迁移策略。MySQL 8.0 新项目优先 utf8mb4_0900_ai_ci；跨 5.7/8.0 的系统可选 utf8mb4_unicode_ci。临时 SQL 可用 CONVERT(expr USING utf8mb4) 或 COLLATE，但根因仍应回到表结构统一。

5. 大表高峰直接 DDL，造成锁等待和主从延迟 ​

错误现象：执行 ALTER TABLE 后应用连接池耗尽，慢查询中出现大量 Waiting for table metadata lock，从库延迟持续升高。

修正方法：迁移前清理长事务，在低峰执行；大表使用在线 DDL 工具并限速；每批完成后观察主从延迟。不要把字符集迁移当成纯元数据修改，它在很多场景下会重建整表。

最终建议很明确：新业务直接使用 utf8mb4，并在建表规范中同时规定字符集、排序规则、行格式和索引字段长度；历史系统迁移时，把“能存 emoji”和“索引不超长、DDL 可平稳执行”作为同一个项目验收，才能避免从一个坑跳进另一个坑。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。