Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的解决方法

共计 2019 个字符，预计需要花费 6 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章主要介绍了 Mysql 5.6 隐式转换 导致的索引失效和数据不准确的解决方法，具有一定借鉴价值，需要的朋友可以参考下。希望大家阅读完这篇文章后大有收获。下面让丸趣 TV 小编带着大家一起了解一下。

背景

在一次进行 SQl 查询时，我试着对 where 条件中 vachar 类型的字段去掉单引号查询，这个时候发现这条本应该很快的语句竟然很慢。这个 varchar 字段有一个复合索引。其中的总条数有 58989，甚至不加单引号查出来的数据不是我们想要的数据。使用的是 mysql 5.6 版本，innoDB 引擎 实际情况如下

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

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

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

分析

从执行结果来看，使用了单引号的走了对应的索引。没有使用单引号的没有走索引，进行了全表扫描。为什么会这样呢？mysql 的优化器怎么不直接进行类型转换呢？在 SQL 语句中单引号的引入也就是代表这个类型是字符串数据类型 CHAR，VARCHAR，BINARY，VARBINARY，BLOB，TEXT，ENUM，和 SET。。不加单引号也就代表这是一个字符串之外的类型，如 int，bigDecimal 类型等如果给一串有字幕和特殊符号的字符串不加单引号，后果就是类型转换失败导致 SQl 不能执行。

如上图所述：

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

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

（网图）

我们先得出结论：如果对索引字段做函数操作（本例是 cast 函数做了隐式的转换），可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。（https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html）[外链图片转存失败, 源站可能有防盗链机制, 建议将图片保存下来直接上传 (img-l5AwT0xu-1607244327891)(http://note.youdao.com/yws/res/23689/CE6F785994E6476D816B23787CE65217)] 意思也就是：请注意，如果您使用 BINARY，CAST（）或 CONVERT（）转换索引列，则 MySQL 可能无法有效使用索引。查出来的数据不准确，也是因为隐式转换，转换后导致数值类型不一样，导致不等变为相等。

隐式转换

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

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

2. 分析实际遇到的情况

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

由于属于隐式转换的其他情况，所以对比值都得转换为浮点数进行比较我们先将查询条件值进行转换为浮点数，再着将表的记录值也得进行转换，所以这个时候此前已经创建好的索引排序已经不能生效了。因为隐式转换（函数）已经改变了原来的值，所以说优化器在这里就直接不选用索引，直接使用全表扫描。

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

隐式转换和函数的使用会导致索引失效和 select 出的数据不准确隐式转换的发生条件以及规则隐式转换导致索引失效的具体原因，由于需要将对比值都要进行类型转换导致失效。避免发生隐式类型转换，隐式转换的类型主要有字段类型不一致、in 参数包含多个类型、字符集类型或校对规则不一致等

感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享 Mysql 5.6 隐式转换 导致的索引失效和数据不准确的解决方法内容对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，遇到问题就找丸趣 TV，详细的解决方法等着你来学习!

丸趣 TV 网 – 提供最优质的资源集合！