MySQL保存emoji表情之Linux设置数据库编码为utf8mb4
576 ✦ 843842144@qq.com

一、问题背景:

MySQL数据库编码为utf8时,保存emoji表情失败,异常信息:

java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\x94’ for column ‘content’ at row 1

查看数据库字符集信息,如下

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /opt/mysql/share/charsets/ |
| collation_connection | utf8_general_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8_general_ci |
+--------------------------+----------------------------+
11 rows in set
查找资料,了解到collation_connection 、collation_database 、collation_server是什么没关系。

但必须保证

系统变量 描述
character_set_client (客户端来源数据使用的字符集)
character_set_connection (连接层字符集)
character_set_database (当前选中数据库的默认字符集)
character_set_results (查询结果字符集)
character_set_server (默认的内部操作字符集)
这几个变量必须是utf8mb4。

二、解决问题步骤:

1、Linux环境, 通过编辑my.cnf文件,设置MySQL数据库字符集,centos6.5 安装MySQL 5.7.21 ,没有找到my.cnf文件。

关于,MySQL读取配置参数顺序,如下描述:

On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first).


File Name Purpose
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf Server-specific options
defaults-extra-file The file specified with --defaults-extra-file=path, if any
~/.my.cnf User-specific options
即/etc/my.cnf  ——> /etc/mysql/my.cnf——>/usr/local/mysql/etc/my.cnf

2、将Windows环境安装的MySQL配置文件my-default.ini,copy至Linux服务器/usr/local/mysql/etc/目录下,重命名为my.cnf,

添加如下基础配置信息:

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
完整文件如下:文件下载地址

3、重启MySQL数据库

输入以下命名重启MySQL数据库,

service mysqld restart
Linux进入MySQL命令行模式:

mysql -uroot -p
查看修改后结果:

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 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+----------------------------------+
11 rows in set (0.31 sec)
至此,数据库字符集修改成功。

参考:https://blog.csdn.net/hurtheart517/article/details/81118263

PS:

我们从另一个角度来看问题,其实我们在存的时候过滤掉表情即可,就不用担心以后各种取各种兼容各种显示了。

<!-- https://mvnrepository.com/artifact/com.vdurmont/emoji-java -->
<dependency>
<groupId>com.vdurmont</groupId>
<artifactId>emoji-java</artifactId>
<version>4.0.0</version>
</dependency>

这个包里,有这个方法

String string = EmojiParser.removeAllEmojis(param);
Submit
no comments yet~