也议MySQL中隐式转换

1. 环境说明

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

RHEL 6.4 x86_64 + MySQL 5.6.19

测试表:

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

2. 数值类型(int)

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

select * from emp where empno='7788';

会产生隐式转换吗?


下面实验证明:

MySQL [test]> 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)

MySQL [test]> explain select * from emp where empno=7788;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | emp   | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

MySQL [test]> 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)

MySQL [test]> explain select * from emp where empno='7788';
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | emp   | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

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


注意:

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

MySQL [test]> select * from emp where empno='7788ab12';   ## 这个就相当于empno=7788,后面的ab12将被截断,并且不影响索引的使用
+-------+-------+---------+------+---------------------+------+------+--------+
| 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)

MySQL [test]> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '7788ab12' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

MySQL [test]> select * from emp where empno='ab7788';   ## 这个就相当于empno=0
Empty set (0.01 sec)

3. 字符类型(varchar)

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

select * from emp where ename=23456;
上面的SQL会不会出现隐式转换呢?


下面实验证明:

MySQL [test]> select * from emp where ename='23456';
+-------+-------+-----------+------+---------------------+------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
|  7936 | 23456 | BOSCO-DBA | 7788 | 2014-09-13 16:13:56 | 2450 |  800 |     10 |
+-------+-------+-----------+------+---------------------+------+------+--------+
1 row in set (0.00 sec)

MySQL [test]> explain select * from emp where ename='23456';   ## 正常来说,可以使用到索引idx_ename
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | emp   | ref  | idx_ename     | idx_ename | 47      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

MySQL [test]> select * from emp where ename=23456;   ## 当varchar类型不对时,仍然是可以查出结果
+-------+-------+-----------+------+---------------------+------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
|  7936 | 23456 | BOSCO-DBA | 7788 | 2014-09-13 16:13:56 | 2450 |  800 |     10 |
+-------+-------+-----------+------+---------------------+------+------+--------+
1 row in set, 14 warnings (0.00 sec)

MySQL [test]> explain select * from emp where ename=23456;   ## 当varchar类型不匹配时,索引无效了,选择了全表扫描
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | idx_ename     | NULL | NULL    | NULL |   15 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


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

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

-- Bosco  QQ:375612082

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


郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。