【179期】一条诡异的 insert 语句，问题分析及排查过程！

点击上方“Java精选”，选择“设为星标”

别问别人为什么，多问自己凭什么！

下方有惊喜，留言必回，有问必答！

每天 08:35 更新文章，每天进步一点点...

问题背景

有同事反馈在mysql上面执行一条普通的insert语句，结果报错，

execute failed due to >>> Incorrect string value: '\xA1;offl...' for column 'biz_info' at row 1

经过半天的折腾，终于搞清楚了来龙去脉，这里简单给大家分享下。为了方便说明，我将测试例子中的表和语句简化，但不影响问题重现。

问题复现

连接字符集：UTF8

表结构：

CREATE TABLE `ggg` (
  `id` int(11) DEFAULT NULL,
  `c` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
root@test 06:13:48>insert into ggg values(1,concat('cardName:校园网',char(59),'offlineCardType:campus'));
Query OK, 1 row affected, 1 warning (2.51 sec)
root@test 06:14:36>show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1366
Message: Incorrect string value: '\x91;offl...' for column 'c' at row 1 

查看结果

root@test 06:16:06>select * from ggg where id=1;
*************************** 1. row ***************************
id: 1
c: cardName:鏍″洯缃

问题分析

从报错的结果来看，感觉是字符集转换引起的问题，而且由于连接串的字符集是UTF8，表的字符集是GBK，更容易引起怀疑。但是，即使是字符集转换，也不应该导致插入报错，因为语句中的中文字符“校园网"都是普通汉字，UTF8->GBK不应该存在问题。

那我们在回过头来看看insert语句，唯一特殊的是使用了concat和char两个函数。会不会跟这两个函数有关系？char(59)实际是字符“;”，为了验证想法，做了两个实验：

将char(59)替换成';'

insert into ggg values(1,concat('cardName:校园网',';','offlineCardType:campus'));

设置连接串字符集为GBK

insert into ggg values(1,concat('cardName:校园网',char(59),'offlineCardType:campus'));

果然，两种情况执行结果都是OK的，查询结果如下：

root@test 09:22:32>select * from ggg\G
*************************** 1. row ***************************
id: 1
c: cardName:鏍″洯缃
*************************** 2. row ***************************
id: 1
c: cardName:校园网;offlineCardType:campus
*************************** 3. row ***************************
id: 1
c: cardName:校园网;offlineCardType:campus

跟踪了下源代码，找到了原因。char()函数返回的是一个binary类型字符串，在进行concat时，会导致'cardName:校园网'字符串到binary的转换。

转换前，mysql将字符串‘cardName：校园网’看作是9个英文字符和3个汉字字符；转换后，mysql将其看作是18个字节的二进制串，其中，UTF8字符集的三个汉字“校园网”占了9个字节。另外，面试题资料推荐公众号Java精选，回复Java面试，获取最新面试题资料，支持在线随时随地刷题。

由于目标表字符集是GBK，因此在入库时，还会发生一次binary到GBK的转码，“校园网”的二级制编码是E6A0A1 E59BAD E58DA1，在转码过程中，由于GBK字符集只包含一个字节(编码值<128)和二个字节的字符(汉字和特殊字符)，“校园网”的二进制串会按照两个字节拆分E6A0 A1E5 9BAD E58D A1，前面四个变为“鏍″洯缃”，解析到A1时，由于A1既不是单字节字符，又不能与后面的字节组成一个合法的GBK字符，导致转换出错。

现在就很好解释为啥改变语句后，两种情况都OK了。第一种情况，将char(59)直接替换成‘;’，由于不涉及UF8到binary的转换，只有utf8到gbk转码的过程，这个转换是OK的，不会出现乱码；第二种情况，将连接串的字符集设置为GBK，那么会涉及GBK到binary的转换，然后再从binary转换到GBK，由于整个转换过程并没有二进制数据丢失，所以也是OK的。

问题产生的两个关键点

1. 连接字符集与表字符集不匹配；

2. 使用了char函数。

解决办法

1. char函数提供了using语法来实现返回特定字符集的字符串，比如：char(59 using utf8)；

2. 保证连接字符集与表字符集一致。

作者：天士梦

https://www.cnblogs.com/cchust/p/4601536.html

公众号“Java精选”所发表内容注明来源的，版权归原出处所有（无法查证版权的或者未注明出处的均来自网络，系转载，转载的目的在于传递更多信息，版权属于原作者。如有侵权，请联系，笔者会第一时间删除处理！

------ THE END ------

精品资料，超赞福利！

3000+ 道面试题在线刷，最新、最全 Java 面试题！

期往精选  点击标题可跳转

技术交流群！

最近有很多人问，有没有读者&异性交流群，你懂的！想知道如何加入。加入方式很简单，有兴趣的同学，只需要点击下方卡片，回复“加群”，即可免费加入交流群！

文章有帮助的话，在看，转发吧！