开发者

mysql字符集引起的java.sql.SQLException:Incorrect string value:问题

开发者 https://www.devze.com 2024-11-20 18:38 出处:网络 作者: 二掌柜,酒来!
目录问题1原因:字符集编码选择utf8mb4 的出现解决方案1. 修改编码方式2. 更改mysql 参数注意总结问题1
目录
  • 问题1
    • 原因:字符集编码选择
      • utf8mb4 的出现
  • 解决方案
    • 1. 修改编码方式
      • 2. 更改mysql 参数
        • 注意
        • 总结

          问题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.27DDQRWsf

          原因:字符集编码选择javascript

          我们新建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 字符提供支持,我想一个是为了向后兼容性的考虑,还有就是基本多文种平面之外的字符www.devze.com确实很少用到。

          要在 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%'

          mysql字符集引起的java.sql.SQLException:Incorrect string value:问题

          • 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_servercollation_server

          注意

          在解决这个问题的过程中,还有一些其他的冗余操作,属于加上也能正常入口特殊字段,但不加也行。如何问题没有解决的话,不妨一试。

          1.数据库链接接增加 &character_setwww.devze.com_server=utf8mb4

          Connector/J 5.1.47 及以上版本:

          • 指定 characterEncoding 参数为 UTF8/UTF-8 即可, 新版本直接映射到 utf8mb4 编码;
          • 如果 connectionCollation 指定的排序规则不是 utf8mb4 相关的, 则 characterEncoding 参数会重写为排序规则对应的编码;

          Connector/J 5.1.47 以下版本:

          • 设置 MySQL 参数变量 character_set_server=utf8mb4;
          • 指定 characterEncoding 参数为 UTF8/UTF-8, jdbc 程序会进行探测是否使用 utf8mb4;

          2.在 application.yaml 中添加下面属性

          initConnectionSqls=[ "SET NAMES utf8mb4"]

          获取上面的值并设置给数据源

          dataSource.setConnectionInitSqls(sqlLists);
          • connectionInitSqls 是一个用于配置数据库连接池的属性,它允许您指定在每个数据库连接建立时要执行的初始化 SQL 语句。
          • 这些 SQL 语句可以用于在连接建立后执行一些特定的操作,例如设置会话变量、执行特定的查询或配置连接的特性。
          • 在许多数据库连接池实现中,包括一些开源的连接池库和应用服务器,都支持 connectionInitSqls 属性来定义连接初始化 SQL。
          • 通过配置这个属性,您可以确保每个连接在使用之前都会执行指定的 SQL 语句。

          3.使用最新的 MySQL 连接器。

          mysql:mysql-connector-java:8.0.27
          jdbc:mysql://192.168.10.10:3306/db_name?characterEncoding=utf8

          总结

          以上为个人经验,希望能给大家一个参考,也希望大家多多支持js编程客栈(www.devze.com)。

          0

          精彩评论

          暂无评论...
          验证码 换一张
          取 消