也議MySQL中隱式轉換

標籤：隱式轉換   mysql隱式轉換   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: empCreate 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=utf81 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=0Empty 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 ----
-------------------------------------------------------------------------------------------------------
著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

也議MySQL中隱式轉換