MySQL支持emoji表情存储

MySQL插入emoji表情,出现错误

1
java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8A' for column 'content' at row

可以对4字节的字符进行编码存储,然后取出来的时候,再进行解码。但是这样做会使得任何使用该字符的地方都要进行编码与解码。

utf8mb4编码是utf8编码的超集,兼容utf8,并且能存储4字节的表情字符。
采用utf8mb4编码的好处是:存储与获取数据的时候,不用再考虑表情字符的编码与解码问题。

MySQL的版本

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

MySQL驱动

5.1.34可用,最低不能低于5.1.13

修改MySQL配置文件

修改mysql配置文件my.cnf(Windows为my.ini)
my.cnf 一般在 /etc/mysql/my.cnf 位置

部分Linux系统的 my.cnf 文件打开后是这样的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

那么这个配置文件的路径应该改成文件内容下面的地址

1
/etc/mysql/conf.d/mysql.cnf

找到后请在以下三部分里添加或修改如下内容

1
2
3
4
5
6
7
8
9
10
11
[client]
default-character-set=utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

[mysql]
default-character-set=utf8mb4

重启数据库,检查变量

Windows终端指令

1
2
net stop mysql57
net start mysql57

Linux终端指令

1
2
3
service mysql stop
service mysql start
service mysql status

数据库SQL语句

1
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

修改前输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| collation_connection | gbk_chinese_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+--------------------------+-------------------+

修改后输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+--------------------------+--------------------+
| 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 |
+--------------------------+--------------------+