MySQL 字符集迁移到 utf8mb4：索引长度超限（ERROR 1071）的根因与修复

快速答案

• 核心结论：将 MySQL 从旧版 utf8（utf8mb3）迁移到完整 utf8mb4 时，VARCHAR(255) 索引列会因 4 字节字符集导致索引长度超限（255×4=1020 > 767 字节），报错 ERROR 1071 (42000): Specified key was too long。
• 第一检查：确认 MySQL 版本 ≥5.5.3（utf8mb4 支持），以及 InnoDB 行格式是否为 DYNAMIC 或 COMPRESSED（MySQL 5.7+ 默认启用 innodb_large_prefix 但需对应行格式）。
• 最小修复命令：将超限的 VARCHAR 列缩小到 191（191×4=764 < 767），或启用 innodb_large_prefix 并设置 ROW_FORMAT=DYNAMIC。
• 适用环境：MySQL 5.5.3+，尤其 5.7+ 生产环境；配合 Django、Ruby on Rails、Python 等 ORM 框架使用 utf8mb4 时必读。

问题复现：索引长度超限的根因

当执行 ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 时，MySQL 会尝试重建索引。在 InnoDB 默认行格式下，索引键最大长度为 767 字节。对于 VARCHAR(255) 列：

• 旧版 utf8（utf8mb3）：255 × 3 = 765 字节 < 767 ✅
• utf8mb4：255 × 4 = 1020 字节 > 767 ❌

因此 MySQL 抛出：

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

两种修复方案对比

方案一：缩小列长度（通用方案）

SQL
-- 1. 找出所有索引列
SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'yourdb';

-- 2. 对每个 VARCHAR(255) 索引列执行缩小
ALTER TABLE tablename MODIFY columnname VARCHAR(191) CHARACTER SET utf8mb4;

-- 3. 转换表字符集
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注意：VARCHAR(191) 是安全值（191×4=764 < 767）。如果应用层有长度校验（如 Django 的 max_length=255），需同步修改。

方案二：启用大前缀（MySQL 5.7+ 推荐）

SQL
-- 检查当前行格式和 innodb_large_prefix 状态
SHOW VARIABLES LIKE 'innodb_large_prefix';
SHOW TABLE STATUS WHERE Name = 'tablename';

-- 修改表行格式为 DYNAMIC
ALTER TABLE tablename ROW_FORMAT=DYNAMIC;

-- 然后转换字符集（无需缩小列）
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

MySQL 5.7+ 默认 innodb_large_prefix=ON，但要求 ROW_FORMAT=DYNAMIC 或 COMPRESSED。如果表是 ROW_FORMAT=COMPACT，即使 innodb_large_prefix=ON 也会报错。

生产环境迁移完整步骤

1. 前置检查

SQL
-- 检查 MySQL 版本
SELECT VERSION();

-- 检查当前字符集设置
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

-- 检查所有表的字符集
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema');

2. 配置 my.cnf（永久生效）

INI
[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_large_prefix=ON
innodb_file_format=Barracuda

关键注意：[mysqld] 段下不能使用 default-character-set，必须用 character-set-server。否则 MySQL 5.7+ 启动会失败。

3. 使用 pt-online-schema-change 避免锁表

对于大型生产表，直接 ALTER TABLE 会锁表。使用 Percona Toolkit：

BASH
pt-online-schema-change h=localhost,D=mydb,t=bigtable \
  --alter "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" \
  --execute

4. 验证迁移结果

SQL
-- 检查表字符集
SHOW CREATE TABLE tablename;

-- 检查索引长度
SHOW INDEX FROM tablename;

-- 测试写入 4 字节字符（emoji）
INSERT INTO tablename (columnname) VALUES ('测试emoji 😊');

常见报错与排查

ERROR 1071: Specified key was too long

根因：索引键长度超过 767 字节（InnoDB 默认限制）。

解决：

SQL
-- 方案 A：缩小列
ALTER TABLE tablename MODIFY columnname VARCHAR(191) CHARACTER SET utf8mb4;

-- 方案 B：启用大前缀（MySQL 5.7+）
ALTER TABLE tablename ROW_FORMAT=DYNAMIC;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

MySQL 启动失败：default-character-set under [mysqld]

报错：MySQL 服务无法启动，错误日志显示 unknown variable 'default-character-set=utf8mb4'。

解决：移除 [mysqld] 下的 default-character-set，改用：

INI
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

Incorrect string value: '\xF0\x9F...' for column

根因：列或连接字符集不是 utf8mb4，无法存储 4 字节字符（如 emoji）。

解决：

SQL
-- 检查列字符集
SHOW FULL COLUMNS FROM tablename;

-- 修改列字符集
ALTER TABLE tablename MODIFY columnname VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 设置连接字符集
SET NAMES utf8mb4;

Character set 'utf8mb4' is not a compiled character set

根因：MySQL 版本 < 5.5.3，不支持 utf8mb4。

解决：升级 MySQL 到 5.5.3+，或使用 utf8（但无法存储 emoji）。

常见问题 FAQ

Q: 为什么新创建的表仍然是 latin1，即使配置文件中设置了 utf8mb4？

A: 可能原因：

1. 配置文件未生效：检查 my.cnf 位置（mysql --help | grep 'Default options'）。
2. 连接时客户端覆盖：在连接字符串中指定 charset=utf8mb4。
3. 显式指定了 DEFAULT CHARSET=latin1。
4. 数据库级别默认字符集未设置：执行 ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Q: 迁移后性能会下降多少？

A: utf8mb4 比 utf8 多占用约 33% 存储空间，索引更大，写入和查询性能可能下降 10-20%。建议：

• 只对需要存储 4 字节字符的列使用 utf8mb4。
• 使用前缀索引（INDEX(col(100))）减少索引大小。
• 监控慢查询，必要时调整 innodb_buffer_pool_size。

Q: 如何安全地在生产环境回滚？

A: 迁移前备份：

BASH
mysqldump --default-character-set=utf8mb4 --single-transaction --quick mydb > mydb_backup.sql

回滚时恢复备份，或执行反向转换：

SQL
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

注意：如果已写入 4 字节字符，回滚到 utf8 会导致数据丢失。