【数据库】报错：SQLException: Incorrect string value: ‘\xF0\x9F\x98\x80‘ for colum

在同步数据的时候，遇到了如标题这样的报错

这得从mysql的utf8和utf8mb4说起（从盘古开天辟地说起）

 

一、原因：

mysql最早的utf8是3个byte组成的，但是有一些字符是4个byte组成的，比如现在各类短视频，交友平台用户昵称会使用的emoji表情符号，也就是说mysql中存这个昵称的字段如果是utf8的编码，那很可能就会报如上的错！

 

二、解决：

1、将字段改为utf8mb4

提供不同的思路：

（1）、新建utf8mb4的表，然后把数据导出再导入

如何设置字符集，如何建表，希望大家先静下心来，看看下面的文章

我们可以设置mysqlserver，database，table，column四个级别的字符集

再见乱码：5分钟读懂MySQL字符集设置（作者：程序猿小卡_casper）https://segmentfault.com/a/1190000012775484

 

（2）、直接把原表的utf8改成utf8mb4

命令：ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4;

但是执行之前，一定要先看看如下文章，因为执行这条命令的时候很有可能影响该表的使用

mysql使用utf8mb4经验吐血总结：https://blog.csdn.net/qq_17555933/article/details/101445526

 

（3）、pt-online-schema-change

如果是数据分析场景，涉及到历史数据，最方便的就是通过以前的编码把数据正常读下来，然后插入新的编码的字段中（因为不用online所以还是比较方便的），最怕的就是同一张表，老数据用以前的字符集才能读出来，新数据需要用新的字符集才能读出来，那就比较麻烦了

如果希望在线执行DDL，有这么一个工具可以使用，大家有兴趣研究下（毕竟我不是专业的DBA，不太清楚这个方面，我就不瞎写了）

在线修改大表结构pt-online-schema-change（作者：青叶）：https://segmentfault.com/a/1190000014924677?utm_source=tag-newest

 

2、使用jdbc连接数据库

jdbc连接数据库，会url连接串中如果没有写明一些参数，那么会自动读取server级别的参数，这就会带来一个问题，如果没有设置character-set-server等相关参数为utf8mb4的时候，我们还是会遇到各种各样？？或者乱码或者输入插入报错的问题。因为我们需要在client端指定！

jdbc:mysql://127.0.0.1:3306/mysql?com.mysql.jdbc.faultInjection.serverCharsetIndex=45

这个参数和这个45是什么意思？

有兴趣的小伙伴可以debug下jdbc的ConnectionImpl

大体上来说就是，如果设置了这个参数，执行sql之前会帮你执行一段

SET NAMES utf8mb4

注意：characterEncoding=utf8mb4，是不行的

报错如下：

连接串也可以这么写：jdbc:mysql://127.0.0.1:3306/mysql?characterEncoding=utf8&com.mysql.jdbc.faultInjection.serverCharsetIndex=45

也可以不把characterEncoding写上，代码中会自动解析到使用的是utf8

又或者不写那个=45的参数，执行sql前，先执行如下代码也可以

conn.prepareStatement("set names utf8mb4").execute();

 

3、修改全局的utf8mb4

这个基本上是要改my.cnf配置文件的，否则各种设置，重启就没了，我就不具体说了，大家找其他相应文章吧

使用的时候，注意mysql版本要在5.5.3以上，jdbc的包版本要在5.1.13 及以上版本

 

4、裁剪掉多余的部分

举个例子，字段A只能存3个字符，insert的时候我传入了"abcd"这4个字符，如果mysql的sql_mode是严格模式，那么会报错1406 - Data too long for column 'name' at row 1

如果不是严格模式（ANSI模式），就会存abc这个值

那么这个也能用来解决utf8的表，我传入4个byte的emoji，但是会被裁剪掉，也就说我无法还原那个emoji字符

在某些特殊场景也可以使用这样的方式来解决（比如爬虫中一些相对无关紧要的字段，比如我用来做分词做检索的字段，明显表情符号对我来说没什么太大用处）

--查询：
SELECT @@sql_mode

--设置：
set sql_mode='ANSI';

具体有哪些，大家可以自行百度~

MySQL的sql_mode解析与设置：（作者：cwwcn）

https://blog.csdn.net/ccccalculator/article/details/70432123

 

三、另外

如果不同的表join，字符集不同也会导致索引失效，请大家注意下！！！

MySQL表字段字符集不同导致的索引失效问题：（作者：scott）https://mp.weixin.qq.com/s/ns9eRxjXZfUPNSpfgGA7UA

 

菜鸡一只，继续努力！！

2020年的所有假期已经过完了，国庆回了趟家，感觉越发喜欢家乡的氛围和生活节奏，不过忙里偷闲可能才适合我把哈哈，继续努力把2020年，这对大多数人来说糟糕的一年过完，迎接明年的假期~