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)
首先提個問題，如上測試表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)
同樣，針對測試表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 ----
-------------------------------------------------------------------------------------------------------
版權所有，文章允許轉載，但必須以鏈接方式注明源地址，否則追究法律責任!
