编码是个大问题

问题描述

1
2
3
4
5
6
7
8
9
create table test_2
(
  id      int(9) auto_increment
    primary key,
  content varchar(10),
  mb4     varchar(10)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

执行 insert into test (content, mb4) values ('花心', '😍'); 出现错误 Incorrect string value: '\xF0\x9F\x98\x8D' for column 'mb4' at row 1。

问题分析

因为标准的 utf8 字符编码是可以用 1~4 个字节去编码的 21 位字符，这几乎涵盖了能见到的所有字符。然而在 MySQL 的实现中，utf8 即 utf8mb3 最长只能使用 3 个字节，所以只能支持 Unicode 中的大部分字符（U+0000至U+FFFF），但是像一些常见的 emoji （4个字节）就不能存储了，这时候就需要修改字符集为 utf8mb4（注意：utf8mb4 在 MySQL 5.5.3 之后引入）。utf8mb4 是 utf8 的超集，对 utf8 兼容。

问题解决（utf8 转换为 utf8mb4）

1
2
-- 修改某个字段的编码集
alter table test_2 modify column mb4 varchar(10) character set utf8mb4 collate utf8mb4_unicode_ci;

除此之外，还要修改 MySQL 服务器和客户端的连接编码集合。

  1. character_set_client 客户端发送过来的语句和编码
  2. character_set_connection mysqld 收到客户端的语句后，要转换到的编码
  3. character_set_results 指 server 执行语句后，返回给客户端的数据编码

可以使用 SET NAMES 'charset_name' [COLLATE 'collation_name'] 代替以下三条语句：

1
2
3
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

所以可以写成 set names utf8mb4 collate utf8mb4_unicode_ci。

问题总结

当字段编码从 utf8mb3 转换到 utf8mb4，只是正常的增删改查并不意味着结束。因为单个字符的字节长度发生变化，需要关注这个变化所带来的影响。

  1. Key 768 long 错误

对于表行格式是 COMPACT或 REDUNDANT，InnoDB 的索引长度是单个索引的最大字节数，为768。而字段定义的长度是能存储的最大字符数，如 varchar(200) 表示最多能存储 200 个字符。如果字符是 utf8nb3 编码，最大是 600 个字节数，而切换为 utf8mb4，则最大为 800 个字节数，可能导致索引长度溢出。