亲宝软件园·资讯

展开

Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题

Jeff、yuan 人气:0

背景

下面我们来看一下执行的结果

在这里插入图片描述

在上面的描述中我们还得注意就是,你的where条件的字符串不加单引号必须是全数字。不然就会报错

在这里插入图片描述

还有可能查出来的数据不是我们想要的数据。如下图

在这里插入图片描述

分析

  1. 从执行结果来看,使用了单引号的走了对应的索引。没有使用单引号的没有走索引,进行了全表扫描。
  2. 为什么会这样呢? mysql的优化器怎么不直接进行类型转换呢?

如上图所述:

1054 - Unknown column '000w1993521' in 'where clause', Time: 0.008000s

我们先来看一下一条SQL的执行过程

在这里插入图片描述

(网图)

隐式转换

1. 产生条件
当操作符与不同类型的操作数一起使用时,会发生类型转换以使操作数兼容。则会发生转换隐式
发生隐式转换的条件:

  1. 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
  2. 两个参数都是字符串,会按照字符串来比较,不做类型转换
  3. 两个参数都是整数,按照整数来比较,不做类型转换
  4. 十六进制的值和非数字做比较时,会被当做二进制串
  5. 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
  6. 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
  7. 所有其他情况下,两个参数都会被转换为浮点数再进行比较

2. 分析实际遇到的情况

1.那我们也就清楚了,上面我提出的例子是整数和字符串的比较,那就属于其他情况了。那我们就先来分析一下索引失效的原因

2.查询出不匹配的值(或者说是部分匹配的值),如上面的查询结果。这真得看看源码了,这也就是MYsql的隐式转换规则。这里不就细分析了(因为没有查到相关的文档)
由于历史原因,需要兼容旧的设计,可以使用 MySQL 的类型转换函数 cast 和 convert,来明确的进行转换。
总结

参考
https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html
https://xiaomi-info.github.io/2019/12/24/mysql-implicit-conversion/
https://zhuanlan.zhihu.com/p/95170837

加载全部内容

相关教程
猜你喜欢
用户评论