MySQL utf8mb4与emoji表情

MySQL · suifu · 于 11个月前发布 · 581 次阅读
公司有新要求,ios客户端要上线评论中可以使用emoji表情的功能,在mysql 5.5 之前,UTF-8编码只支持1-3个字节;从MySQL 5.5开始,可以支持4个字节UTF编码 utf8mb4 ,一个字符能够支持更多的字符集,也能够支持更多表情符号。

utf8mb4兼容utf8,且比utf8能表示更多的字符,是utf8字符集的超集。所以现在一些新的业务,比如ISO中的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。


网上的文章各执一词,本文就生产环境中真实可用的参数进行记录

整体操作流程其实并不难


一:首先我们修改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
 

二:对数据库相关的表进行字符集修改
将数据库转换为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;

三:重启数据库服务器使之生效

[root@HE3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.... SUCCESS!
四:登录数据库检查是否如下:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client    | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database  | utf8mb4            |
| character_set_filesystem | binary            |
| character_set_results    | utf8mb4            |
| character_set_server    | utf8mb4            |
| character_set_system    | utf8              |
| collation_connection    | utf8mb4_unicode_ci |
| collation_database      | utf8mb4_unicode_ci |
| collation_server        | utf8mb4_unicode_ci |
+--------------------------+--------------------+
rows in set (0.00 sec)
特别说明下: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。



五:让开发那边的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中的设置进行使用,这样就满足了应用的需求。


本文出自 “岁伏” 博客,请务必保留此出处http://suifu.blog.51cto.com/9167728/1853864

本文由 carry9148 创作,采用 知识共享署名 3.0 中国大陆许可协议 进行许可。 可自由转载、引用,但需署名作者且注明文章出处。


如果这篇文章对您有帮助,不妨微信小额赞助我一下,让我有动力继续写出高质量的帖子。

共收到 0 条回复 mysql utf8mb4 emoji
没有找到数据。
回复本帖 (需要登录)