MySQL 臨時表

MySQL 臨時表在我們需要儲存一些臨時資料時是非常有用的。臨時表只在當前連線可見，當關閉連線時，Mysql會自動刪除表並釋放所有空間。

臨時表在MySQL 3.23版本中新增，如果你的MySQL版本低於 3.23版本就無法使用MySQL的臨時表。不過現在一般很少有再使用這麼低版本的MySQL資料庫服務了。

MySQL臨時表只在當前連線可見，如果你使用PHP指令碼來建立MySQL臨時表，那每當PHP指令碼執行完成後，該臨時表也會自動銷燬。

如果你使用了其他MySQL客戶端程式連線MySQL資料庫伺服器來建立臨時表，那麼只有在關閉客戶端程式時才會銷燬臨時表，當然你也可以手動銷燬。

例項

以下展示了使用MySQL 臨時表的簡單例項，以下的SQL程式碼可以適用於PHP指令碼的mysql_query()函式。

>mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);


mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

當你使用 SHOW TABLES命令顯示資料表列表時，你將無法看到 SalesSummary表。

如果你退出當前MySQL會話，再使用 SELECT命令來讀取原先建立的臨時表資料，那你會發現資料庫中沒有該表的存在，因為在你退出時該臨時表已經被銷燬了。

刪除MySQL 臨時表

預設情況下，當你斷開與資料庫的連線後，臨時表就會自動被銷燬。當然你也可以在當前MySQL會話使用 DROP TABLE 命令來手動刪除臨時表。

以下是手動刪除臨時表的例項：

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);


mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist

	MySQL中臨時表的基本建立與使用教程



	當工作在非常大的表上時，你可能偶爾需要執行很多查詢獲得一個大量資料的小的子集，不是對整個表執行這些查詢，而是讓MySQL每次找出所需的少數記錄，將記錄選擇到一個臨時表可能更快些，然後在這些表執行查詢。



	建立臨時表很容易，給正常的CREATE TABLE語句加上TEMPORARY關鍵字：



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



	臨時表將在你連線MySQL期間存在。當你斷開時，MySQL將自動刪除表並釋放所用的空間。當然你可以在仍然連線的時候刪除表並釋放空間。


DROP TABLE tmp_table



	如果在你建立名為tmp_table臨時表時名為tmp_table的表在資料庫中已經存在，臨時表將有必要遮蔽（隱藏）非臨時表tmp_table。


	如果你宣告臨時表是一個HEAP表，MySQL也允許你指定在記憶體中建立它：


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



	因為HEAP表儲存在記憶體中，你對它執行的查詢可能比磁碟上的臨時錶快些。然而，HEAP表與一般的表有些不同，且有自身的限制。詳見MySQL參考手冊。


	正如前面的建議，你應該測試臨時表看看它們是否真的比對大量資料庫執行查詢快。如果資料很好地索引，臨時表可能一點不快。


	臨時表再斷開於mysql的連線後系統會自動刪除臨時表中的資料，但是這隻限於用下面語句建立的表：


	定義欄位：


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



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


CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name



	另外mysql也允許你在記憶體中直接建立臨時表，因為是在記憶體中所有速度會很快，語法如下：


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



	 從上面的分析可以看出臨時表的資料是會被清空的，你斷開了連線就會被自動清空，但是你程式中不可能每發行一次sql就連線一次資料庫吧(如果是這樣的話，那就會出現你擔心的問題，如果不是就沒有問題)，因為只有斷開資料庫連線才會被清空資料，在一個資料庫連線裡面發行多次sql的話系統是不會自動清空臨時表資料的。


	只有在當前連線情況下， TEMPORARY 表才是可見的。當連線關閉時， TEMPORARY 表被自動取消。這意味著兩個不同的連線可以使用相同的臨時表名稱，同時兩個臨時表不會互相沖突，也不與原有的同名的非臨時表衝突。（原有的表被隱藏，直到臨時表被取消時為止。）必須擁有 CREATE TEMPORARY TABLES 許可權，才能建立臨時表。可以通過指定 ENGINE|TYPE = MEMORY; 來指定建立記憶體臨時表。


	如果表已存在，則使用關鍵詞 IF NOT EXISTS 可以防止發生錯誤。注意，原有表的結構與 CREATE TABLE 語句中表示的表的結構是否相同，這一點沒有驗證。註釋：如果在 CREATE TABLE...SELECT 語句中使用 IF NOT EXISTS ，則不論表是否已存在，由 SELECT 部分選擇的記錄都會被插入。


	DROP TEMPORARY TABLE 語句只取消 TEMPORARY 表，語句不會終止正在進行中的事務。在採用連線池的情況下，為防止多次 CREATE 、 DROP TEMPORARY TABLE 帶來的效能瓶頸，可以使用 CREATE IF NOT EXISTS + TRUNCATE TABLE 的方式來提升效能。


	臨時表支援主鍵、索引指定。在連線非臨時表查詢可以利用指定主鍵或索引來提升效能。


CREATE PROCEDURE sp_test_tt(IN i_chars VARCHAR(50),OUT o_counts BIGINT)
BEGIN
     create temporary table if not exists tmpTable – 不存在則建立臨時表
     (
      objChk varchar(255) primary key,
      ModelName varchar(50),
      Operator varchar(500),
      PModelName varchar(50)
     );
     truncate TABLE tmpTable; -- 使用前先清空臨時表。
 
     insert into tmpTable values(i_chars,i_chars,i_chars,i_chars);
     insert into tmpTable values(i_chars,i_chars,i_chars,i_chars); -- 語句1
     select * from tmpTable; -- 語句2
     select count(*) into o_counts from tmpTable; -- 語句3
END;



	上述程式碼語句 1 返回臨時表中所有資料，語句 2 將總記錄數寫入輸出引數。 truncate 語句放在 create 之後，而不是整個儲存過程最後，原因在於隨後的語句 1 插入同樣的值，二臨時表 PK 校驗將產生一個錯誤，則儲存過程最終異常結束。綜合異常處理，可以如下修改，以在每次儲存過程呼叫完畢後清除臨時表。

再來看一個例子：


CREATE PROCEDURE sp_test_tt(IN i_chars VARCHAR(50),OUT o_counts BIGINT)
BEGIN
     create temporary table if not exists tmpTable
     (
      objChk varchar(255) primary key,
      ModelName varchar(50),
      Operator varchar(500),
      PModelName varchar(50)
     ) ENGINE = MEMORY;
     begin
          declare exit handler for sqlwarning,NOT FOUND,SQLEXCEPTION set o_counts=-1;
          insert into tmpTable values(i_chars,i_chars,i_chars,i_chars);
          select * from tmpTable; -- 語句1
          select count(*) into o_counts from tmpTable;
     end;
     truncate TABLE tmpTable; -- 語句2
END;



	雖然上述程式碼語句 2 最後 truncate table 清空了全部臨時表資料，但前面語句 1 select 的資料結果集不會被清除。已通過 java 程式驗證。


	臨時表可以解決二維陣列輸出的問題。但是，大批量的資料插入只能由程式採用迴圈來做。某些特殊情況下的輸入陣列，例如選擇好的一組待刪除資料的 ID 的輸入，也只能利用迴圈來做。臨時表也不適用於需要三維陣列的情況。




	通過CREATE TEMPORARY TABLE 建立的臨時表，這種臨時表稱為外部臨時表。這種臨時表只對當前使用者可見，當前會話結束的時候，該臨時表會自動關閉。這種臨時表的命名與非臨時表可以同名（同名後非臨時表將對當前會話不可見，直到臨時表被刪除）。


	內部臨時表


	內部臨時表是一種特殊輕量級的臨時表，用來進行效能優化。這種臨時表會被MySQL自動建立並用來儲存某些操作的中間結果。這些操作可能包括在優化階段或者執行階段。這種內部表對使用者來說是不可見的，但是通過EXPLAIN或者SHOW STATUS可以檢視MYSQL是否使用了內部臨時表用來幫助完成某個操作。內部臨時表在SQL語句的優化過程中扮演著非常重要的角色， MySQL中的很多操作都要依賴於內部臨時表來進行優化。但是使用內部臨時表需要建立表以及中間資料的存取代價，所以使用者在寫SQL語句的時候應該儘量的去避免使用臨時表。


	內部臨時表有兩種型別：一種是HEAP臨時表，這種臨時表的所有資料都會存在記憶體中，對於這種表的操作不需要IO操作。另一種是OnDisk臨時表，顧名思義，這種臨時表會將資料儲存在磁碟上。OnDisk臨時表用來處理中間結果比較大的操作。如果HEAP臨時表儲存的資料大於MAX_HEAP_TABLE_SIZE（詳情請參考MySQL手冊中系統變數部分），HEAP臨時表將會被自動轉換成OnDisk臨時表。OnDisk臨時表在5.7中可以通過INTERNAL_TMP_DISK_STORAGE_ENGINE系統變數選擇使用MyISAM引擎或者InnoDB引擎。




	mysql的記憶體表和臨時表 




	

		

			記憶體表： 
		
		

			

				

					session 1

$ mysql -uroot

root@(none) 10:05:06>use test

Database changed

root@test 10:06:06>CREATE TABLE tmp_memory (i INT) ENGINE = MEMORY;

Query OK, 0 rows affected (0.00 sec)

root@test 10:08:46>insert into tmp_memory values (1);

Query OK, 1 row affected (0.00 sec)

root@test 10:08:46>





session2

$ mysql -uroot test

root@test 10:05:12>CREATE TABLE tmp_memory (i INT) ENGINE = MEMORY;

ERROR 1050 (42S01): Table 'tmp_memory' already exists

root@test 10:16:27>select * from tmp_memory;

+------+

| i |

+------+

| 1 |

+------+

row in set (0.00 sec)



				
			
			

				1. 多個session，建立表的名字不能一樣
			
			

				2. 一個session建立會話後，對其他session也是可見的

3. data目錄下只有tmp_memory.frm ,表結構放在磁碟上，資料放在記憶體中

4. mysql 重啟或者關閉後記憶體表裡的資料會丟失，但是表結構仍然存在

5. 可以建立索引,刪除索引,支援唯一索引

6. 不影響主備，主庫上插入的資料，備庫也可以查到

7. show tables 看得到表 
			
			

				臨時表： 
			
			

				

					session1

$ mysql -uroot test

root@test 10:30:18>CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL);

Query OK, 0 rows affected (0.05 sec)

root@test 10:31:54>select * from tmp_table;

+--------+-------+

| name | value |

+--------+-------+

| aaaaaa | 10 |

+--------+-------+

row in set (0.00 sec)





session2

root@test 10:20:13> CREATE TEMPORARY TABLE tmp_table (name VARCHAR(10) NOT NULL,value INTEGER NOT NULL);

Query OK, 0 rows affected (0.02 sec)





root@test 10:30:39>insert into tmp_table values ('bbbbbbb',10);

Query OK, 1 row affected (0.01 sec)





root@test 10:31:33>select * from tmp_table;

+---------+-------+

| name | value |

+---------+-------+

| bbbbbbb | 10 |

+---------+-------+

row in set (0.00 sec)





root@test 10:31:43>exit

Bye

[1 Single:MS-Master db152011.sqa.cm6: mysql ~ ]

$ mysql -uroot test

root@test 10:32:17>select * from tmp_table;

ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist

root@test 10:32:22>

root@test 10:32:23>



				
			
			

				1. 建立的表的名字可以一樣  
			
			

				2. 表結構和資料都放在記憶體中

3. 會話消失表結構和資料都消失

4. 可以建立索引,刪除索引

5. 主庫建立的表，備庫查不到，

6. show tables 看不到表
			
			

				使用記憶體表需要注意的事項 
			
			

				1. 記憶體表需要自己delete資料或者drop表；需要drop許可權，這點比較危險
			
			

				2. 記憶體表的表結構是儲存在磁碟上的，如果多個session使用同一個表名，會存在衝突；如果不需要使用表名，如果使用一次都需要建立表結構，到時候會有很多小檔案存在，不利於db的維護，dba清理表也有風險；
			
			

				基於以上不適合用記憶體表
			
			

				1. 臨時表是會話級別的，即使多個session建立的表名一樣，都相互不影響
			
			

				2. 會話消失，所有的都消失，這點很不利於應用排查問題
			
			

				另外這兩個都需要消耗額外的記憶體空間，雖然db端可以忍受，但是不太可控；DB端還有這個引數:
			
			

				max_tmp_tables 一個客戶能同時保持開啟的臨時表的最大數量，這個值預設32，可以根據需要調整此值