原文作者: 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,在数据库中进行查询的时候,在查询过滤的时候,过滤条件一定要注意字段类型,杜绝隐式转化,这样不仅会导致查询缓慢,还会导致结果错误,这是生产业务所不能接受的;
欢迎关注: