mysql虛擬表

虛擬表，顧名思義，就是實際上並不存在（物理上不存在），但是邏輯上存在的表。這樣說很抽象，還是看一些實際的例子吧。

在mysql中，存在三種虛擬表：臨時表、記憶體表和檢視。檢視會單獨講，本節僅僅將臨時表和記憶體表。

一、mysql臨時表

　　1、什麼是臨時表

臨時表是建立在系統臨時資料夾中的表，如果使用得當，完全可以像普通表一樣進行各種操作。 臨時表的資料和表結構都儲存在記憶體之中，退出時，其所佔的空間會自動被釋放。

　　2、建立臨時表

         (1)定義欄位

      CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,    value INTEGER NOT NULL)    

     (2)直接將查詢結果匯入臨時表

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

3、查詢臨時表
select * from tmp_table

4、刪除臨時表
drop table tmp_table

二、臨時表的應用

當工作在十分大的表上執行時，在實際操作中你可能會需要執行很多的相關查詢，來獲的一個大量資料的小的子集。較好的辦法，不是對整個表執行這些查詢，而是讓MySQL每次找出所需的少數記錄，將記錄選擇到一個臨時表，然後對這些表執行查詢。

　　1）、當某一個SQL語句關聯的表在2張及以上，並且和一些小表關聯。可以採用將大表進行分拆並且得到比較小的結果集合存放在臨時表中。

　　2）、程式執行過程中可能需要存放一些臨時的資料，這些資料在整個程式的會話過程中都需要用的等等。

3）、臨時表預設的是MyISAM,但是可以修改。

4)、可以把一些經常訪問的資料放到臨時表中，這樣訪問時會快一些,因為資料是在伺服器記憶體中,另外每次查詢的時候，資料庫都需要生成一些臨時資料在臨時表裡

三、臨時表使用注意事項：

(1)臨時表只在當前連線可見，當這個連線關閉的時候，會自動drop。這就意味著你可以在兩個不同的連線裡使用相同的臨時表名，並且相互不會衝突，或者使用已經存在的表，但不是臨時表的表名。(當這個臨時表存在的時候，存在的表被隱藏了，如果臨時表被drop，存在的表就可見了)。

(2) 臨時表只能用在 memory,myisam,merge,或者innodb引擎。

（3）臨時表不支援mysql cluster(簇)。

（4）在同一個query語句中，你只能查詢一次臨時表。例如：下面的就不可用

　　mysql> SELECT * FROM temp_table, temp_table AS t2;

　　ERROR 1137: Can't reopen table: 'temp_table'

　　如果在一個儲存函式裡，你用不同的別名查詢一個臨時表多次，或者在這個儲存函式裡用不同的語句查詢，這個錯誤都會發生。

（5）show tables 語句不會列舉臨時表，但是會列出記憶體表。

（6）你不能用rename來重新命名一個臨時表。但是，你可以alter table代替：

　　mysql>ALTER TABLE orig_name RENAME new_name;

********************************************************************************************

四、記憶體表

1、記憶體表:表結構建在磁碟裡，資料在記憶體裡 ，當停止服務後，表中的資料丟失，而表的結構不會丟失。記憶體表也可以被看作是臨時表的一種。

2、記憶體表的建立：

CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL, value INTEGER NOT NULL ) TYPE = HEAP  

注意： TYPE = HEAP必須要有。

五、記憶體表的應用

記憶體表使用雜湊雜湊索引把資料儲存在記憶體中，因此具有極快的速度，適合快取中小型資料庫。

1、heap對所有使用者的連線是可見的，這使得它非常適合做快取。 
2、一旦伺服器重啟，所有heap表資料丟失，但是heap表結構仍然存在，因為heap表結構是存放在實際資料庫路徑下的，不會自動刪除。重啟之後，heap將被清空，這時候對heap的查詢結果都是空的。 
3、如果heap是複製的某資料表（建立普通使用者表這些也會丟失），則複製之後所有主鍵、索引、自增等格式將不復存在，需要重新新增主鍵和索引，如果需要的話。 
4、對於重啟造成的資料丟失，有以下的解決辦法： 
　a、在任何查詢之前，執行一次簡單的查詢，判斷heap表是否存在資料，如果不存在，則把資料重新寫入，或者DROP表重新複製某張表。這需要多做一次查詢。不過可以寫成include檔案，在需要用該heap表的頁面隨時呼叫，比較方便。 
　b、對於需要該heap表的頁面，在該頁面第一次且僅在第一次查詢該表時，對資料集結果進行判斷，如果結果為空，則需要重新寫入資料。這樣可以節省一次查詢。 
　c、更好的辦法是在mysql每次重新啟動時自動寫入資料到heap，但是需要配置伺服器，過程比較複雜，通用性受到限制。

六、記憶體表的注意事項

1、heap不允許使用xxxTEXT和xxxBLOB資料型別；只允許使用=和<=>操作符來搜尋記錄（不允 許& amp; lt;、>、<=或>=）；mysql4.1版本之前不支援auto_increment；只允許對非空資料列進行索引（not null）。

注：操作符 “<=>” 說明：NULL-safe equal.這個操作符和“=”操作符執行相同的比較操作，不過在兩個操作碼均為NULL時，其所得值為1而不為NULL，而當一個操作碼為NULL時，其所得值為0而不為NULL。 
2、記憶體表可以通過max_heap_table_size = 2048M來加大使用的記憶體。

3、記憶體表必須使用memory儲存引擎