MySQL保存emoji表情之Linux设置数据库编码为utf8mb4

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/hurtheart517/article/details/81118263

一、问题背景:

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)

至此,数据库字符集修改成功。

三、后记:

如果是Windows下通过Navicat连接MySQL,会发现修改重启数据库后,查询结果如下:

一直怀疑数据库字符编码,修改失败,想想可能和连接的客户端有关,将Navicat的数据库连接属性,编码选项修改为utf-8

查询结果与Linux MySQL命令行查询结果一致。最后上传一张,项目效果图

参考资料:

http://blog.51cto.com/suifu/1853864

https://www.cnblogs.com/shihaiming/p/5855616.html

展开阅读全文

没有更多推荐了,返回首页