程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL中臨時表的基本創建與使用教程,

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

編輯:MySQL綜合教程

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 的輸入,也只能利用循環來做。臨時表也不適用於需要三維數組的情況。

您可能感興趣的文章:

  • MySQL中關於臨時表的一些基本使用方法
  • 解析Mysql臨時表及特點
  • mysql 臨時表 cann''t reopen解決方案
  • mysql復制中臨時表的運用技巧

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved