MySQL 插入移动端表情数据
故事背景
mysql.params = tinyInt1isBit=false&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
简单描述就是,在表级别是utf8mb4_general_ci的情况下,往某个字段里存表情文案报错了,报错信息如下:
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into pub_voice_main (id,message) values (2,'sss🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓')]; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x8D\x93\xF0\x9F...' for column 'message' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\x9F\x8D\x93\xF0\x9F...' for column 'message' at row 1
建表语句如下:
CREATE TABLE `pub_voice_main` (
`id` BIGINT(11) NOT NULL,
`message` TEXT CHARACTER SET utf8mb4 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4
插入数据:
insert into pub_voice_main (id,message) values (2,'sss🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓');
按理说这样操作是没有问题的,但是还是报错了,后面翻阅资料,尝试后之后终于找到了解决的方法,原来问题出在characterEncoding这里,我们指定了库和表以及字段都是utf8mb4之后,characterEncoding要去掉才行,不然还是会报错,指定字段识别不了表情字符。
报错原因:
问题的症结在于,MySQL的"utf8"
实际上不是真正的UTF-8
。
"utf8"
只支持每个字符最多三个字节,而真正的UTF-8
是每个字符最多四个字节。
MySQL一直没有修复这个bug,他们在2010年发布了一个叫作"utf8mb4"
的字符集,绕过了这个问题。
当然,他们并没有对新的字符集广而告之(可能是因为这个bug让他们觉得很尴尬),以致于现在网络上仍然在建议开发者使用"utf8"
,但这些建议都是错误的。
创建新的库:
USE `test`;
CREATE TABLE `pub_voice_main` (
`id` BIGINT (11) NOT NULL,
`message` VARCHAR(256) CHARACTER SET utf8mb4 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 ;
jdbc.url 去掉characterEncoding参数
mysql.params = tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull
配置数据源:
代码插入数据:
@Test
public void testEmoji() {
int i = dataSourceManager.getTestXDao().executeSql("INSERT INTO pub_voice_main (id,message) VALUES (2,'sss\uD83C\uDF53\uD83C\uDF53\uD83C\uDF53\uD83C\uDF53\uD83C\uDF53\uD83C\uDF53\uD83C\uDF53\uD83C\uDF53\uD83C\uDF53\uD83C\uDF53\uD83C\uDF53')");
System.out.println("i = " + i);
List<String> ones = dataSourceManager.getTestXDao().ones(String.class, "select message from pub_voice_main ");
System.out.println(JsonUtils.toString(ones));
}
执行结果:
2021-01-21 16:20:17.298 [main] DEBUG com.toolbox.aspect.AopSqlMonitor - INSERT INTO pub_voice_main (id,message) VALUES (2,'sss🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓')
2021-01-21 16:20:17.298 [main] DEBUG com.toolbox.aspect.AopSqlMonitor - INSERT INTO pub_voice_main (id,message) VALUES (2,'sss🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓')
i = 1
2021-01-21 16:20:17.338 [main] DEBUG com.toolbox.aspect.AopSqlMonitor - select message from pub_voice_main
2021-01-21 16:20:17.338 [main] DEBUG com.toolbox.aspect.AopSqlMonitor - select message from pub_voice_main
["sss🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓","sss🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓🍓"]
后续
后来,笔者又试了把数据库的字符集改成utf8, 也插入成功了,也就是说,插入表情对库的字符集并没有硬性要求!只要表的字符集是utf8mb4就行了。
总结
如果mysql需要存储表情文案,要满足三个条件:
1.表字符设置为utf8mb4
2.字段字符设置为utf8mb4
3.jdbc.url不拼接characterEncoding参数
以上操作基于mysql-5.6.23版本