• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

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

MySQL SEian.G 2年前 (2019-03-10) 528次浏览 已收录 0个评论
文章目录[隐藏]

在日常的工作中经常会遇到客户反馈,针对一个等值查询,为什么查询出来的结果是错误的呢?而此刻我的内存独白是:一定是sql语句写的有问题呗,找我干啥?当然了,这也就是开玩笑,客户是上帝啊,客户虐我千万遍,我待客户如初恋!接下来肯定就是收集相关的信息,比如建表语句,SQL语句,查询结果等;

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

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

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

环境说明:

MYSQL 5.7

测试表:

root@localhost [wjq]>show create table emp\G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `EMPNO` int(11) NOT NULL,
  `ENAME` varchar(15) NOT NULL,
  `JOB` varchar(15) NOT NULL,
  `MGR` int(11) DEFAULT '0',
  `HIREDATE` timestamp NULL DEFAULT NULL,
  `SAL` int(20) DEFAULT '0',
  `COMM` int(11) DEFAULT '0',
  `DEPTNO` int(11) NOT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY `idx_deptno` (`DEPTNO`),
  KEY `idx_sal` (`SAL`),
  KEY `idx_comm` (`COMM`),
  KEY `idx_ename` (`ENAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

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

1、过滤字段为数值类型(int)

在如上测试表emp中empno是主键,类型为int,那么:

select * from emp where empno=’7788′;

会产生隐式转换吗?

下面通过实验证明:

root@localhost [wjq]>select * from emp where empno=7788;
+-------+-------+---------+------+---------------------+------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+-------+---------+------+---------------------+------+------+--------+
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |
+-------+-------+---------+------+---------------------+------+------+--------+
1 row in set (0.00 sec)

root@localhost [wjq]>explain select * from emp where empno=7788; 
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

root@localhost [wjq]>select * from emp where empno='7788';
+-------+-------+---------+------+---------------------+------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+-------+---------+------+---------------------+------+------+--------+
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |
+-------+-------+---------+------+---------------------+------+------+--------+
1 row in set (0.00 sec)

root@localhost [wjq]>explain select * from emp where empno='7788';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

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

注意:

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

测试如下:

root@localhost [wjq]>select * from emp where empno='7788wjq123';
+-------+-------+---------+------+---------------------+------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+-------+---------+------+---------------------+------+------+--------+
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |
+-------+-------+---------+------+---------------------+------+------+--------+
1 row in set, 1 warning (0.00 sec)

root@localhost [wjq]>show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '7788wjq123' |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

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

root@localhost [wjq]>explain select * from emp where empno='7788wjq123';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

root@localhost [wjq]> SELECT '7788wjq123'=7788;
+-------------------+
| '7788wjq123'=7788 |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [wjq]>SELECT CAST('7788wjq123' AS SIGNED)=7788;
+-----------------------------------+
| CAST('7788wjq123' AS SIGNED)=7788 |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

下面的这个就相当于empno=0
root@localhost [wjq]>select * from emp where empno='wjq7788';
Empty set (0.00 sec)

root@localhost [wjq]>select 'wjq7788'=7788;
+----------------+
| 'wjq7788'=7788 |
+----------------+
|              0 |
+----------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [wjq]>SELECT CAST('wjq7788' AS SIGNED)=7788;       
+--------------------------------+
| CAST('wjq7788' AS SIGNED)=7788 |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

2、过滤字段为字符类型(varchar)

针对测试表emp中的ename字段(varchar类型),上面有一辅助索引idx_ename,并且ename中有两个值是全数字的,若有这样的查询:

select * from emp where ename=123456;

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

下面实验证明:

root@localhost [wjq]>select * from emp where ename=123456;
+-------+--------+-----+------+---------------------+------+------+--------+
| EMPNO | ENAME  | JOB | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+--------+-----+------+---------------------+------+------+--------+
|  7936 | 123456 | DBA | 7788 | 2019-03-08 16:13:56 | 1230 |    0 |     10 |
+-------+--------+-----+------+---------------------+------+------+--------+
1 row in set, 16 warnings (0.00 sec)

root@localhost [wjq]>show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'SMITH'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ALLEN'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'WARD'   |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'JONES'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'MARTIN' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'BLAKE'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'CLARK'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'SCOTT'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'KING'   |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'TURNER' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ADAMS'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'JAMES'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'FORD'   |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'MILLER' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'wjq345' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '12wjq5' |
+---------+------+--------------------------------------------+
16 rows in set (0.00 sec)

root@localhost [wjq]>explain select * from emp where ename=123456;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_ename     | NULL | NULL    | NULL |   18 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

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

下面接着

root@localhost [wjq]>select * from emp where ename=12;
+-------+--------+-----+------+---------------------+------+------+--------+
| EMPNO | ENAME  | JOB | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+--------+-----+------+---------------------+------+------+--------+
|  7939 | 12wjq5 | DBA | 7788 | 2019-03-08 16:13:56 | 2567 |    0 |     30 |
+-------+--------+-----+------+---------------------+------+------+--------+
1 row in set, 16 warnings (0.00 sec)

root@localhost [wjq]>
root@localhost [wjq]>explain select * from emp where ename=12;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_ename     | NULL | NULL    | NULL |   18 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

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

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

root@localhost [wjq]>show warnings\G;
*************************** 1. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use ref access on index 'idx_ename' due to type or collation conversion on field 'ENAME'
*************************** 2. row ***************************
  Level: Warning
   Code: 1739
Message: Cannot use range access on index 'idx_ename' due to type or collation conversion on field 'ENAME'
*************************** 3. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `wjq`.`emp`.`EMPNO` AS `EMPNO`,`wjq`.`emp`.`ENAME` AS `ENAME`,`wjq`.`emp`.`JOB` AS `JOB`,`wjq`.`emp`.`MGR` AS `MGR`,`wjq`.`emp`.`HIREDATE` AS `HIREDATE`,`wjq`.`emp`.`SAL` AS `SAL`,`wjq`.`emp`.`COMM` AS `COMM`,`wjq`.`emp`.`DEPTNO` AS `DEPTNO` from `wjq`.`emp` where (`wjq`.`emp`.`ENAME` = 12)
3 rows in set (0.00 sec)


root@localhost [wjq]> SELECT CAST('12' AS SIGNED)=12;  
+-------------------------+
| CAST('12' AS SIGNED)=12 |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

root@localhost [wjq]> SELECT CAST('12wjq5' AS SIGNED)=12;
+-----------------------------+
| CAST('12wjq5' AS SIGNED)=12 |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

之所以上述查看有结果,是因为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:
如果查询过滤中使用了浮点型,那么比较会是近似的,将导致结果看起来不一致,也就是可能导致查询结果错误。

root@localhost [wjq]>SELECT '180153763202434582' = 180153763202434582;
+-------------------------------------------+
| '180153763202434582' = 180153763202434582 |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [wjq]>SELECT '180153763202434582' = 180153763202434585;
+-------------------------------------------+
| '180153763202434582' = 180153763202434585 |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

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

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

root@localhost [wjq]>select '180153763202434582'+0.0;
+--------------------------+
| '180153763202434582'+0.0 |
+--------------------------+
|     1.801537632024346e17 |
+--------------------------+
1 row in set (0.00 sec)

root@localhost [wjq]>select '180153763202434585'+0.0; 
+--------------------------+
| '180153763202434585'+0.0 |
+--------------------------+
|     1.801537632024346e17 |
+--------------------------+
1 row in set (0.00 sec)

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

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

root@localhost [wjq]>SELECT '180153763202434589'+0.0;
+--------------------------+
| '180153763202434589'+0.0 |
+--------------------------+
|     1.801537632024346e17 |
+--------------------------+
1 row in set (0.00 sec)

root@localhost [wjq]>SELECT '180153763202434594'+0.0;  
+--------------------------+
| '180153763202434594'+0.0 |
+--------------------------+
|     1.801537632024346e17 |
+--------------------------+
1 row in set (0.00 sec)

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

root@localhost [wjq]>SELECT '180153763202434589' = 180153763202434585; 
+-------------------------------------------+
| '180153763202434589' = 180153763202434585 |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [wjq]>SELECT '180153763202434594' = 180153763202434585;  
+-------------------------------------------+
| '180153763202434594' = 180153763202434585 |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

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

总结

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

 

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


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:分析MySQL中隐式转换导致查询结果错误及索引不可用
喜欢 (3)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址