也议MySQL中隐式转换

1. 环境说明

blog地址：http://blog.csdn.NET/hw_libo/article/details/39252427

RHEL 6.4 x86_64 + MySQL 5.6.19

测试表：

 

[sql]  view plain  copy

 

 print?

1. MySQL [test]> show create table emp\G  
2. *************************** 1. row ***************************  
3.        Table: emp  
4. Create Table: CREATE TABLE `emp` (  
5.   `EMPNO` int(11) NOT NULL,  
6.   `ENAME` varchar(15) NOT NULL,  
7.   `JOB` varchar(15) NOT NULL,  
8.   `MGR` int(11) DEFAULT '0',  
9.   `HIREDATE` timestamp NULL DEFAULT NULL,  
10.   `SAL` int(20) DEFAULT '0',  
11.   `COMM` int(11) DEFAULT '0',  
12.   `DEPTNO` int(11) NOT NULL,  
13.   PRIMARY KEY (`EMPNO`),  
14.   KEY `idx_deptno` (`DEPTNO`),  
15.   KEY `idx_sal` (`SAL`),  
16.   KEY `idx_comm` (`COMM`),  
17.   KEY `idx_ename` (`ENAME`)  
18. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
19. 1 row in set (0.00 sec)  

[sql]  view plain  copy

 

 print?

1. </pre><pre name="code" class="sql">MySQL [test]> select * from emp;  
2. +-------+--------+-----------+------+---------------------+------+------+--------+  
3. | EMPNO | ENAME  | JOB       | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |  
4. +-------+--------+-----------+------+---------------------+------+------+--------+  
5. |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800 | NULL |     20 |  
6. |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600 |  300 |     30 |  
7. |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250 |  500 |     30 |  
8. |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975 |    0 |     20 |  
9. |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |     30 |  
10. |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 |    0 |     30 |  
11. |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 |    0 |     10 |  
12. |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |  
13. |  7839 | KING   | PRESIDENT |    0 | 1981-11-17 00:00:00 | 5000 |    0 |     10 |  
14. |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30 |  
15. |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 |    0 |     20 |  
16. |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 |    0 |     30 |  
17. |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 |    0 |     20 |  
18. |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 |    0 |     10 |  
19. |  7936 | 23456  | BOSCO-DBA | 7788 | 2014-09-13 16:13:56 | 2450 |  800 |     10 |  
20. +-------+--------+-----------+------+---------------------+------+------+--------+  
21. 15 rows in set (0.00 sec)  

 

 

2. 数值类型（int）

首先提个问题，如上测试表emp中empno是主键，类型为int，那么：

 

[sql]  view plain  copy

 

 print?

1. select * from emp where empno='7788';  

 

会产生隐式转换吗？

 

下面实验证明：

 

[sql]  view plain  copy

 

 print?

1. MySQL [test]> select * from emp where empno=7788;  
2. +-------+-------+---------+------+---------------------+------+------+--------+  
3. | EMPNO | ENAME | JOB     | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |  
4. +-------+-------+---------+------+---------------------+------+------+--------+  
5. |  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |  
6. +-------+-------+---------+------+---------------------+------+------+--------+  
7. 1 row in set (0.00 sec)  
8.   
9. MySQL [test]> explain select * from emp where empno=7788;  
10. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+  
11. | id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |  
12. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+  
13. |  1 | SIMPLE      | emp   | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |  
14. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+  
15. 1 row in set (0.00 sec)  
16.   
17. MySQL [test]> select * from emp where empno='7788';  
18. +-------+-------+---------+------+---------------------+------+------+--------+  
19. | EMPNO | ENAME | JOB     | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |  
20. +-------+-------+---------+------+---------------------+------+------+--------+  
21. |  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |  
22. +-------+-------+---------+------+---------------------+------+------+--------+  
23. 1 row in set (0.00 sec)  
24.   
25. MySQL [test]> explain select * from emp where empno='7788';  
26. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+  
27. | id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |  
28. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+  
29. |  1 | SIMPLE      | emp   | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |  
30. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+  
31. 1 row in set (0.00 sec)  

可见，针对数据类型字段，即使类型不一致，并不影响是否使用索引，执行计划是一样的，不会产生隐式转换。但仍然建议在生产库中尽量避免出现这样的SQL。

 

 

注意：

数值类型有一种隐式转换，如果以数字开关的，后面的字符将被截断，只取前面的数字值，如果不以数字开关的将被置为0。如下：

 

[sql]  view plain  copy

 

 print?

1. MySQL [test]> select * from emp where empno='7788ab12';   ## 这个就相当于empno=7788，后面的ab12将被截断，并且不影响索引的使用  
2. +-------+-------+---------+------+---------------------+------+------+--------+  
3. | EMPNO | ENAME | JOB     | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |  
4. +-------+-------+---------+------+---------------------+------+------+--------+  
5. |  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |  
6. +-------+-------+---------+------+---------------------+------+------+--------+  
7. 1 row in set, 1 warning (0.00 sec)  
8.   
9. MySQL [test]> show warnings;  
10. +---------+------+----------------------------------------------+  
11. | Level   | Code | Message                                      |  
12. +---------+------+----------------------------------------------+  
13. | Warning | 1292 | Truncated incorrect DOUBLE value: '7788ab12' |  
14. +---------+------+----------------------------------------------+  
15. 1 row in set (0.00 sec)  
16.   
17. MySQL [test]> select * from emp where empno='ab7788';   ## 这个就相当于empno=0  
18. Empty set (0.01 sec)  

 

 

3. 字符类型（varchar）

 

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

 

[sql]  view plain  copy

 

 print?

1. select * from emp where ename=23456;  

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

 

 

下面实验证明：

 

[sql]  view plain  copy

 

 print?

1. MySQL [test]> select * from emp where ename='23456';  
2. +-------+-------+-----------+------+---------------------+------+------+--------+  
3. | EMPNO | ENAME | JOB       | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |  
4. +-------+-------+-----------+------+---------------------+------+------+--------+  
5. |  7936 | 23456 | BOSCO-DBA | 7788 | 2014-09-13 16:13:56 | 2450 |  800 |     10 |  
6. +-------+-------+-----------+------+---------------------+------+------+--------+  
7. 1 row in set (0.00 sec)  
8.   
9. MySQL [test]> explain select * from emp where ename='23456';   ## 正常来说，可以使用到索引idx_ename  
10. +----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+  
11. | id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                 |  
12. +----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+  
13. |  1 | SIMPLE      | emp   | ref  | idx_ename     | idx_ename | 47      | const |    1 | Using index condition |  
14. +----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+  
15. 1 row in set (0.00 sec)  

 

[sql]  view plain  copy

 

 print?

1. MySQL [test]> select * from emp where ename=23456;   ## 当varchar类型不对时，仍然是可以查出结果  
2. +-------+-------+-----------+------+---------------------+------+------+--------+  
3. | EMPNO | ENAME | JOB       | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |  
4. +-------+-------+-----------+------+---------------------+------+------+--------+  
5. |  7936 | 23456 | BOSCO-DBA | 7788 | 2014-09-13 16:13:56 | 2450 |  800 |     10 |  
6. +-------+-------+-----------+------+---------------------+------+------+--------+  
7. 1 row in set, 14 warnings (0.00 sec)  
8.   
9. MySQL [test]> explain select * from emp where ename=23456;   ## 当varchar类型不匹配时，索引无效了，选择了全表扫描  
10. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
11. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |  
12. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
13. |  1 | SIMPLE      | emp   | ALL  | idx_ename     | NULL | NULL    | NULL |   15 | Using where |  
14. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
15. 1 row in set (0.00 sec)  

 

 

可见，如果是字符类型，当出现类型不一致时，是会影响索引的使用的，会产生隐式转换的。

 

blog地址：http://blog.youkuaiyun.com/hw_libo/article/details/39252427

-- Bosco  QQ：375612082

---- END ----
-------------------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

转载于:https://www.cnblogs.com/Dennis-mi/articles/6089747.html