在日常的工作中经常会遇到客户反馈，针对一个等值查询，为什么查询出来的结果是错误的呢？而此刻我的内存独白是：一定是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)
表中的数据如下所示：
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中隐式转换导致查询结果错误及索引不可用
![脚本输入中如何消除mysql: [Warning] Using a password on the command line interface can be insecure.](http://www.seiang.com/wp-content/themes/git/timthumb.php?src=http://www.seiang.com/wp-content/uploads/2022/03/2022030207265426.png&h=110&w=185&q=90&zc=1&ct=1)
