java连接mysql设置UTF-8mb4

为了兼容emoji表情符及一些特殊字符(4字节utf保存的字符,若采用传统的uft8的3字节保存会导致保存报错),需要在项目中设置uft8mb4编码格式。

这里首先看一下mysql官网文档对于使用的mysql驱动的说明:https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-charsets.html 对于驱动版本有明确要求

Notes
For Connector/J 5.1.46 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.
对于5.1.46及更早的版本,要使用utf8mb4字符集进行连接,服务器必须配置character_set_server=utf8mb4;如果不是这样,
则在连接字符串中使用UTF-8进行字符编码时,它将映射到MySQL字符集名称utf8,这是utf8mb3的别名。 For Connector
/J 5.1.47 and later: When UTF-8 is used for characterEncoding in the connection string, it maps to the MySQL character set name utf8mb4.
当UTF-8用于连接字符串中的字符编码时,它将映射到MySQL字符集名称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.
由于utfmb3没有可用于连接选项charatencoding的Java样式字符集名称,
因此使用utf8mb3作为连接字符集的唯一方法是对连接选项connection collation使用utf8mb3排序规则(例如,utf8_general_ci),
这将强制使用utf8mb3字符集。

另外:

  • mysql的版本不能太低,低于5.5.3的版本不支持utf8mb4编码。
  • 若出现仍未生效的情况,可考虑修改mysql数据库对于字符的配置,以及相应的表对于字符的配置。

大致需要修改的配置如下:

  • mysql配置文件:
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
  • 建库时,指定字符集编码为 utf8mb4、字符集校对规则为 utf8mb4_general_ci 或 utf8mb4_unicode_ci。
  • 建表时指定 utf8mb4 编码。
原文地址:https://www.cnblogs.com/silenceshining/p/12556131.html