架构师

您现在的位置是:首页 > 技术博客 > 数据库

数据库

5分钟改造MySQL使其支持存储emoji等表情符号

架构师小跟班 2020-05-19 数据库
5分钟改造MySQL使其支持存储emoji等表情符号甲方爸爸的新需求:客户端需要在评论中使用emoji函数。mysql5.5以前,UTF-8编码只支持1-3字节;MySQL5.5后,它可以支持4字节的UTF编码ut

5分钟改造MySQL使其支持存储emoji等表情符号

甲方爸爸的新需求:客户端需要在评论中使用emoji函数。

mysql5.5以前,UTF-8编码只支持1-3字节;MySQL5.5后,它可以支持4字节的UTF编码utf8mb4,一个字符可以支持更多的字符集和更多的表情符号。

UTF8MB4与UTF8兼容,可以表示比UTF8更多的字符,是UTF8字符集的超集。所以现在一些新的需求,比如IOS中的emoji,将MySQL数据库的字符集设置为utf8mb4。

问题背景:

Caused by: java.sql.SQLException: Incorrect string value: '\\xF6\\x9D\\x98\\x84' for column 'comment' at row 1

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)

    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)

    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)

    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)

问题分析:

如果我们将列comment设置为varchar(100),用于存储评论信息,现在上线新功能存储emoji表情,插入emoji表情就会报出上述错误,UTF-8编码有可能是两个、三个、四个字节。Emoji表情是4个字节,而Mysql的utf8编码最多3个字节,所以数据插不进去。utf8mb4兼容utf8,且比utf8能表示更多的字符。

解决办法:

将Mysql的编码从utf8转换成utf8mb4。

具体步骤:

1)首先我们修改my.cnf参数

[client]

default-character-set=utf8mb4

[mysql]

default-character-set=utf8mb4

[mysqld]

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

init_connect = 'SET NAMES utf8mb4'

character-set-client-handshake = false

2)对数据库相关的表进行字符集修改

将数据库转换为utf8mb4

mysql> ALTER DATABASE erp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

将已经建好的表也转换成utf8mb4

mysql>ALTER TABLE `erp_comment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

将需要使用emoji的字段设置类型为:

mysql>ALTER TABLE `erp_comment` MODIFY COLUMN `comment`  varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3)重启MySQL服务器

[root@HE3 ~]# /etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS! 

Starting MySQL.... SUCCESS!

4)登录数据库检查:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';

特别注意:collation_connection/collation_database/collation_server如果是utf8mb4_general_ci,没有关系。

但一定要保证:character_set_client/character_set_connection/character_set_database/character_set_results/character_set_server为utf8mb4。

5)让开发人员在pom文件中,去掉characterEncoding参数,并重新编译一下

如果你用的是java服务器,升级或确保你的mysql connector版本高于5.1.13,否则仍然无法使用utf8mb4

然后再让前端应用插入emoji表情,就可以了。

拓展阅读:

character-set-server 和 collation-server 这些设置为utf8mb4字符集是比较容易理解的,只是将MySQL数据库相关的字符集都设置为utf8mb4;

但为了实现客户端utf8连接到MySQL后,使用的也是utf8mb4字符集,就在 mysqld配置中配置了。

init_connect='SET NAMES utf8mb4' 表示初始化连接都设置为utf8mb4字符集,再配置skip-character-set-client-handshake = true 忽略客户端字符集设置,不论客户端是何种字符集,都按照init_connect中的设置进行使用,这样就基本满足了需求。


文章评论