ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL 04-EMOJI 表情与 UTF8MB4 的故事 [打印本页]

---

作者: 慢吞云雾缓吐愁    时间: 2024-5-17 04:50
标题: MySQL 04-EMOJI 表情与 UTF8MB4 的故事
拓展阅读

MySQL View
MySQL truncate table 与 delete 清空表的区别和坑
MySQL Ruler mysql 一样平常开发规范
MySQL datetime timestamp 以及如何主动更新，如何实现范围查询
MySQL 06 mysql 如何实现类似 oracle 的 merge into
MySQL 05 MySQL入门教程（MySQL tutorial book）
MySQL 04- EMOJI 表情与 UTF8MB4 的故事
MySQL Expression 1 of ORDER BY clause is not in SELECT list,references column
emoji

想在 mysql 数据库插入  emoji 表情，结果报错：

1. ### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x8B' for column 'name' at row 1

复制代码

错误缘故原由很多小伙伴也知道，mysql 种的 utf8 和 java 的 utf-8 并不是完全对等的。
应该指定 mysql 的编码为 utf8mb4 才是正确的。
修改编码

查看编码

1. show variables like 'character_set_database'; # 查看数据库编码
2. show create table comment; # 查看表编码

复制代码

修改数据库 & 表编码

可以在原来的底子上直接修改：

1. alter database <数据库名> character set utf8mb4; # 修改数据库
2. alter table <表名> character set utf8mb4; # 修改表
3. alter table <表名> change <字段名> <字段名> <类型> character set utf8mb4; # 修改字段

复制代码

建表时指定

1. drop database echo_blog;
2. CREATE DATABASE echo_blog DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
4. drop table comment;
5. create table comment
6. (
7.     id int unsigned auto_increment comment '主键' primary key,
8.     create_time timestamp default CURRENT_TIMESTAMP not null comment '创建时间',
9.     update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
10. ) comment '评论信息' ENGINE=Innodb default charset=UTF8MB4 auto_increment=1;

复制代码

乱码问题

修改完成之后，插入乐成。
但是数据库中全部是 ??? 之类的内容，而不是表情内容。
到底哪里出问题了呢？
mysql 编码问题

查看 mysql 编码

1. SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

复制代码

如下：

1. mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
2. +--------------------------+----------------------------------------------------+
3. | Variable_name            | Value                                              |
4. +--------------------------+----------------------------------------------------+
5. | character_set_client     | utf8mb4                                            |
6. | character_set_connection | utf8mb4                                            |
7. | character_set_database   | utf8mb4                                            |
8. | character_set_filesystem | binary                                             |
9. | character_set_results    | utf8mb4                                            |
10. | character_set_server     | utf8mb4                                            |
11. | character_set_system     | utf8                                               |
12. | character_sets_dir       | D:\tools\mysql\mysql-5.7.24-winx64\share\charsets\ |
13. | collation_connection     | utf8mb4_unicode_ci                                 |
14. | collation_database       | utf8mb4_unicode_ci                                 |
15. | collation_server         | utf8mb4_unicode_ci                                 |
16. +--------------------------+----------------------------------------------------+

复制代码

属性说明：
character_set_client
主要用来设置客户端使用的字符集。普通的讲就是mysql把客户端传递过来的数据都当成是utf8mb4
character_set_connection
主要用来设置连接数据库时的字符集，如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置。
character_set_database
主要用来设置默认创建数据库的编码格式，如果在创建数据库时没有设置编码格式，就按照这个格式设置。
character_set_filesystem
文件系统的编码格式，把操作系统上的文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的。
character_set_results
数据库给客户端返回时使用的编码格式，如果没有指明，使用服务器默认的编码格式。普通的讲就是mysql发送个客户端的数据是utf8mb4的
character_set_server
服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为界说。
character_set_system
数据库系统使用的编码格式，这个值一直是utf8，不需要设置，它是为存储系统元数据的编码格式。
character_sets_dir
这个变量是字符集安装的目次。
Mysql的字符集内部处置惩罚

1.mysql Server收到哀求时将哀求数据从 character_set_client 转换为 character_set_connection
2.进行内部操作前将哀求数据从 character_set_connection 转换为内部操作字符集,步调如下
　　A. 使用每个数据字段的 CHARACTER SET 设定值；
　　B. 若上述值不存在，则使用对应数据表的字符集设定值
　　C. 若上述值不存在，则使用对应数据库的字符集设定值；
　　D. 若上述值不存在，则使用 character_set_server 设定值。
3.最后将操作结果从内部操作字符集转换为 character_set_results
临时修改配置

