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