学完这篇你能把 MySQL 从库到连接层彻底切到 utf8mb4,并且知道哪些地方会在半夜咬你一口。
第一步:先认清 utf8 是个假的 utf8
MySQL 里的 utf8 其实只是 utf8mb3,最多 3 字节,而 emoji、部分生僻字、数学符号是 4 字节。所以你在 utf8 的库上,用户发个 😂 就会得到 Incorrect string value: '\xF0\x9F...' 或者存进去变成 ???。
规则很简单:MySQL 5.7 及以上、要支持 emoji,一律用 utf8mb4。像论坛这种有表情包、有用户昵称的地方,根本没有讨论余地。
第二步:查清现状,别靠猜
先登录 MySQL 看四层配置:
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
重点看三个:character_set_server(服务端默认)、character_set_database(当前库)、character_set_client/connection/results(连接层)。
再看具体表:
SHOW CREATE TABLE posts;
-- 或者批量找出还是 utf8mb3 的表
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_COLLATION NOT LIKE 'utf8mb4%';
注意:SHOW CREATE TABLE 里如果只写了 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4,不代表所有列都是 utf8mb4——历史遗留表经常是「表默认 utf8mb4、某个 varchar 列还是 utf8」。要逐列查:SELECT COLUMN_NAME, CHARACTER_SET_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='your_db' AND TABLE_NAME='posts';
第三步:改库和表
改库默认字符集(只影响以后新建的表):
ALTER DATABASE your_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
改已有表 + 已有列,必须用 CONVERT TO:
ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
注意:CONVERT TO 会重建整张表,百万级帖子表会锁很久。要么挑凌晨低峰操作,要么用 pt-online-schema-change/gh-ost 这类在线工具。改之前先 mysqldump 一份,别省这一步。
注意:只执行 ALTER TABLE ... DEFAULT CHARSET=utf8mb4 是不会转换已有列的,很多人踩在这里——改完以为好了,结果老列还是存不了 emoji。
第四步:连接层也要改,否则白干
服务端改了,PHP 连上来还是拿 utf8 说话,照样报错。三处都要动:
my.cnf:
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[client]
default-character-set = utf8mb4
改完重启 MySQL。
- PDO 连接串里显式声明(推荐,最不容易漏):
$dsn = 'mysql:host=127.0.0.1;dbname=your_db;charset=utf8mb4';
- 老式 mysqli 记得
$mysqli->set_charset('utf8mb4');,别用 SET NAMES 拼 SQL 字符串。
第五步:索引长度这个老坑
utf8mb4 每字符 4 字节,varchar(255) 建索引就是 1020 字节,超过 InnoDB 老格式 767 字节上限,会报:
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
解法两条:把 MySQL 5.7+ 的行格式设为 DYNAMIC(默认就是),或者把索引前缀降到 191:
ALTER TABLE users ADD UNIQUE KEY uk_name (username(191));
注意:MySQL 5.6 及更早默认 COMPACT 行格式 + innodb_large_prefix=OFF,直接建 varchar(255) 唯一索引必挂。升级到 5.7/8.0 能省掉一半麻烦。
第六步:排序规则别混用
常见报错:Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='——两张表排序规则不一致,JOIN 或比较就炸。
选一个全站统一,推荐 utf8mb4_unicode_ci(排序更准,支持多语言);MySQL 8.0 可以用 utf8mb4_0900_ai_ci(更快,但和 5.7 不兼容,别在混合环境用)。
注意:_ci 是不区分大小写,这意味着唯一索引里 Admin 和 admin 会冲突。如果你要用户名严格区分大小写,得用 utf8mb4_bin 或者业务层自己校验。这个坑通常在上线后用户注册时才发现。
第七步:插一条 emoji 验证
INSERT INTO test (content) VALUES ('测试😂👍');
SELECT content, CHAR_LENGTH(content), LENGTH(content) FROM test;
CHAR_LENGTH 是字符数,LENGTH 是字节数,两者不等说明 4 字节字符存进去了。如果显示 ???,说明连接层没改;如果报 Incorrect string value,说明列还是 utf8mb3。
顺便提醒:前端表单的 maxlength 和后端校验都按字符算,但数据库字段长度也按字符算,两边要对齐,否则用户填满长度再带 emoji 就超了。
小结
- MySQL 的
utf8 是 3 字节的 utf8mb3,要 emoji 就必须 utf8mb4
- 改表必须用
CONVERT TO,只改 DEFAULT CHARSET 不动已有列
- 服务端、库、表、列、连接串五处都要改,漏一处就报错
- 大表转换会锁表,先备份、挑低峰、必要时用在线 DDL 工具
- 索引前缀 191 / 行格式 DYNAMIC,是老版本绕不开的坎
- 排序规则全站统一,
_ci 下唯一索引不区分大小写
- 用
CHAR_LENGTH vs LENGTH 做最终验证