1. 背景
线上系统运行时,运维反馈,后台log忽然报了许多插入乱码问题。log信息大致如下:
2018-07-19 00:00:13.593 [http-nio-8080-exec-173] ERROR c.k.i.b.s.service.impl.QuestionnaireService 1276- error
org.springframework.jdbc.UncategorizedSQLException:
### Error updating database. Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\x8D\xF0\x9F...' for column 'answerValue' at row 11
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT INTO questionnaire_record(paperId,questionId,answerValue,patId,visitId,wardNo,submitTime,publishedTime,UUID) VALUES ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? ) , ( ?, ?, ?, ?, ?, ?, now(), ?, ? )
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\x8D\xF0\x9F...'
2. 分析
- 初步分析是业务系统里问卷的答案里有表情符,插不进去,定位是emoji字符插入不了数据库问题
3. 解决步骤
- 修改数据库配置项:位于xxxx\MySQL Server 5.7\my.ini
default-character-set=utf8mb4
character-set-server=utf8mb4
注意:如果配置项不存在,根据图中位置手动添加
-
修改已经创建数据库的编码格式
ALTER DATABASE iot CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; -
可以通过以下指令修改每一个表和表中每一行记录的编码格式
- 每个表:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; - 表中每行记录:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
在以后创建新表的时候,避免指定编码格式为utf8,以支持表情符号