Loading
0

Mysql存储emoji报错解决

毕设项目使用了WangEditor作为编辑器,在测试文字、图片、视频、表情等各种载体的编辑内容时,偶然发现存储emoji会导致数据库相关报错。在搜索引擎查询相关原因后,发现是数据库字符编码的锅,mysql的UTF-8似乎只支持三个字节的UTF-8字符,而emoji有4个字符,从而导致报错。

06-Mar-2022 21:32:40.451 严重 [http-nio-8080-exec-8] org.apache.catalina.core.StandardWrapperValve.invoke 在路径为/Allin的上下文中,Servlet[SpringMVC]的Servlet.service()引发了具有根本原因的异常Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x84<b...' for column 'describle' at row 1
### The error may involve com.bugcola.dao.ItemsMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into items (id, catelog_id, user_id,     name, price, real_price, start_time,     end_time, polish_time,describle)     values (?, ?, ?,     ?, ?, ?, ?,     ?, ?, ?     )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x84<b...' for column 'describle' at row 1
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x98\x84<b...' for column 'describle' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x84<b...' for column 'describle' at row 1
	java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x84<b...' for column 'describle' at row 1
		at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
		at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

于是使用Navicat把对应数据库编码及相关字段编码换成UTF-8mb4,接着再利用Navicat把已有数据的编码转换成UTF-8mb4,就能正常存储emoji了。

声明:本文为原创,作者为 Zanyxd,转载时请保留本声明及附带文章链接:https://blog.bugcola.com/note/index.php/1230/