解决emoji表情特殊字符写入数据库Incorrect string value:报错问题

问题

早上巡检某项目日志，筛选发现几条报错信息，如下：

org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8D\xBA",...' for column 'message_body' at row 1
### The error may exist in xxx/mapper/XxxMapper.java (best guess)
### The error may involve xxx.dao.mysql.mapper.XxxMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO xxx  ( trace_id,    create_time,  message_body,      data_size )  VALUES  ( ?,    ?,  ?,      ? )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8D\xBA",...' for column 'message_body' at row 1
uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x8D\xBA",...' for column 'message_body' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8D\xBA",...' for column 'message_body' at row 1

其中Incorrect string value: '\xF0\x9F\x8D\xBA说明是emoji表情特殊字符，数据库表字段字符集不支持。

解决

emoji表情可通过utf8mb4字符集来存储，开始检查数据库表设置。

查看数据库：show create database search_center;
=> CREATE DATABASE xxx /*!40100 DEFAULT CHARACTER SET utf8mb4 */
字符集为utf8mb4

查看表：show create table xxx;
=>... ENGINE=InnoDB AUTO_INCREMENT=4567 DEFAULT CHARSET=utf8mb4 COMMENT 'xxx'
表字符集为utf8mb4

尝试修改报错信息的字段：
alter table xxx modify column message_body mediumtext character set utf8mb4 collate utf8mb4_unicode_ci comment '消息体';

修改后日志里仍然出现了该错误信息。

项目中数据库使用的是阿里云的RDS，在它的网页控制台里执行sql：
insert into mns_log_product_hotword_change(id,message_body) values(0,'"name":"啤酒🍺"');
执行成功，并且数据能正常查询出来。

查询资料发现是character_set_server设置的问题，JDBC底层在执行sql前会通过character_set_server的值自动执行SET NAMES命令设置字符集编码。

查看MySQL字符集：show variables like '%character%';
=>

character_set_client      utf8mb4
character_set_connection  utf8mb4
character_set_database    utf8mb4
character_set_filesystem   binary
character_set_results
character_set_server      utf8
character_set_system      utf8
character_sets_dir        /u01/mysql/share/charsets/

发现character_set_server的值为utf8，而不是utf8mb4。

参考阿里云官方文档RDS MySQL使用utf8mb4字符集存储emoji表情，
在控制台里修改RDS实例的字符集：
在实例列表---参数设置，找到character_set_server，参数默认值utf8，
运行参数值utf8，修改为utf8mb4，确定然后点击提交参数按钮，在合适时段重启实例即可。

备忘

• utf8mb4字符集支持存储emoji表情里的特殊字符
• 数据库实例、数据库、表、字段保存utf8mb4的统一设置

常用sql：

show create database xxx;
show create table xxx;
desc xxx;
show full columns from xxx;
alter table xxx modify column aaa mediumtext character set utf8mb4 collate utf8mb4_unicode_ci comment 'xxx'; 
show variables like '%character%';

参考

• mysql字符集utf8mb4失效踩坑 https://blog.csdn.net/sz85850597/article/details/99695874
• 使用jdbc来处理MySQL的utf8mb4字符集（转） https://blog.csdn.net/freezingxu/article/details/81674597
• RDS MySQL使用utf8mb4字符集存储emoji表情 https://help.aliyun.com/document_detail/41702.html
• mysql 修改编码utf8mb4依旧无法保存表情 https://www.jianshu.com/p/31de3eb7df77
• MySQL查看字符集以及修改字符集 https://blog.csdn.net/YSTWD_WY/article/details/124193675