也议MySQL中隐式转换 - 数据库编程

-----+-------+---------+------+---------------------+------+------+--------+ | 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 |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+----------

也议MySQL中隐式转换(二)

3. 字符类型（varchar）