问题1
在执行一次数据库插入的时候,偶然发现的一个问题。数据库在插入一些生僻字,如𨭉、𡌶
或者emoji 表情包的时候,会出现如下异常。
Cause: java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x91\x87\xE7\x9A…’ for column ‘content’ at row 1
环境
mysql-connector-java-5.1.46
mysql 5.7.27
原因:字符集编码选择
我们新建mysql数据库的时候,需要指定数据库的字符集,一般我们都是选择utf8这个字符集。而如果我们仔细观察的话,其实我们会发现还有一种utf8mb4字符集。那么这两者有什么关联呢。
utf8mb4 的出现
utf8 是 Mysql 中的一种字符集,只支持最长三个字节
的 UTF-8字符,也就是 Unicode 中的基本多文本平面。
- utf8 是 MySQL 中的一种字符集,最早支持的 UTF-8 编码。
- 它只能存储最长三个字节的 UTF-8 字符,也就是 Unicode 中的基本多文本平面(BMP)中的字符。
- 原始的 utf8 实现并没有涵盖所有 Unicode 字符,仅支持 BMP 中的字符,大约占所有 Unicode 字符的 90%。
- 在 MySQL 5.7 及更早版本中,utf8 是默认字符集。
MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就够了。
可以简单的理解 utf8mb4 是目前最大的一个字符编码,支持任意文字。
Mysql 中的 utf8 为什么只支持持最长三个字节的 UTF-8字符呢?我想了一下,可能是因为 Mysql 刚开始开发那会,Unicode 还没有辅助平面这一说呢。那时候,Unicode 委员会还做着 “65535 个字符足够全世界用了”的美梦。Mysql 中的字符串长度算的是字符数而非字节数,对于 CHAR 数据类型来说,需要为字符串保留足够的长。当使用 utf8 字符集时,需要保留的长度就是 utf8 最长字符长度乘以字符串长度,所以这里理所当然的限制了 utf8 最大长度为 3,比如 CHAR(100) Mysql 会保留 300字节长度。至于后续的版本为什么不对 4 字节长度的 UTF-8 字符提供支持,我想一个是为了向后兼容性的考虑,还有就是基本多文种平面之外的字符确实很少用到。
要在 Mysql 中保存 4 字节
长度的 UTF-8 字符,需要使用 utf8mb4 字符集,但只有 5.5.3 版本以后的才支持。我觉得,为了获取更好的兼容性,应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据,utf8mb4 会多消耗一些空间,根据 Mysql 官方建议,使用 VARCHAR 替代 CHAR。
解决方案
修改 Mysql 数据中数据表的编码格式,设置成 utf8mb4
1. 修改编码方式
- 修改单个字段编码方式
alter table <表名> modify column <字段名> <字段类型> character set utf8mb4 collate utf8mb4_unicode_ci;
utf8mb4_unicode_ci 是排序方式
- 修改表编码方式
ALTER TABLE <表名> CONVERT TO CHARACTER SET utf8mb4 COLLATE UTF8MB4_UNICODE_CI;
或者直接通过cavicat or sqlyog 等连接工具修改编码方式表。有时候修改没有效果,还是需要命令行修改。
2. 更改mysql 参数
mysql 字符集参数查看
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'
- character_set_client:客户端字符集,用于指定从客户端发送到服务器的字符集。
- character_set_connection:连接字符集,用于指定客户端与服务器之间的连接字符集。
- character_set_database:数据库字符集,用于指定创建新数据库时的默认字符集。
- character_set_results:结果字符集,用于指定从服务器返回给客户端的结果字符集。
- character_set_server:服务器字符集,用于指定服务器的默认字符集。
- collation_server:服务器校对规则,用于指定服务器的默认校对规则。
- collation_database:数据库校对规则,用于指定创建新数据库时的默认校对规则。
- collation_connection:连接校对规则,用于指定客户端与服务器之间的连接校对规则。
SET GLOBAL character_set_client = utf8mb4;
SET GLOBAL character_set_connection = utf8mb4;
SET GLOBAL character_set_database = utf8mb4;
SET GLOBAL character_set_results = utf8mb4;
SET GLOBAL character_set_server = utf8mb4;
SET GLOBAL collation_server = utf8mb4_unicode_ci;
SET GLOBAL collation_database = utf8mb4_unicode_ci;
SET GLOBAL collation_connection = utf8mb4_unicode_ci;
需要重启数据库
生效!!!
需要注意的是,修改字符集可能会涉及到数据转换和重新排序操作。在执行这些操作之前,请务必备份您的数据库,以防数据丢失或不可逆的更改发生。
只改这个两个也能成功。
character_set_server
,collation_server
尾言
在解决这个问题的过程中,还有一些其他的冗余操作,属于加上也能正常入口特殊字段,但不加也行。如何问题没有解决的话,不妨一试。
- 数据库链接接增加
&character_set_server=utf8mb4
Connector/J 5.1.47 及以上版本:
1. 指定 characterEncoding 参数为 UTF8/UTF-8 即可, 新版本直接映射到 utf8mb4 编码;
2. 如果 connectionCollation 指定的排序规则不是 utf8mb4 相关的, 则 characterEncoding 参数会重写为排序规则对应的编码;
Connector/J 5.1.47 以下版本:
1. 设置 MySQL 参数变量 character_set_server=utf8mb4;
2. 指定 characterEncoding 参数为 UTF8/UTF-8, jdbc 程序会进行探测是否使用 utf8mb4;
- 在 application.yaml 中添加下面属性
initConnectionSqls=[ "SET NAMES utf8mb4"]
获取上面的值并设置给数据源
dataSource.setConnectionInitSqls(sqlLists);
connectionInitSqls 是一个用于配置数据库连接池的属性,它允许您指定在每个数据库连接建立时要执行的初始化 SQL 语句。这些 SQL 语句可以用于在连接建立后执行一些特定的操作,例如设置会话变量、执行特定的查询或配置连接的特性。
在许多数据库连接池实现中,包括一些开源的连接池库和应用服务器,都支持 connectionInitSqls 属性来定义连接初始化 SQL。通过配置这个属性,您可以确保每个连接在使用之前都会执行指定的 SQL 语句。
- 使用最新的 MySQL 连接器。
mysql:mysql-connector-java:8.0.27
jdbc:mysql://192.168.10.10:3306/db_name?characterEncoding=utf8