MySQL关于表情存储的深入理解

1. utf8 与 utf8mb4 异同

先看 官方手册

the character set named utf8 uses a maximum of three bytes per character and contains only bmp characters. the utf8mb4 character set uses a maximum of four bytes per character supports supplementary characters:

- for a bmp character, utf8 and utf8mb4 have identical storage characteristics: same code values, same encoding, same length.
- for a supplementary character, utf8 cannot store the character at all, whereas utf8mb4 requires four bytes to store it. because utf8 cannot store the character at all, you have no supplementary characters in utf8 columns and need not worry about converting characters or losing data when upgrading utf8 data from older versions of mysql.

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和一些不常用的汉字，如 “墅” ，这些需要四个字节才能编码出来。

注：qq里面的内置的表情不算，它是通过特殊映射到的一个gif图片。一般输入法自带的就是。

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

提示：另外一种能够存储emoji的方式是，不关心数据库表字符集，只要连接字符集使用 latin1，但相信我，你绝对不想这个干，一是这种字符集混用管理极不规范，二是存储空间被放大

2. utf8mb4_unicode_ci 与 utf8mb4_general_ci 如何选择

字符除了需要存储，还需要排序或比较大小，涉及到与编码字符集对应的 排序字符集（collation）。ut8mb4对应的排序字符集常用的有 utf8mb4_unicode_ci、utf8mb4_general_ci，到底采用哪个在 * 上有个讨论，what’s the difference between utf8_general_ci and utf8_unicode_ci

主要从排序准确性和性能两方面看：

准确性

utf8mb4_unicode_ci 是基于标准的unicode来排序和比较，能够在各种语言之间精确排序

utf8mb4_general_ci 没有实现unicode排序规则，在遇到某些特殊语言或字符是，排序结果可能不是所期望的。

但是在绝大多数情况下，这种特殊字符的顺序一定要那么精确吗。比如unicode把、当成ss和oe来看；而general会把它们当成s、e，再如àáā各自都与 a 相等。

性能

utf8mb4_general_ci 在比较和排序的时候更快

utf8mb4_unicode_ci 在特殊情况下，unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

但是在绝大多数情况下，不会发生此类复杂比较。general理论上比unicode可能快些，但相比现在的cpu来说，它远远不足以成为考虑性能的因素，索引涉及、sql设计才是。 我个人推荐是 utf8mb4_unicode_ci，将来 8.0 里也极有可能使用变为默认的规则。相比选择哪一种collation，使用者应该更关心字符集与排序规则在db里要统一就好。

这也从另一个角度告诉我们，不要可能产生乱码的字段作为主键或唯一索引。我遇到过一例，以 url 来作为唯一索引，但是它记录的有可能是乱码，导致后来想把它们修复就特别麻烦。

3.java驱动使用

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

但还没完，遇到一个大坑。官方手册 里还有这么一段话：

connector/j did not support utf8mb4 for servers 5.5.2 and newer.

connector/j now auto-detects servers configured with character_set_server=utf8mb4 or treats the java encoding utf-8 passed
  using characterencoding=... as utf8mb4 in the set names= calls it makes when establishing the connection. (bug #54175)

意思是，java驱动会自动检测服务端 character_set_server 的配置，如果为utf8mb4，驱动在建立连接的时候设置 set names utf8mb4。然而其他语言没有依赖于这样的特性

4. 主从复制报错

这个问题没有遇到，只是看官方文档有提到，曾经也看到过类似的技术文章。

大概就是从库的版本比主库的版本低，导致有些字符集不支持；或者人工修改了从库上的表或字段的字符集定义，都有可能引起异常。

5.问题解决

确定mysql版本高于5.5.3

修改mysql character-set-server 配置项为utf8mb4,需要重启mysql服务

修改需要兼容表情的字段的编码集为utf8mb4

一般来说修改mysql character-set-server之后，修改表字段编码为utf8mb4就可以

如果还不可以在连接mysql时执行set names utf8mb4，使整个链接使用utf8mb4

6.注意点

mysql中的utf8和java的utf8并不是一个概念

java的utf8本身就支持四个字节，mysql的只支持三个字节，相当于utf8mb3，所以尝试修改jdbc url属性characterencoding为utf8mb4是行不通的

会抛出异常，如下

 unsupported character encoding 'utf8mb4'.