程序博客网 > 淘宝试用网

也议MySQL中隐式转换

来源：互联网 发布：淘宝试用网 编辑：程序博客网 时间：2024/05/08 17:50

http://blog.csdn.net/hw_libo/article/details/39252427


版权声明：本文为博主原创文章，未经博主允许不得转载。

目录(?)[+]

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?在CODE上查看代码片派生到我的代码片
  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?在CODE上查看代码片派生到我的代码片
  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?在CODE上查看代码片派生到我的代码片
  1. select * from emp where empno='7788';  

会产生隐式转换吗？


下面实验证明：

[sql] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  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?在CODE上查看代码片派生到我的代码片
  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?在CODE上查看代码片派生到我的代码片
  1. select * from emp where ename=23456;  
上面的SQL会不会出现隐式转换呢？


下面实验证明：

[sql] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  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?在CODE上查看代码片派生到我的代码片
  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.csdn.net/hw_libo/article/details/39252427

-- Bosco  QQ：375612082

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


0 0
淘宝试用网 淘宝试用网
原创粉丝点击
热门IT博客
linux发行版本有哪些linux发行版本有哪些
怎么编辑淘宝宝贝详情怎么编辑淘宝宝贝详情
微信小程序需要域名吗
网络营销策划论文
淘宝上靠谱的汉服店铺
安卓免流量软件.apk
mac jenkins 安装路径
realplayer软件功能
淘宝卖的人参是真的吗
mac玩ios手机游戏
淘宝差评回复大全
开发java项目银行系统
大数据培训机构排名
迪优美特网络机顶盒价格
淘宝联盟什么时候返
淘宝包装箱的加工流程
ubuntu 14.04内核
python 定时循环执行
淘宝补偿五元怎么使用
上海关键词seo公司
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 歌曲半壶纱 半壶沙 半壶纱歌词的寓意 半壶纱简谱古筝详细指法 半壶纱歌词是什么意思 半壶纱舞蹈教学古典舞 瑜伽舞韵半壶纱 半壶纱 歌曲 半壶纱舞蹈分解动作 半壶纱舞蹈动作解析 半壶纱是哪部电视主题曲 半壶紫砂壶 半壶纱的寓意是什么 半壶纱歌词完整 半手工壶 半壶纱古筝d调曲谱 舞蹈半壶纱古典舞 半壶纱适合什么人听 半壶纱吉他谱 半壶纱古筝曲谱带指法 半壶老酒歌词 舞韵瑜伽半壶纱 半壶纱表达了什么爱 沙壶球 顾涛紫沙壶 紫沙壶的保养方法 半壸纱 半壶砂 半壶水 半壶 半壶纱伴奏 半壶纱mp3下载 半壶纱下载 半壶纱什么意思 半壶纱图片 秦戈半夏 半夏曲的功效与作用 生旱半夏价格 半夏露颗粒 半夏露颗粒多少钱一盒 半夏露颗粒适合哪种咳嗽

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里