分析MySQL中隐式转换导致查询结果错误及索引不可用

原文作者： SEian.G
原文链接：http://www.seiang.com/?p=387
在日常的工作中经常会遇到客户反馈，针对一个等值查询，为什么查询出来的结果是错误的呢？而此刻我的内存独白是：一定是sql语句写的有问题呗，找我干啥？当然了，这也就是开玩笑，客户是上帝啊，客户虐我千万遍，我待客户如初恋！接下来肯定就是收集相关的信息，比如建表语句，SQL语句，查询结果等；

下面针对客户所反馈的情况，我们去动手实验一下；

MySQL中隐式转换详细查看官方文档相关的说明：

https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

环境说明：

MYSQL 5.7

测试表：

表中的数据如下所示：
分析MySQL中隐式转换导致查询结果错误及索引不可用

1、过滤字段为数值类型（int）
在如上测试表emp中empno是主键，类型为int，那么：

select * from emp where empno=’7788′;

会产生隐式转换吗？

下面通过实验证明：

通过上述的测试结果可以发现，针对数据类型字段，即使类型不一致，并不影响是否使用索引，执行计划是一样的，不会产生隐式转换。但仍然建议在开发程序和生产库中尽量避免出现这样的SQL。

注意：

在过滤字段为数值类型的时候，数值类型有一种隐式转换，如果以数字开头的，包含有字符，后面的字符将被截断，只取前面的数字值，如果不以数字开关的将被置为0。

测试如下：

##这个就相当于empno=7788，后面的wjq123将被截断，但是并且不影响索引的使用，如下是执行计划：

2、过滤字段为字符类型（varchar）
针对测试表emp中的ename字段（varchar类型），上面有一辅助索引idx_ename，并且ename中有两个值是全数字的，若有这样的查询：

select * from emp where ename=123456;

上面的SQL会不会出现隐式转换呢？

下面实验证明：

从上面的测试结果可以看出，当过滤的字段是字符类型的时候，没有使用到索引，走的全表扫描；

下面接着

当varchar类型不对时，仍然是可以查出结果，后面的wjq5被截断，无法使用索引，查询的结果也是错误的

我们用数值型的12和ename进行比较的时候，不仅无法利用索引，同时查询出来的结果也是错误的，

之所以上述查看有结果，是因为MySQL针对12wjq5的值进行了转化，变成了12；

通过上述的测试，如果是字符类型，当出现类型不一致时，是会影响索引的使用的，会产生隐式转换的，并且查询出来的结果很有可能是错误的。

如下是官方文档说明：
For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly.

3、过滤字段为浮点类型（float或double）
Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:
如果查询过滤中使用了浮点型，那么比较会是近似的，将导致结果看起来不一致，也就是可能导致查询结果错误。

通过上面的结果可以发现，字符串的’180153763202434582′ 和 数值的180153763202434585比较结果竟然是相等的。

我们再看下字符串’180153763202434582′ 和字符串’180153763202434585′ 转化为浮点型的结果

我们发现，将两个不同的字符串转化为浮点数后，结果是一样的，

所以只要是转化为浮点数之后的值是相等的，那么，经过隐式转化后的比较也会相等，我们继续进行测试其他转化为浮点型相等的字符串的结果

字符串’180153763202434589’和’180153763202434594’转化为浮点型结果一样，我们看下他们和数值的比较结果

结果也是符合预期的。因此，当MySQL遇到字段类型不匹配的时候，会进行各种隐式转化，一定要小心，有可能导致精度丢失。

总结
不管是Oracle还是MySQL，在数据库中进行查询的时候，在查询过滤的时候，过滤条件一定要注意字段类型，杜绝隐式转化，这样不仅会导致查询缓慢，还会导致结果错误，这是生产业务所不能接受的；

欢迎关注：