在项目中使用到Emoji表情保存(MySQL),出现异常,解决方法分为两个部分:
一. 服务端:
数据库/表/字段均采用UTF8编码,MySQL的说法为:
- The utf8 Character Set (3-Byte UTF-8 Unicode Encoding)
- The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
UTF8为3个字节,为4个字节,而Emoji为4个字节,所以在插入时会出现异常:
INSERT INTO ${table} (${field}) VALUES ('What a nice emoji?!');
Incorrect string value: '\xF0\x9F\x98\x80!' for column '${field}' at row 1
修改表/字段的编码为UTF8MB4:
ALTER TABLE `${table}` CHARACTER SET = utf8mb4;
ALTER TABLE `${table}` CHANGE `${field}` `${field}` VARCHAR(1200) CHARACTER SET utf8mb4 NULL DEFAULT NULL;
测试插入:
INSERT INTO ${table} (${field}) VALUES ('Now you are ready to handle emoji ?');
select ${field} from ${table}
+--------------------------------------+
| Level |
+--------------------------------------+
| Now you are ready to handle emoji ? |
+--------------------------------------+
二. 客户端
添加如下配置(配置的key根据使用的连接池来确定):
spring:
datasource:
url: jdbc:mysql://localhost:3306/base-architecture?useUnicode=true&characterEncoding=utf8
type: com.zaxxer.hikari.HikariDataSource
hikari:
connection-init-sql: SET NAMES utf8mb4
PS:emoji,charset-unicode-utf8,charset-unicode-utf8mb4