完美解决 java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x8D\x8E \xF0...‘ for column ‘nick_name

场景

Java + mybatis + mysql ，前端调用接口传 表情符：《🍎 🤔》，后端进行insert/update 抛出以下异常

异常信息如下：

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7187ae57] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@51ee461a] will not be managed by Spring
==>  Preparing: UPDATE u_user SET nick_name=? WHERE id=? AND deleted=0
==> Parameters: 🍎 🤔(String), 70(Integer)
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7187ae57]
2021-11-16 09:32:49.674 ERROR 23368 --- [nio-8800-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8D\x8E \xF0...' for column 'nick_name' at row 1
### The error may exist in com/nn/dao/mapper/UUserMapper.java (best guess)
### The error may involve com.nn.dao.mapper.UUserMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: UPDATE u_user  SET nick_name=?  WHERE id=?  AND deleted=0
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8D\x8E \xF0...' for column 'nick_name' at row 1
; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x8D\x8E \xF0...' for column 'nick_name' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8D\x8E \xF0...' for column 'nick_name' at row 1] with root cause

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8D\x8E \xF0...' for column 'nick_name' at row 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:127) ~[mysql-connector-java-8.0.11.jar:8.0.11]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) ~[mysql-connector-java-8.0.11.jar:8.0.11]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.11.jar:8.0.11]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960) ~[mysql-connector-java-8.0.11.jar:8.0.11]
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:388) ~[mysql-connector-java-8.0.11.jar:8.0.11]

问题原因

出现原因：当insert 或者 update 数据中有表情时发生。这些表情是按照4个字节一个单位进行编码的，而我们使用的utf-8编码在mysql数据库中默认是按照3个字节一个单位进行编码的.

问题解决

第一步：修改mysql的配置文件mysql/bin/my.ini, 添加如下内容：

注意：是添加内容
[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'
skip-character-set-client-handshake = true

完整my.ini

[mysqld]
 
# 设置3306端口
 
port=3306
 
# 设置mysql的安装目录
 
basedir=D:/bq/sorft/mysql-8.0.26-winx64
# 设置mysql数据库的数据的存放目录
 
datadir=D:/bq/sorft/mysql-8.0.26-winx64/data
 
lc-messages-dir=D:/bq/sorft/mysql-8.0.26-winx64/share
 
# 允许最大连接数
 
max_connections=200
 
# 允许连接失败的次数。
 
max_connect_errors=10
 
# 服务端使用的字符集默认为UTF8
 
character-set-server=utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake = true
 
# 创建新表时将使用的默认存储引擎
 
default-storage-engine=INNODB
 
# 默认使用“mysql_native_password”插件认证
 
#mysql_native_password
 
default_authentication_plugin=mysql_native_password
 
default-time-zone='+08:00'
#sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
 
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
 
[client]
# 设置mysql客户端连接服务端时默认使用的端口
default-character-set=utf8mb4

第二步：重启数据库

linux输入命令:service mysql restart
windows输入命令:net stop mysql  和 net start mysql

第三步：统一数据库，数据表，字段编码

** 👍👍👍重要：将编码统一为：utf8mb4 格式**

• 创建数据库 指定编码

CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8mb4_0900_ai_ci;

• 创建数据表，指定编码

CREATE TABLE `u_user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '记录id',
  `media_head_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户头像',
  `nick_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户昵称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='用户表';