mysql使用utf8mb4经验总结

mysql

赵亮
weston_contribute@163.com

1. utf8 与 utf8mb4 异同

MySQL在 5.5.3 之后增加了 utf8mb4 字符编码，mb4即 most bytes 4。简单说 utf8mb4 是 utf8 的超集并完全兼容utf8，能够用四个字节存储更多的字符。

抛开数据库，标准的 UTF-8 字符集编码是可以用 1~4 个字节去编码21位字符，这几乎包含了是世界上所有能看见的语言了。然而在MySQL里实现的utf8最长使用3个字节，也就是只支持到了 Unicode 中的 基本多文本平面 （U+0000至U+FFFF），包含了控制符、拉丁文，中、日、韩等绝大多数国际字符，但并不是所有，最常见的就算现在手机端常用的表情字符 emoji和一些不常用的汉字，如 “墅” ，这些需要四个字节才能编码出来。

也就是当你的数据库里要求能够存入这些表情或宽字符时，可以把字段定义为 utf8mb4，同时要注意连接字符集也要设置为utf8mb4，否则在 严格模式 下会出现 Incorrect string value: /xF0/xA1/x8B/xBE/xE5/xA2… for column ‘name‘ 这样的错误，非严格模式下此后的数据会被截断。

2. 怎么从utf8转换为utf8mb4

2.1 转换方法

如果你的表定义和连接字符集都是utf8，那么直接在你的表上执行

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4;

则能够该表上所有的列的character类型变成 utf8mb4，表定义的默认字符集也会修改。连接的时候需要使用 set names utf8mb4 便可以插入四字节字符。（如果依然使用 utf8 连接，只要不出现四字节字符则完全没问题）。

2.2 转换带来的问题

1. 是它不能ONLINE，也就是执行之后全表禁止修改，有关这方面的讨论见 mysql 5.6 原生Online DDL解析 ；

2. 它可能会自动该表字段类型定义，如 VARCHAR 被转成 MEDIUMTEXT ，可以通过 MODIFY 指定类型为原类型。

3. ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8mb4 这样的语句就不要随便执行了，特别是当表原本不是utf8时，除非表是空的或者你确认表里只有拉丁字符，否则正常和乱的就混在一起了。

4. 你连接时使用的latin1字符集写入了历史数据，表定义是latin1或utf8，不要期望通过 ALTER ... CONVERT ... 能够让你达到用utf8读取历史中文数据的目的，没卵用，老老实实做逻辑dump。所以我才叫它“伪”转换

3. key 768 long 错误

字符集从utf8转到utf8mb4之后，最容易引起的就是索引键超长的问题。

3.1 出现错误原因

对于表行格式是 COMPACT 或 REDUNDANT ，InnoDB有单个索引最大字节数 768 的限制，而字段定义的是能存储的字符数，比如 VARCHAR(200) 代表能够存200个汉字，索引定义是字符集类型最大长度算的，即 utf8 maxbytes=3, utf8mb4 maxbytes=4，算下来utf8和utf8mb4两种情况的索引长度分别为600 bytes和800bytes，后者超过了768，导致出错： Error 1071: Specified key was too long; max key length is 767 bytes。COMPRESSED 和 DYNAMIC格式不受限制，但也依然不建议索引太长，太浪费空间和cpu搜索资源。

如果已有定义超过这个长度的，可加上前缀索引，如果暂不能加上前缀索引（像唯一索引），可把该字段的字符集改回utf8或latin1。

注意，要防止出现 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation ‘=‘ 错误：连接字符集使用utf8mb4，但 SELECT/UPDATE where条件有utf8类型的列，且条件右边存在不属于utf8字符，就会触发该异常。表示踩过这个坑。

提示：EXPLAIN 结果里面的 key_len 指的搜索索引长度，单位是bytes，而且是以字符集支持的单字符最大字节数算的，这也是为什么 INDEX_LENGTH 膨胀厉害的一个原因。

3.2 解决方案

3.2.1 让MySQL支持更长的索引。

1. 启用系统变量innodb_large_prefix
2. 系统变量innodb_large_prefix为ON
3. 系统变量innodb_file_format为Barracuda
4. ROW_FORMAT为DYNAMIC或COMPRESSED

3.2.2 将超出范围的字段建立前置索引

1. 关于MySQL的前置索引

3.2.3 缩短超出范围的字段的长度

alter table `t_customer` modify column `nick` varchar(100) DEFAULT NULL COMMENT '客户昵称'

我们超出的索引长度大部分都是因为account，name和nick字段出现的问题导致的，而我们这些数据的输入都是来源于认证中心。我们可以看一下认证中心中用户详情表的字段定义，其中account,nick的字符长度为100，所以CRM系统的数据不会超过这个值。修改字段长度并不会影响现有的索引。

我在灰度环境中做过测试，执行一下数据：

alter table `t_customer` modify column `nick` varchar(100) DEFAULT NULL COMMENT '客户昵称'

预计要花76秒。
完成这项工作的checklist：

• 检查所有的创建了索引的字段长度是否超过（ 767 / 4 ）字节。
• 研究超过了的字节是否可以缩减至（767 / 4）字节以内。
• 编写所有需要修改的字段的SQL。

预计完成时间需要1天。

我建议用第三种方式

4. java驱动使用

Java语言里面所实现的UTF-8编码就是支持4字节的，所以不需要配置 mb4 这样的字眼，但如果从MySQL读写emoji，MySQL驱动版本要在 5.1.13 及以上版本，数据库连接依然是 characterEncoding=UTF-8 。