MySQL和Oracle中的隐式转换

今天在处理一个问题的时候，需要根据其他部门提供的sql语句对一个表中的数据进行了筛查。
语句类似下面的形式
> SELECT MAX_LEVEL,LOGOUT_TIME,CURRENT_DATE AS NOWTIME,cn_master FROM t_test_october_back_a WHERE ID in （ 100, 200, 300, 400, 500） ;
+-----------+---------------+------------+-----------+
| MAX_LEVEL | LOGOUT_TIME | NOWTIME | ID|
+-----------+---------------+------------+-----------+
| 1 | 1440407918000 | 2015-08-31 | 100|
| 100| 1441009281000 | 2015-08-31 | 200|
| 1 | 1440408002000 | 2015-08-31 | 300|
+-----------+---------------+------------+-----------+
x rows in set, 65535 warnings （10.98 sec）
本来这一个简单查询就完成了，也得到了业务部门需要的数据情况，但是查看最后一行的内容，还是有些蹊跷。如果观察仔细，对于这种id的数据查询，走索引的话，绝对不会再10秒左右，这是第一个奇怪的地方，第二个奇怪的地方就是65535 warnings，一个简单查询怎么会有这么多的warnings
最开始怕show warnings的时候会一下子显示出来6万多行数据，还小小担心了一下，结果输出的结果只有64行。
>show warnings;
+---------+------+----------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: "3506996@abc.com" |
| Warning | 1292 | Truncated incorrect DOUBLE value: "28366@abc.com" |
| Warning | 1292 | Truncated incorrect DOUBLE value: "81700700@abc.com" |
| Warning | 1292 | Truncated incorrect DOUBLE value: "391112900@abc.com" |
| Warning | 1292 | Truncated incorrect DOUBLE value: "867964771@abc.com" |
...
64 rows in set （0.00 sec）
查看建表语句,发现这个id列是varchar类型的。
>show create table t_test_october_back_a;
| t_tl_october_back_a | CREATE TABLE `t_tl_october_back_a` （
`id` bigint（20） NOT NULL AUTO_INCREMENT,
`id` varchar（60） NOT NULL,
`area_server` varchar（80） NOT NULL,
`max_level` tinyint（4） NOT NULL,
`logout_time` bigint（20） NOT NULL,
PRIMARY KEY （`id`）,
UNIQUE KEY `idx_test_october_back_cn_master` （`id`）
） ENGINE=InnoDB AUTO_INCREMENT=2042252493 DEFAULT CHARSET=gbk |我们就顺着错误信息来看看，把警告中的部分直接作为参数,发现查询的时间极快。
>select *from t_tl_october_back_a where cn_master ="867964771@abc.com";
+-------+-------------------------+---------------------------+-----------+---------------+
| id | cn_master | area_server | max_level | logout_time |
+-------+-------------------------+---------------------------+-----------+---------------+
| 18723 | 867964771@abc.com | abcd-abcd | 104 | 1434446979000 |
+-------+-------------------------+---------------------------+-----------+---------------+
1 row in set （0.00 sec）
对此我们来通过执行计划来简单对比一下。
如果使用数字的方式，会走全表扫描
> explain SELECT MAX_LEVEL,LOGOUT_TIME FROM t_tl_october_back_a WHERE CN_MASTER in （ 100, 200）;
+----+-------------+---------------------+------+-------------------------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+------+-------------------------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | t_test_october_back_a | ALL | idx_test_october_back_cn_master | NULL | NULL | NULL | 28597841 | Using where |
+----+-------------+---------------------+------+-------------------------------+------+---------+------+----------+-------------+
1 row in set （0.00 sec）
而如果使用字符的形式，执行计划就会走索引，执行效果就是预期的样子。
>explain SELECT MAX_LEVEL,LOGOUT_TIME FROM t_tl_october_back_a WHERE CN_MASTER = "100" ;
+----+-------------+---------------------+-------+-------------------------------+-------------------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------+-------------------------------+-------------------------------+---------+-------+------+-------+
| 1 | SIMPLE | t_test_october_back_a | const | idx_test_october_back_cn_master | idx_test_october_back_cn_master | 122 | const | 1 | |
+----+-------------+---------------------+-------+-------------------------------+-------------------------------+---------+-------+------+-------+
1 row in set （0.00 sec）
通过上面的例子可以看到，在查询的时候抛出的警告，其实就是在做类型转换的时候本来输出的是数字类型，就会尝试做隐式转换，而那个65535只是一个最大限制而已，表中的数据其实已经远远超过千万。
这个时候我们大体感受到了隐式转换在MySQL中的一些影响，我们来看看在MySQL 5.6和Oracle中的表现如何。
###############
MYSQL 5.6
mysql> select version（）;
+-------------------------------------------+
| version（） |
+-------------------------------------------+
| 5.6.23-enterprise-commercial-advanced-log |
+-------------------------------------------+
mysql> create table test （id1 int,id2 varchar（10））;
mysql> insert into test values（1,"1"）;
mysql> insert into test values（2,"2"）;
mysql> insert into test values（3,"3"）;
mysql> commit;
mysql> create index idx_id1 on test（id1）;
mysql> create index idx_id2 on test（id2）;
隐式转换，有数字转换为字符的时候，直接走了索引扫描
mysql> explain select * from test where id1="1";
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | test | ref | idx_id1 | idx_id1 | 5 | const | 1 | NULL |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
1 row in set （0.01 sec）隐式转换，由字符转换为数字的时候，直接走了全表扫描
mysql> explain select *from test where id2=2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | idx_id2 | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set （0.00 sec）
###############
Oracle 11gR2 sqlplus -v
SQL*Plus: Release 10.2.0.3.0 - ProductionSQL> create table test （id1 number,id2 varchar2（10））;
SQL> insert into test values（1,"1"）;
SQL> begin
2 for i in 1..100 loop
3 insert into test values（i,chr（39）||i||chr（39））;
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats（"TEST","TEST",CASCADE=>TRUE）;
隐式转换，由数字转换为字符的时候，直接走了索引扫描
SQL> explain plan for select *from test where id1="2";
SQL> select *from table（dbms_xplan.display）;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3847161316
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 （0）| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 7 | 2 （0）| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID1 | 1 | | 1 （0）| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information （identified by operation id）:
PLAN_TABLE_OUTPUT
2 - access（"ID1"=2）隐式转换，由字符转换为数字的时候，直接走了全表扫??
SQL> explain plan for select *from test where id2=3;
SQL> select *from table（dbms_xplan.display）;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 （0）| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 7 | 3 （0）| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information （identified by operation id）:
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter（TO_NUMBER（"ID2"）=3）可见在这个方面MySQL和Oracle中的表现是一致的，对于这种隐式转换还是要多加注意。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址