架构师

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

数据库

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

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

最近在写一个小工具,从QQ群中提取群员的QQ号,昵称等信息,然后做一些羞羞的事情。但是在插入数据库的时候遇到个小问题,有些用户的昵称千奇百怪,可能并不是文本信息,怎么办呢?

这时我们就需要对数据库的字符集进行修改,由utf8改为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)

解决办法:

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

具体步骤:

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

[mysql]

default-character-set=utf8mb4

[mysqld]

basedir=D:\DevTools\mysql-5.7.20-winx64

datadir=D:\DevTools\mysql-5.7.20-winx64\data

port=3306

max_connections=200

character-set-server=utf8mb4

default-storage-engine=INNODB

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

也可以使用Navicat图形化工具修改。

数据库:

mysql> ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

表:

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

字段:

mysql>ALTER TABLE `sys_mems` MODIFY COLUMN `nick`  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)登录数据库检查:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' 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中的设置进行使用,这样就基本满足了需求。


文章评论