MySQL和Oracle中的隐式转换

今天在处理一个问题的时候，需要根据其他部门提供的sql语句对一个表中的数据进行了筛查。 语句类似下面的形式gt; SELECT MAX_LE

今天在处理一个问题的时候，，需要根据其他部门提供的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)