性能文章>因为一条诡异的 insert 语句引起的Mysql问题分析及排查过程!>

因为一条诡异的 insert 语句引起的Mysql问题分析及排查过程!转载

1月前
192002

导语

Mysql优化是各位工程师日常经常遇到的问题,本篇是一篇非常简单的mysql优化过程,着重介绍的是排查过程,优化方案一笔带过,非常适合初级开发者阅读!

 

正文

问题背景

有同事反馈在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. 保证连接字符集与表字符集一致。

 

请先登录,感受更多精彩内容
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

MySQL之KEY分区引发的血案
需求背景业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一。image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id:业务表t
代码优化日记 ——火焰图找问题代码
一、问题背景- 排序服务,用于推荐item分数预测,详细项目背景及排序请求执行逻辑可参考之前的一篇文章 :《[性能优化:线程资源回收](https://heapdump.cn/user/708
MySQL 死锁套路:一次诡异的批量插入死锁问题分析
线上最近出现了批量insert的死锁,百思不得解。死锁记录如下:```2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB: (1) TRANSACTI
MySQL 死锁套路:唯一索引下批量插入顺序不一致
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。先来做几个小实验,简化的表结构如下:```CREATE TABL
MySQL 死锁套路:再来看一例走不同索引更新的例子
前面有文章介绍了利用调试MySQL源码的方式来调试锁相关的信息,这里利用这个工具来解决一个比较简单的问题,线上的表字段较多,这里简单成为了一个表:```CREATE TABLE `t3` ( `id
如何在 Mac 下用 Clion 调试 MySQL 源码
前面写了几篇文章来通过调试 MySQL 源码来分析死锁问题,有读者问如何用 IDE 调试源码,这篇文章简单介绍一下如何在 Mac 下调试。之所以使用调试的方式来分析死锁问题是因为在解决 MySQL 死
再聊 TCP backlog
这篇文章我们以 backlog 参数来深入研究一下建连的过程。通过阅读这篇文章,你会了解到下面这些知识:- backlog、半连接队列、全连接队列是什么- linux 内核是如何计算半连接队列、全连接
掉坑了!GROUP_CONCAT函数引发的线上问题
本文分享一篇在工作遇到的一个问题,关于MySQL GROUP_CONCAT函数导致的问题。希望能帮忙到你。 业务场景在说遇到的坑之前,先描述一下大致的业务场景。系统有一个排班的功能,一个医生一天可以排