MySql支持emoji表情设置

使用MySql支持Emoji表情

将字段设置成utf8mb4

ALTER TABLE `t_order`
MODIFY COLUMN `remark`  varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '订单备注'

 

以为这样就OK了，结果输入emoji表情，执行insert失败，失败信息为：

2020-12-23 10:35:09 [org.apache.juli.logging.DirectJDKLog:182] - 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\x98\x83\xF0\x9F...' for column 'remark' at row 1
### The error may involve com.xima.shop.mapper.OrderMapper.insertUseGeneratedKeys-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO t_order  ( state,flag_del,create_user,create_time,update_user,update_time,memo,order_no,order_time,user_id,user_name,user_phone,total_amt,pay_amt,pay_type,pay_no,flag_sms_send,sms_send_count,state_order,cancel_reason,logistics_num,remark ) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x83\xF0\x9F...' for column 'remark' at row 1
; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x98\x83\xF0\x9F...' for column 'remark' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x83\xF0\x9F...' for column 'remark' at row 1] with root cause
java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x83\xF0\x9F...' for column 'remark' at row 1

　　

上网搜索了一下，还是数据库服务器设置的问题，使用命令更新数据库服务器的编码为utf8mb4

set character_set_server=utf8mb4

 

查看数据库编码show variables like '%char%'

show variables like '%char%'

 

中间小插曲：

我们的数据库使用的阿里云rds，使用root账号执行set character_set_server=utf8mb4，但是develop账号仍然看到的是utf8编码，于是乎，到阿里云后台管理界面修改数据库参数 character_set_server  = utfmb4

 

参考以下资料：

https://www.cnblogs.com/java-synchronized/p/7237146.html

https://blog.51cto.com/wangfoye/1913450