上面的配置都可以通过命令临时修改：

1. SET character_set_client = utf8mb4;
2. SET character_set_connection = utf8mb4;
3. SET character_set_database = utf8mb4;
4. SET character_set_results = utf8mb4;
5. SET character_set_server = utf8mb4;
7. SET collation_connection = utf8mb4_unicode_ci;
8. SET collation_database = utf8mb4_unicode_ci;
9. SET collation_server = utf8mb4_unicode_ci;

复制代码

固然，也可以通过修改 my.ini 配置文件。
修改 mysql 服务器配置文件

比如 windows 下个人的 mysql 安装目次为：D:\tools\mysql\mysql-5.7.24-winx64
那就在下面创建 my.ini（如果没有的话）。
内容如下：

1. [mysql]
2. # 设置mysql客户端默认字符集
3. default-character-set=utf8mb4
5. [mysqld]
6. # 设置3306端口
7. port=3306
8. # 允许最大连接数
9. max_connections=20
10. # 服务端使用的字符集默认为8比特编码的latin1字符集
11. character-set-server=utf8mb4
12. # 创建新表时将使用的默认存储引擎
13. default-storage-engine=INNODB
15. collation-server=utf8mb4_unicode_ci
16. init_connect='SET NAMES utf8mb4'
18. character-set-client-handshake = FALSE
19. explicit_defaults_for_timestamp=true
21. [client]
22. default-character-set=utf8mb4

复制代码

修改完成后需要重启 mysql 服务。
可以在 bin 下执行 mysqld restart。这个实践下来只初始化了部分编码。
个人实在 windows services（服务） 下，把 mysql 服务进行了重新启动。
jdbc 配置

druid 数据源配置

1. spring:
2.   datasource:
3.     druid:
4.       username: root
5.       password: xxxxxx
6.       url: jdbc:mysql://localhost:3306/echo_blog?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
7.       driver-class-name: com.mysql.jdbc.Driver
8.       connection-init-sqls: set names utf8mb4;

复制代码

官方资料

https://dev.mysql.com/doc/connectors/en/connector-j-reference-charsets.html

1. Notes
2. For Connector/J 8.0.12 and earlier: In order to use the utf8mb4 character set for the connection, the server MUST be configured with character_set_server=utf8mb4; if that is not the case, when UTF-8 is used for characterEncoding in the connection string, it will map to the MySQL character set name utf8, which is an alias for utf8mb3.
4. For Connector/J 8.0.13 and later:
6. When UTF-8 is used for characterEncoding in the connection string, it maps to the MySQL character set name utf8mb4.
8. If the connection option connectionCollation is also set alongside characterEncoding and is incompatible with it, characterEncoding will be overridden with the encoding corresponding to connectionCollation.
10. Because there is no Java-style character set name for utfmb3 that you can use with the connection option charaterEncoding, the only way to use utf8mb3 as your connection character set is to use a utf8mb3 collation (for example, utf8_general_ci) for the connection option connectionCollation, which forces a utf8mb3 character set to be used, as explained in the last bullet.
12. Warning
13. Do not issue the query SET NAMES with Connector/J, as the driver will not detect that the character set has been changed by the query, and will continue to use the character set configured when the connection was first set up.

复制代码

说明：

1. 提示
2. mysql-connector-java 版本在8.0.12之前的，包括8.0.12，服务端必须设置character_set_server=utf8mb4;如果不是的话，就算设置了characterEncoding=UTF-8，照样会被设置为MYSQL的 utf8字符集，也就是utf8mb3。
4. 对于8.0.13和以后的版本，如果设置了characterEncoding=UTF-8，他会映射到MYSQL的utf8mb4字符集。
6. 如果connectionCollation 也和characterEncoding一起设置了，但是不兼容，characterEncoding会被connectionCollation的设置覆盖掉。
8. 由于没有Java-Style的utfmb3对应的字符集名称可以用在connection选项charaterEncoding上，唯一的设置utf8mb3的方式就是在连接选项设置utf8mb3 collation（例如utf8_general_ci），这会强制使用utf8mb3字符集，正如上文所述。
10. 警告
11. 不要通过Connector发起SET NAMES指令，因为driver不会检测字符集是不是被查询语句改动，并且当连接第一次建立之后，会继续使用当时的字符集设置。

复制代码

可以发现 jdbc 中的配置 connection-init-sqls: set names utf8mb4; 这句话是没啥用的。
建议老老实实的修改 mysql 服务端的配置。
依然乱码

这个时候 java 客户端保存 emoji，依然有部分乱码。
比如：
[code]
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4