跳至主要內容

程序员小富大约 4 分钟

大家好，我是小富。

《十万个why》系列持续更新中

用户在评论区发了个 😂，你的系统直接炸了：

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x82' for column 'content'

数据库编码明明设的 UTF-8 啊，UTF-8 不是全球通用的编码吗，中文日文韩文都能存，为什么一个 emoji 就不行了？

因为 MySQL 的 utf8 不是真正的 UTF-8。

MySQL 撒了一个持续了二十多年的谎

真正的 UTF-8 编码规范是这样的：

Unicode 范围UTF-8 字节数包含字符
U+0000 ~ U+007F1 字节ASCII（英文、数字）
U+0080 ~ U+07FF2 字节拉丁文、希腊文等
U+0800 ~ U+FFFF3 字节中日韩常用汉字、大部分字符
U+10000 ~ U+10FFFF4 字节emoji、生僻汉字、古文字、音乐符号等

标准的 UTF-8 最多用 4 个字节编码一个字符。但 MySQL 当年实现 utf8 字符集的时候，只实现了最多 3 个字节。

也就是说，MySQL 的 utf8 实际上是 utf8mb3——一个阉割版的 UTF-8，最多只支持到 3 字节。

3 字节能覆盖 Unicode 的基本多文本平面（BMP，U+0000 ~ U+FFFF），中文、日文、英文都在这个范围内，所以平时用着没问题。但 emoji 表情（😂 的码点是 U+1F602）、部分生僻汉字（𠮷、𨐈）以及很多新增的 Unicode 字符，都在补充平面（U+10000 以上），需要 4 字节编码。

MySQL 的 utf8 存不了 4 字节字符，所以 emoji 存进去就报错。

为什么 MySQL 要这么做？

MySQL 的 utf8 诞生于 2003 年左右。那个年代 Unicode 委员会还在坚信"65536 个字符够全世界用了"（BMP 的范围），补充平面的使用非常稀少。MySQL 的开发者可能觉得 3 字节够用了，还能省存储空间（CHAR 类型按最大字节数分配，3 字节比 4 字节省 25%）。

但后来 emoji 爆发了。2010 年 Unicode 6.0 正式收录 emoji，之后智能手机普及，emoji 成了全球互联网的通用语言。MySQL 的 utf8 一下子从"几乎够用"变成了"隔三差五报错"。

MySQL 官方的处理方式也很"经典"——他们没有修复 utf8，而是另起炉灶搞了一个 utf8mb4。

为什么不直接修复？因为向后兼容。如果把 utf8 改成真正的 4 字节 UTF-8，所有用 utf8 的 CHAR 列的存储空间会从 3×N 字节膨胀到 4×N 字节，已有的表结构和数据可能出问题。

utf8mb4 才是真正的 UTF-8

从 MySQL 5.5.3（2010 年）开始，MySQL 引入了 utf8mb4 字符集，这才是标准的、支持 1~4 字节的 UTF-8 编码。

utf8    = utf8mb3 = 最多 3 字节 = 假的 UTF-8
utf8mb4 = 最多 4 字节 = 真正的 UTF-8

从 MySQL 8.0.28 开始，官方文档已经明确建议用 utf8mb4 替代 utf8，并且 utf8mb3 在未来版本中可能被废弃。MySQL 8.0 的默认字符集也从 latin1 改成了 utf8mb4。

怎么修复

建库时直接用 utf8mb4：

CREATE DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

已有表的迁移：

-- 修改表的默认字符集
ALTER TABLE comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 如果只改某一列
ALTER TABLE comments MODIFY content VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

连接层也要配：

# Spring Boot application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8&connectionCollation=utf8mb4_unicode_ci

注意 JDBC 连接串里的 characterEncoding=UTF-8 在 MySQL Connector/J 8.0 以上已经默认对应 utf8mb4，不需要额外处理。但如果你用的是旧版驱动（5.1.x），需要在连接后手动执行 SET NAMES utf8mb4。

一个容易忽略的坑：索引长度限制

utf8 下，一个字符最多 3 字节。utf8mb4 下，一个字符最多 4 字节。

MySQL InnoDB 默认的索引前缀最大长度是 767 字节（MySQL 5.6 及以前）或 3072 字节（MySQL 5.7+ 开启 innodb_large_prefix）。

在 767 字节限制下：

  • utf8 的 VARCHAR 最多索引 767/3 = 255 个字符
  • utf8mb4 的 VARCHAR 最多索引 767/4 = 191 个字符

如果你有一个 VARCHAR(255) 的列并且建了索引，从 utf8 改成 utf8mb4 后，索引会报错：

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

解决办法：缩短 VARCHAR 长度到 191，或者用前缀索引，或者升级到 MySQL 5.7+ 并开启 innodb_large_prefix。

不只是 emoji，这些字符也存不了

很多人以为只有 emoji 存不了，其实所有 4 字节的 Unicode 字符都存不了：

  • emoji：😂 🎉 👍 ❤️ 等（几乎所有 emoji）
  • 生僻汉字：𠮷（吉的异体字）、𨐈、𪚥 等（CJK 统一汉字扩展 B 区及以后）
  • 古文字：甲骨文、楔形文字、埃及象形文字
  • 音乐符号：𝄞（高音谱号）等
  • 数学符号：一些高级数学符号

如果你的系统会存用户输入的文本（评论、昵称、聊天记录），用 utf8 迟早翻车。

总结

对比MySQL utf8MySQL utf8mb4
实际含义utf8mb3（阉割版）真正的 UTF-8
最大字节数34
能存 emoji不能能
能存生僻字不能能
MySQL 8.0 默认不是是

一条建议：所有新项目，无脑用 utf8mb4。 老项目如果还在用 utf8，找个时间迁移掉，别等到用户发了个 emoji 把你系统搞崩了才想起来。

MySQL 的 utf8 可能是数据库历史上最成功的"命名欺诈"——叫着 UTF-8 的名字，干的却不是 UTF-8 的活。

我是小富，下期见。

上次编辑于: