MySQL无法存emoji表情的解决方法

TOMCAT日志报错信息

; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '/xF0/x9F/x98/x82' for column 'CONTENT' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '/xF0/x9F/x98/x82' for column 'CONTENT' at row 1}
### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '/xF0/x9F/x98/x82' for column 'CONTENT' at row 1
### Cause: java.sql.SQLException: Incorrect string value: '/xF0/x9F/x98/x82' for column 'CONTENT' at row 1
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '/xF0/x9F/x98/x82' for column 'CONTENT' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '/xF0/x9F/x98/x82' for column 'CONTENT' at row 1
Caused by: java.sql.SQLException: Incorrect string value: '/xF0/x9F/x98/x82' for column 'CONTENT' at row 1

解决方法

1. 修改Mysql数据库的字符集为utf8mb4
   修改/etc/my.cnf，将字符集修改如下，先不要急着重启数据库。

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

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

注意：mysql版本是5.5.3及以上版本才支持utf8mb4字符集，从8.0版本开始默认就是utf8mb4

2. 将存储表情的相关字段和表的字符集修改为utf8mb4

ALTER TABLE B_DOC_COMMENT CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE B_DOC_COMMENT modify CONTENT VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3. 确认JDBC的mysql connector版本要大于5.1.13，否则还是无法插入
   下载链接：
   https://dev.mysql.com/downloads/connector/j/
   然后将mysql-connector-java-5.1.46-bin.jar文件替换掉原来的即可
   最后重启Mysql和应用程序(Tomcat)即可