emoji无法存储到MySQL数据库解决方案

背景：

世界之大，什么都有可能发生，尤其是写代码这种事。正在写公司的小程序项目，想让同事用微信登录后测一下修改的功能，哪晓得修复的bug还没测呢，新的BUG的就被同事测出来了，我当时恨不得吃了她。问题是这样的，同事登录小程序授权登录小程序报错，是因为同事修改了她的微信昵称，是带有emoji表情的昵称，该死的昵称入不了库~于是，我决定先解决这个BUG吧，哈哈哈~

---

问题原因分析：

utf-8编码可能是2个字节，3个字节，4个字节，但是MySQL的utf8编码其实并不是真的utf8编码，MySQL的utf8编码只支持3字节的数据，而移动端的表情数据是4个字节的字符。如果直接采用utf-8编码的数据库中插入表情数据，Java程序中就会报SQL异常，比如：

java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\x94’ for column ‘name’ at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)

---

解决方案：

将数据库的编码，表的编码，字段的编码改成utf8mb4编码。utf8mb4编码是utf8的超集合，兼容utf8。

1、MySQL的版本

utf8mb4 的最低 MySQL 版本支持版本为 5.5.3+，若不是，请升级到较新版本。

2、MySQL驱动

5.1.34 可用,最低不能低于 5.1.13。

3、MySQL配置文件修改

my.cnf一般在etc/mysql/my.cnf位置。找到后请在以下三部分里添加如下内容： 
[client] 
default-character-set = utf8mb4 
[mysql] 
default-character-set = utf8mb4 
[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='SET NAMES utf8mb4'

我的文件如下：

[mysqld]

port=3306
# 设置mysql的安装目录，如果目录中带空格则必须加引号
basedir=D:\devlope\mysql5.6.22\mysql

# 设置mysql数据库的数据的存放目录，文件夹名必须是data
datadir=D:\devlope\mysql5.6.22\mysql\data

#设置数据库引擎为INNODB
default-storage-engine=INNODB


# 设置mysql服务器编码
#character_set_server=utf8
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

# 设置mysql服务器字符集
#collation-server=utf8_general_ci


[client]
#default-character-set = utf8
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4

[WinMySQLadmin]
Server=DD:\devlope\mysql5.6.22\mysql\bin\mysqladmin.exe

 

4、重启数据库，检查变量

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

但是按照预期的结果应该是下面这样的，

但是必须保证一下几项是utf8mb4.

（*）这里说明一下，虽然我的数据库编码检测有出入，但是亲测后是有效的，能够插入emoji，具体的原因有待考证，希望广大读者各抒己见。

 

更改数据库编码：

ALTER DATABASE 你的数据库名 CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`;

更改表的编码：

ALTER TABLE `TABLE_NAME` CONVERT TO CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`; 

更改表对应字段的编码：

ALTER TABLE `TABLE_NAME`MODIFY COLUMN `COLUMN_NAME`  varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

 

 

5、数据库连接配置

jdbc.url=jdbc:mysql://127.0.0.1:3306/cxbl?useUnicode=true&autoReconnect=true&rewriteBatchedStatements=TRUE

其中，autoReconnect=true 是必须加上的。

数据源连接池的xml配置文件中，需要加上字符集的设置：

<bean parent="parentDataSource" id="DataSource88">
  <property name="connectionInitSqls">
	<list>
		<value>set names utf8mb4</value>
	</list>
  </property>
</bean>

 

这样数据库中就能正常添加带emoji的昵称了，库中的显示是用?：

 

---

说明：

从 MySQL 5.5.3 开始，MySQL 支持一种 utf8mb4 的字符集，这个字符集能够支持 4 字节的 UTF8 编码的字符。 utf8mb4 字符集能够完美地向下兼容 utf8 字符串。在数据存储方面，当一个普通中文字符存入数据库时仍然占用 3 个字节，在存入一个 Unified Emoji 表情的时候，它会自动占用 4 个字节。所以在输入输出时都不会存在乱码的问题了。

要使用 MySQL 的这个特性，首先需要把 MySQL 升级到 5.5.3 以上的版本。

其次，需要修改数据结构中的字符集为 utf8mb4 ，如 utf8mb4_general_ci 。由于 utf8mb4 是 utf8 的超集，从 utf8 升级到 utf8mb4 不会有任何问题，直接升级即可；如果从别的字符集如 gb2312 或者 gbk 转化而来，一定要先备份数据库。

然后，修改 MySQL 的配置文件 /etc/my.cnf，修改连接默认字符集为 utf8mb4 ，如果是自己写的 PHP 脚本，也可以在连接数据库以后首先执行一句 SQL: SET NAMES utf8mb4;。这时候，PHP 应该就可以正常保存 Emoji 到数据库了。

这种方式可能带来的问题：

存储：在数据表中，对于变长的字段（如VARCHAR2，TEXT），utf8mb4最大可存储的字符可能少于utf8系列的collation；在索引中，对于文本类型的字段，utf8mb4可索引的字符少于utf8系列的collations。如InnoDB的索引最多使用767字节。如果使用utf8mb4，每一个字符都会预留4字节做索引，而utf8则预留3字节。故此前者是191个字符，后者是255个字符。。

性能：由于以上原因，加上字符集大，utf8mb4的性能可能比utf8系列的collations低，可以参考stackoverfolow上的一个测试结果：http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci，差异不是特别大。

运维：如果一个大的环境内，如果其他的数据库都是utf8模式，把其中某个库设置为utf8mb4模式，在后续交接运维可能会造成问题，遗留下坑。

上下游：数据库支持unicode的emoji存储，上下游不一定支持。比如mysql客户端驱动（低版本的jdbc就不行）可能不支持utf8mb4，或者DDL的中间件不支持utf8mb4。web端处理utf8mb4字符展示，这些都有可能影响emoji的存储活着展示。

从上面的信息，从数据库层面如果不是特别看重存储，性能，运维并能解决上下游的问题，数据库是完全可以支持emoji的，但是有个新问题没有解决，emoji在iOS上展示OK，andriod设备如何展示emoji表情？

---

参考：

https://www.cnblogs.com/xujanus/p/5189358.html

https://www.jianshu.com/p/f7d7609de6b0

个人能力有限，如果本文存在错误，希望大家批评指正。