從Java的型別轉換看MySQL和Oracle中的隱式轉換(二)

說起資料型別轉換，在開發中如此，在資料庫中也是如此，之前簡單對比過MySQL和Oracle的資料型別轉換情況，可以參見MySQL和Oracle中的隱式轉換 http://blog.itpub.net/23718752/viewspace-1787973/
不過當時寫完之後，有個讀者隨口問了一句為什麼，為什麼呢？似乎自己還是一知半解，說是規則，無規矩不成方圓，倒也無可非議，不過我覺得還是要再看看，看看還能有哪些收穫，接下來的內容我就不能保證正確性了，希望大家明辨，也希望提出意見，畢竟就是希望把問題搞明白而已。

首先開發語言中就有資料型別的隱式轉換，這一點在java中尤為明顯，畢竟一個承載了太多使命的語言如此龐大，又是強型別語言，資料型別的轉換就是一個尤為重要的部分了。Java中的資料型別轉換主要有下面的規則。
//轉換規則：從儲存範圍小的型別到儲存範圍大的型別。
//具體規則為：byte→short(char)→int→long→float→double
自己也嘚瑟了一下，寫了個簡單的小程式以示明證，這個程式不能說明我會java.
public class Test {
public static void main(String args[]){
/*1*/    System.out.println("aa");
/*2*/    System.out.println('a');
/*3*/    byte a=10;
/*4*/     System.out.println(a);
/*5*/     char b='b';
/*6*/     int c=b;
/*7*/     System.out.println(b);
/*8*/     System.out.println(c);
    }
}
這個程式的輸出為
aa
a
10
b
98

這樣寫的目的就是，
第1行，第2行中的單引號，雙引號需要做的事情就是標示它是一個變數值，兩者的效果在這個時候是一致的。
第3行初始化了一個byte變數，然後輸出，這個時候還是byte
但是第5行宣告瞭一個char型變數，然後在第6行中做了型別的隱式轉換，在第7行中輸出為字元b，但是在第8行輸出為
透過這個簡單的例子可以發現確實資料型別做了隱式轉換，而且單引號，雙引號在這個例子中的作用是一致的，就是標示變數。
因為在Java中檢視資料型別的轉換代價還是相對要困難一些，我們可以在資料庫中來類比。
首先還是重複之前的測試，準備一批的資料。建立一個表，然後插入一些值。
create table test (id1 number,id2 varchar2(10));
 begin                   
    for i in 1..100 loop
    insert into test values(i,chr(39)||i||chr(39));
    end loop;
    commit;
    end;
    /
create index ind1_test on n1.test(id1);
create index ind2_test on n1.test(id2);
然後收集統計資訊。
exec dbms_stats.gather_table_stats('TEST','TEST',CASCADE=>TRUE);
這個時候檢視執行計劃
explain plan for select *from test where id1='2';
SQL>   select *from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2759464289
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND1_TEST |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
   2 - access("ID1"=2)
透過這個確實可以看到謂詞資訊的部分    2 - access("ID1"=2) 已經自動做了轉換，這個時候一個觸發了一個索引掃描。
但是這個過程還是看不出有資料型別轉換的痕跡，我們做一個看似有問題的例子，來觸發一下。儘管id1位int型，但是使用字元型來觸發。
SQL>    explain plan for select *from test where id1='A';
Explained.
SQL>   select *from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2759464289
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND1_TEST |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------
   2 - access("ID1"=TO_NUMBER('A'))
可以看到謂詞資訊已經發生了變化。   2 - access("ID1"=TO_NUMBER('A'))從這個地方我們可以看到確實觸發了一個to_number的操作。
而最佳化器在這個時候雖然觸發了，但是在sql執行的時候，就會報出錯誤，這個時候可以看到Oracle還是蠻嚴謹的。
SQL> select *from test where id1='A';
select *from test where id1='A'
                            *
ERROR at line 1:
ORA-01722: invalid number
而如果使用雙引號，生成執行計劃都會拋錯。
SQL> explain plan for select *from test where id1="A";
explain plan for select *from test where id1="A"
                                             *
ERROR at line 1:
ORA-00904: "A": invalid identifier
可見單引號和雙引號在Oracle代表的含義還是有很大差別。

我們來看看在MySQL中的表現。
還是建立一個簡單的表，插入一些資料。
> create table test (id1 int,id2 varchar(10));
> insert into test values(1,'1');
> insert into test values(2,'2');
> insert into test values(3,'3');
> commit;
> create index idx_id1 on test(id1);
> create index idx_id2 on test(id2);
這個時候生成執行計劃，可以發現走了索引
> 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 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)
而如果檢視id1為varchar的型別時，也走了索引。
> explain select * from test where id1='a';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | idx_id1       | idx_id1 | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)
差別更大的就是如果使用id1='a'，也能夠正常執行，只是沒有任何匹配的記錄。
> select * from test where id1='a';
Empty set (0.00 sec)
而如果由單引號改為雙引號，也能夠正常執行。
> select * from test where id1="a";
Empty set (0.00 sec)
而且雙引號的情況下，生成執行計劃也沒有問題。
> explain select * from test where id1="a";
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | idx_id1       | idx_id1 | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)
可以看出在MySQL中這個時候的範圍似乎更寬，在MySQL中不光用單引號，雙引號，而且還經常會看到·這種符號。
這種在MySQL中可以靈活宣告一些變化個，舉個不太恰當的例子，比如我們建立一個表，一個欄位為int，型別為int直接按照下面的方式來寫，肯定拋錯。
> create table test1(int int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int int)' at line 1
crea' at line 1
可以加上·，就可以識別了。
> create table test1(`int` int);
Query OK, 0 rows affected (0.00 sec)