MySQL和Oracle中的隐式转换

查看建表语句,发现这个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 - Production