解决Emoji表情符号在MySQL数据库中存储失败的问题

解决MySQL不能存储Emoji表情问题 一. 问题描述 1. 问题出现背景

在手机的各种输入法键盘中,会自带一些Emoji表情符号,例如:

如果在移动端发布文本内容时包含了这种Emoji表情符号,通过接口传递到服务器端,服务器端再存入MySQL数据库时,不同字符类型可能会产生不同的异常现象.

• 1️⃣. 对gbk字符集相关类型的数据库,写入数据库的数据,在回显时,会变成 ‘口口’ 无法回显.

• 2️⃣. 对utf8字符集的数据库,则根本无法写入数据库,程序直接报出异常信息:

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8D\xF0\x9F...' for column 'xxx' at row

2. 异常如下图示



二. 原因分析

这属于编码格式问题,MySQL 5.5.3之前版本的数据库中,数据库的默认编码是utf8字符集,只支持1-3个字节的字符,默认保存的是1到3个字节,但是现在的emoji表情需要用4个字节才能保存,所以抛出异常.

三. 解决方案

• 1️⃣.过滤emoji表情,遍历输入的文本,把四字节长度的字符,修正为自定义的字符替换掉.

• 2️⃣.修改数据库编码为utb8mb4,修改MySQL数据库字符集,把数据库字符集从utf8 修改为支持 1-4 个字节字符utf8mb4.

第一种方案的工作量较大,并不可取.推荐使用第二种方案,修改数据库字符集.

从MySQL 5.5.3版本开始,数据库可支持4个字节的utf8mb4字符集,一个字符最多可以有4个字节,所以能支持更多的字符集,故能存储Emoji表情符号.所以MySQL 5.5.3 之后版本基本可以无缝升级到 utf8mb4 字符集.同时,utf8mb4兼容utf8字符集,utf8 字符的编码、位置、存储在utf8mb4与utf8字符集里一样的,不会对有现有数据带来损坏.

四. 升级方案 1. 修改数据库字符集character-set-server=utf8mb4重启数据库生效. 2. 修改database 的字符集为 utf8mb4

alter database dbname character set utf8mb4

3. 修改表的字符集 为utf8mb4

alter table character set = utf8mb4

五. 具体解决办法 1. 查看数据库编码



2. 修改数据库配置文件

打开数据库的配置文件,mysql5以上版本的配置文件存放在C:\ProgramData\MySQL\下,而不是安装目录.如果你不知道配置文件放在哪里,可以通过【服务】找到【mysql】服务,从启动参数中看出:

要修改的是【character_set_database】,在配置文件中可以发现这个参数:

在文件开头有个网址,我们打开这个网址看看:

#
# In this file, you can use all long options that the program supports.
# If you want to know the options a program supports, start the program
# with the "--help" option.
#
# More detailed information about the individual options can also be
# found in the manual.
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
#

打开后再进入到 Section 5.1.5, “Server System Variables”,搜索【character_set_database】,可以看到:

这个变量是不能手动修改的,我们需要修改【character-set-server】:

character-set-server=utf8mb4

重启mysql,查看变量:

六. 修改database/table和column的字符集

进入mysql中,按下述所示进行命令的执行.

1. 修改database的字符集

ALTER DATABASE 数据库名 

CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

示例:

ALTER DATABASE xxxdb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

2. 步骤1）执行完成之后,需要执行use 数据库名,指明当前需要进行字符集修改的数据库.

use xxxdb;

3. 修改table的字符集

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

示例:

ALTER TABLE user_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. 修改column的字符集

ALTER TABLE 表名 CHANGE 字段名 字段名 该字段原来的数据类型 CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

示例:

ALTER TABLE user_comments CHANGE content content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. 注意:

• 1️⃣. MySQL的版本不能太低,低于5.5.3的版本不支持utf8mb4编码.

  select version();
  

• 2️⃣. JDBC驱动版本不能太低,mysql connector版本高于5.1.13.

mysql
mysql-connector-java
5.1.38

3️⃣. 将表中的对应字段,比如会员表的呢称字段,其字符集修改成utf8mb4.

4️⃣. 最后修改druid数据源的配置,增加一行.

5️⃣. 检查下jdbc连接串的设置.

jdbc:mysql://localhost:3306/dbname?useUnicode=true&characterEncoding=utf8

有人建议删除useUnicode=true&characterEncoding=utf8,但这样有可能保存数据时会发生乱码的现象.