MySQL修改字符集编码为utf8mb4支持emoji存储

环境

  • MySQL 5.5.3+
  • JDBC驱动5.1.14+

关于utf8mb4编码

MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8bp4外不需要做其他转换。

三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff,也就是 Unicode 中的基本多文种平面(BMP)。也就是说,任何不在基本多文本平面的 Unicode字符,都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码,常见于 ios 和 android 手机上),和很多不常用的汉字,以及任何新增的 Unicode 字符等等。

由于utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了:

1
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F...' for column 'CONTENT' at row 1

对应的解决方案就是修改字符编码为utf8mb4.


划重点


修改数据库表结构(必须)

1
2
3
4
5
6
--修改数据库字符集
ALTER DATABASE test CHARACTER SET = utf8mb4;
--修改表字符集
alter table test convert to character set utf8mb4;
--修改字符字符集
ALTER TABLE `test` CHANGE COLUMN `name` `name` varchar(12) CHARACTER SET utf8mb4;

修改Mysql配置文件my.ini(必须)

根据版本不同,可能并非所有配置项都存在,只需要修改存在的即可。修改后重启MySQL

1
2
3
4
5
6
7
[client]
default-character-set = utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4

如果你使用的是java语言,需要将jdbc驱动包升级到 mysql-connector-java-5.1.14.jar。


全剧终。


番外篇

关于异常Specified key was too long

1
alter table m_reply convert to character set utf8mb4 Error Code: 1071. Specified key was too long; max key length is 767 bytes

在修改表字符集时,可能会遇到上面的异常,解决方式就是,把长度大于768/4=192的索引改小。

报错原因在于:MySQL Innodb 的索引长度限制为 767 字节,UTF8mb4 字符集是 4 个字节,
767 字节 / 4 字节每字符 = 191 字符(即默认的索引最大长度)
因此在 varchar(255) 类型字段上,创建索引会失败,提示最大索引长度为767字节。具体请google。

PS:在5.7版本未出现上述问题。

utf8mb4_unicode_ci 还是 utf8mb4_general_ci

在性能方面考虑,utf8mb4_unicode_ci 。具体请google。

参考链接