MySQL保存Emoji表情（踩坑巨多系列）-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45154474/article/details/105094279

最近在项目中遇到要保存带表情的文本，在Navicat中插入表情失败后，便在网上寻找办法。

MySQL保存表情的原理我就不在赘述了，这里就记录一下解决过程。由于我踩了一堆又一堆的坑，过程很曲折😂，想直接知道解决方法的小伙伴请直接拉到文末。

1、手动修改MySQL字符集

查看MySQL字符集：

mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

好的，字符集没啥变化。

查看库、表、字段字符集：

mysql> show table status from ims like '%messages%';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| messages | InnoDB |      10 | Dynamic    |   25 |            655 |       16384 |               0 |            0 |         0 |             29 | 2020-03-16 20:21:22 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

mysql> use ims
Database changed
mysql> show full columns from messages;
+----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field    | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id       | int(11)      | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| to       | varchar(20)  | utf8mb4_unicode_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| from     | varchar(20)  | utf8mb4_unicode_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| type     | varchar(20)  | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| content  | varchar(255) | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| sendTime | datetime     | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
| delTo    | tinyint(1)   | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
| delFrom  | tinyint(1)   | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
+----------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+

这些倒是修改好了。

接下来手动修改字符集：

mysql> set character_set_client=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_connection=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_database=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set character_set_results=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql>  set character_set_server=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_system=utf8mb4;
ERROR 1238 (HY000): Variable 'character_set_system' is a read only variable

修改到character_set_system的时候报错了，表明它是一个只读变量，不能修改（其实是数据库系统使用的编码格式，不需要设置，它是为存储系统元数据的编码格式）。

在网上找到一篇文章说：
mysql> set names gbk;
它相当于下面的三句指令：
SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;

这样就更快了：

set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

好的，成功了。
重新查看：

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' 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               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_general_ci |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+
10 rows in set, 1 warning (0.00 sec)

😬不是吧，没效果啊。又在Navicat中尝试插入表情，还是失败了。
虽然失败了，但是还有一个问题就是，这样设置只是临时设置，重启MySQL之后就会回归原样，如果有注意到的话，每次set都是Query OK, 0 rows affected (0.00 sec)。这个操作不会影响任何一行。
所以釜底抽薪的办法还是使用my.ini配置文件配置字符集。

2、按照大多数教程那样，修改my.ini和库、表、字段字符集

首先我发现MySQL5.7安装目录下并没有my.ini配置文件（此处有大坑），只有my-default.ini模板文件。

my-default.ini中也有注释介绍该文件，并提供了一个官方网址：https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html。

官网说，可以将my-default.ini文件复制一份到该目录下，并改名为my.ini，则为MySQL的配置文件。

If you install on Windows from a Zip archive, you can copy the my-default.ini template file in the base installation directory to my.ini and use the latter as the default option file.

然后按照一些教程，在my.ini中加入如下配置：

[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

重启MyQSL，再修改库、表、字段字符集为utf8mb4

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name CHANGE column_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

好，修改完成。

在Navicat中添加一个表情😂，哦豁，还是出错了。
看一下字符集：

mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

坑就出来了。为什么我的my.ini是无效的。
原因很简单，MySQL用的不是这个配置文件~
打开服务–>找到MySQL–>右击选择属性–>
mysql
原来不是没有my.ini，而是它在C:\ProgramData\MySQL\MySQL Server 5.7下，不是在安装目录C:\Program Files\MySQL\MySQL Server 5.7😂

好了，找到问题所在，直接修改，重启，查看字符集。
ok，此时已经修改为utf8mb4了。
保存表情，报错~

3、最后的拦路虎：sql-code

my.ini中默认的sql-mode :
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

删除STRICT_TRANS_TABLES属性，即：
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

重启MySQL，此时就可以保存表情了。😊不过，保存在数据库中的表情是？，没错，就是 疑问号。

4、方法总结

（1）找到my.ini（怎么找前面有讲），加入或修改如下配置：

[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'

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

（2）修改数据库、表、字段字符集：
可以直接在Navicat这些可视化工具中直接修改，
也可以在mysql客户端输入如下命令：

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name CHANGE column_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

（3）重启MySQL
（4）若是使用SpringBoot，在配置文件中加入配置：

spring:
  datasource:
    hikari:
      connection-init-sql: SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci

我是用的是默认的hikari数据源，其他数据源的配置请自行百度😁。

参考文章
https://blog.youkuaiyun.com/qq_29225709/article/details/79419507

https://blog.youkuaiyun.com/my_java2012/article/details/70213383?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task

https://blog.youkuaiyun.com/gentlu/article/details/83783901?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task

https://blog.youkuaiyun.com/u013360850/article/details/85493811?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task