MySQL 04-EMOJI 表情与 UTF8MB4 的故事

2024-04-13 04:28
文章标签 mysql 04 表情 故事 database emoji utf8mb4

本文主要是介绍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 表情，结果报错：

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

错误原因很多小伙伴也知道，mysql 种的 utf8 和 java 的 utf-8 并不是完全对等的。

应该指定 mysql 的编码为 utf8mb4 才是正确的。

修改编码

查看编码

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

修改数据库 & 表编码

可以在原来的基础上直接修改：

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

建表时指定

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

乱码问题

修改完成之后，插入成功。

但是数据库中全部是 ??? 之类的内容，而不是表情内容。

到底哪里出问题了呢？

mysql 编码问题

查看 mysql 编码

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

如下：

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                                               |
| character_sets_dir       | D:\tools\mysql\mysql-5.7.24-winx64\share\charsets\ |
| collation_connection     | utf8mb4_unicode_ci                                 |
| collation_database       | utf8mb4_unicode_ci                                 |
| collation_server         | utf8mb4_unicode_ci                                 |
+--------------------------+----------------------------------------------------+

属性说明：

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

mysql charsets

临时修改配置

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

SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_database = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_server = utf8mb4;SET collation_connection = utf8mb4_unicode_ci;
SET collation_database = utf8mb4_unicode_ci;
SET collation_server = utf8mb4_unicode_ci;

当然，也可以通过修改 my.ini 配置文件。

修改 mysql 服务器配置文件

比如 windows 下个人的 mysql 安装目录为：D:\tools\mysql\mysql-5.7.24-winx64

那就在下面创建 my.ini（如果没有的话）。

内容如下：

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4[mysqld]
# 设置3306端口
port=3306
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODBcollation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'character-set-client-handshake = FALSE
explicit_defaults_for_timestamp=true[client]
default-character-set=utf8mb4

修改完成后需要重启 mysql 服务。

可以在 bin 下执行 mysqld restart。这个实践下来只初始化了部分编码。

个人实在 windows services（服务） 下，把 mysql 服务进行了重新启动。

jdbc 配置

druid 数据源配置

spring:datasource:druid:username: rootpassword: xxxxxxurl: jdbc:mysql://localhost:3306/echo_blog?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTCdriver-class-name: com.mysql.jdbc.Driverconnection-init-sqls: set names utf8mb4;

官方资料

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

Notes
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.For Connector/J 8.0.13 and later:When UTF-8 is used for characterEncoding in the connection string, it maps to the MySQL character set name utf8mb4.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.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.Warning
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.

说明：

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

可以发现 jdbc 中的配置 connection-init-sqls: set names utf8mb4; 这句话是没啥用的。

建议老老实实的修改 mysql 服务端的配置。

依然乱码

这个时候 java 客户端保存 emoji，依然有部分乱码。

比如：

💔✊💓💖😧😯 I Love this!

用命令行查看，数据库变成了：

 �✊���� I Love this!

部分乱码？ what's up？

java 程序断点

在 java 应用中进行断点，发现内容是对的。

mysql 命令行插入

我们直接在 mysql 命令行执行插入：

insert into comment (content) values ('💔✊💓💖😧😯 I Love this!');

MD，发现无法执行，这条路走不通。

命令行终端不支持 emoji 表情。

乱码的原因

这个乱码是因为 mysql 终端导致的，还是别的原因？

我们测试一下，使用 java 程序对内容进行查询。

测试代码

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class CommentServiceTest {@Autowiredprivate CommentService commentService;@Testpublic void selectTest() {Comment comment = commentService.selectById(20);System.out.println(comment);}}

测试效果

日志如下：

Comment{id=20, content=💔✊💓💖😧😯 I Love this!, ackFlag=N, createTime=Sun Aug 15 10:13:56 CST 2021, updateTime=Sun Aug 15 10:13:56 CST 2021}

可以发现，存储的数据本身是没有问题的。

是命令行终端的问题。

命令行的问题

已有的命令行

一开始使用的是 windows10 自带的 cmd，发现不行。

使用 cmder 命令行，还是不行。

powershell

我们测试下微软商店的 Terminal 命令行，也就是新版本的 powershell。

cd D:\tools\mysql\mysql-5.7.24-winx64\bin
.\mysql -uroot -p

登录后执行查询，结果如下：

💔✊💓💖😧😯 I Love this!

纠结了半天的乱码，经确认是命令行终端的问题。

参考资料

MySQL的utf8、utf8mb4、编码问题详解

mysql字符集utf8mb4失效踩坑

Mysql UTF-8mb4字符集的问题

这篇关于MySQL 04-EMOJI 表情与 UTF8MB4 的故事的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/899131。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...
MySQL-CRUD入门1

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就
阅读更多...
Java 连接Sql sever 2008

Java 连接Sql sever 2008

Java 连接Sql sever 2008 /Sql sever 2008 R2 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TestJDBC
阅读更多...
Mysql BLOB类型介绍

Mysql BLOB类型介绍

BLOB类型的字段用于存储二进制数据 在MySQL中，BLOB类型，包括：TinyBlob、Blob、MediumBlob、LongBlob，这几个类型之间的唯一区别是在存储的大小不同。 TinyBlob 最大 255 Blob 最大 65K MediumBlob 最大 16M LongBlob 最大 4G
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2