问题描述

在特定业务场景（帖子、评论、个人签名）存储 emoji 表情。如果使用 utf8 字符集存储，会抛出如下错误：
java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x94' for column 'name' at row 1

原因分析

MySQL 的 utf8 编码最多支持 3 个字节，而 emoji 表情需要占用 4 个字节，在早期的版本并没有实现真正意义上的 utf8 字符集。MySQL 从 5.5.3 版本开始支持 utf8mb4 字符集，大多数情况下，我们使用的生产数据库都是 5.7.x 或者 8.0.x 版本，问题不大。

解决方案

备选方案 改造点 优缺点
把 utf8 编码改为 utf8mb4 客户端修改会话字符集
服务端调整相关库表的字符集		 更改大数据表困难
写入数据时编码，读取数据时解码还原 程序对每一个需要处理的字段进行修改，工作量不可控 不用修改生产数据库，DB 可读性差，额外性能消耗

正常情况下，我们会选择前者来解决这个问题。

  1. 查看 MySQL 服务端的字符集，确认是否为 utf8mb4。
1
SHOW VARIABLES WHERE VARIABLE_NAME LIKE 'character_set_database' OR VARIABLE_NAME LIKE 'collation%';

如果不是，进行调整，调整 MySQL 的配置文件，内容如下。

1
2
3
4
5
6
7
8
9
10
11
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
character-set-client-handshake = FALSE

重启 MySQL Server，重新确认 MySQL 服务端的字符集是否修改成功。

  1. 调整 JDBC 连接串，不配置 characterEncoding 选项，让 MySQL 连接器选择服务端的字符集。
1
jdbc:mysql://localhost:3306/db?useUnicode=true&&zeroDateTimeBehavior=convertToNull&autoReconnect=true
  1. 检查客户端代码的会话字符集。有些云数据库的环境并不支持配置 character-set-client-handshake 或者 init_connect 这样的参数。您可以通过 set names utf8mb4; 命令将 character_set_client、character_set_connection、character_set_results 等会话字符集相设置为 utf8mb4，以保证写入或者读出的数据使用 utf8mb4 字符集进行处理。例如 HikariCP 数据库连接池框架，在应用的配置项加上相关参数。
1
2
3
4
spring:
  datasource:
    hikari:
      connection-init-sql: SET NAMES utf8mb4
  1. 修改历史数据的字符集。对于存储了字符编码为 utf8 的历史数据，如果要支持 utf8mb4 ，需要将已经存在的数据库、表、列的类型修改成 utf8mb4。首先，我们先调整数据库的默认字符集。
1
ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

MySQL 支持您修改表或者列的字符集。修改 Table 的字符集示例如下：

1
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如果您不希望修改整个表的字符集，可以选择指定 Column 进行调整。

1
ALTER TABLE <table_name> MODIFY COLUMN <column_name> VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;