mysql臨時表插入數(shù)據(jù)

臨時表

1.在數(shù)據(jù)庫斷開連接的時候會自動刪除臨時表。

 create temporary table IF NOT EXISTS client.getstucou(
　　type_id int
 )Engine=InnoDB default charset utf8;

注意：在存儲過程中創(chuàng)建了臨時表的情況的話，在外面使用查詢語句是找不到這張表的。

例如：

在存儲過程中的sql語句中才而能找到該表。

2.臨時表插入（select語句查出的數(shù)據(jù)集合）。INSERT INTO 表名 （查詢到的集合）

例如：UNION ALL（將兩個select語句求并集）

INSERT INTO getstucou 
 WITH TEMP AS (
  SELECT
  *
  FROM
  t_shop_type
  WHERE
  parent_type_id = @StuNo
  OR type_id
  = @StuNo
  UNION ALL
  SELECT
  t.*
  FROM
  TEMP,
  t_shop_type t
  WHERE
  TEMP.type_id = t.parent_type_id
  ) SELECT
  type_id 
  FROM
  TEMP;

mysql中臨時表(TEMPORARY)

首先來看看官網(wǎng)給的有關MySQL的臨時表的簡介：

In MySQL, a temporary table is a special type of table that allows you to store a temporary result set, which you can reuse several times in a single session.A temporary table is very handy when it is impossible or expensive to query data that requires a single SELECT statement with the JOIN clauses. In this case, you can use a temporary table to store the immediate result and use another query to process it.

總結而來就是：

當我們需要使用難以接受的或開銷很大的一個SELECT語句和JOIN子句的數(shù)據(jù)時，臨時表非常方便。

語法

大致同CREATE TABLE語法，創(chuàng)建臨時表則是CREATE TEMPORARY TABLE，增加了TEMPORARY關鍵字表示臨時表。

1.創(chuàng)建

（1）創(chuàng)建不同于現(xiàn)有表的臨時表

CREATE TEMPORARY TABLE table_name(
? ?column_1_definition,
? ?column_2_definition,
? ?...,
? ?table_constraints
);

實例：

//首先，創(chuàng)建一個新的臨時表，稱為credits存儲客戶的信用：
CREATE TEMPORARY TABLE credits(
? ? customerNumber INT PRIMARY KEY,
? ? creditLimit DEC(10,2)
);
//然后，將customers表中的行插入臨時表中credits：
INSERT INTO credits(customerNumber,creditLimit)
SELECT customerNumber, creditLimit
FROM customers
WHERE creditLimit > 0;

（2）創(chuàng)建結構基于現(xiàn)有表的臨時表

不能使用該CREATE TEMPORARY TABLE … LIKE語句。而是，使用以下語法：

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table
LIMIT 0;

實例：

//下面的示例創(chuàng)建一個臨時表，該表按收入存儲前10名客戶。臨時表的結構是從以下SELECT語句派生的：
CREATE TEMPORARY TABLE top_customers
SELECT p.customerNumber,?
? ? ? ?c.customerName,?
? ? ? ?ROUND(SUM(p.amount),2) sales
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY sales DESC
LIMIT 10;
//從top_customers臨時表中查詢數(shù)據(jù)，就像從永久表中查詢一樣：
SELECT?
? ? customerNumber,?
? ? customerName,?
? ? sales
FROM
? ? top_customers
ORDER BY sales;

2.刪除

可以通過DROP TABLE語句刪除臨時表，但不推薦這樣，因為當臨時表與永久表同名時，有可能會誤刪永久表，當然若你已經(jīng)準備好機票，我就祝你一路順風吧！若你未曾準備好機票，這里也給你提供中航訂票電話：0086-95583 | 0086-10-95583，祝你好運！

推薦：

DROP TEMPORARY TABLE table_name;

注意：

（1）如果嘗試使用該DROP TEMPORARY TABLE語句刪除永久表，則會收到一條錯誤消息，提示您嘗試刪除的表是未知的。愉快地避免刪表的誤操作了。

（2）如果開發(fā)使用連接池或持久連接的應用程序，則不能保證在終止應用程序時自動刪除臨時表。因為該應用程序使用的數(shù)據(jù)庫連接可能仍處于打開狀態(tài)，并放置在連接池中，以便其他客戶端以后再使用。因此，一個好習慣是在不再使用臨時表時始終刪除它們。

（3）在采用連接池的情況下，為防止多次CREATE 、 DROP TEMPORARY TABLE帶來的性能瓶頸，可以使用CREATE IF NOT EXISTS + TRUNCATE TABLE 的方式來提升性能。（注意：IF NOT EXISTS是在TABLE之后，table_name之前的。）

功能

（1）通過使用CREATE TEMPORARY TABLE語句創(chuàng)建一個臨時表。請注意，該關鍵字TEMPORARY已添加到CREATE和TABLE關鍵字之間 。

（2）當會話結束或連接終止時，MySQL自動刪除臨時表。當然，您可以在DROP TABLE不再使用臨時表時使用該 語句顯式刪除該臨時表。

（3）臨時表僅對創(chuàng)建它的客戶端可用并且可以訪問。不同的客戶端可以創(chuàng)建具有相同名稱的臨時表而不會導致錯誤，因為只有創(chuàng)建臨時表的客戶端才能看到它。但是，在同一會話中，兩個臨時表不能共享相同的名稱。

（4）臨時表可以與數(shù)據(jù)庫中的普通表具有相同的名稱。例如，如果您創(chuàng)建一個employees在示例數(shù)據(jù)庫中命名的臨時表，則現(xiàn)有employees表將變得不可訪問。您針對該employees表發(fā)出的每個查詢現(xiàn)在都引用該臨時表 employees。當您刪除employees臨時表時，永久employees表可用且可訪問。

但即使臨時表可以與永久表具有相同的名稱，也不建議使用。因為這可能導致混亂并可能導致意外的數(shù)據(jù)丟失。例如，如果與數(shù)據(jù)庫服務器的連接丟失并且您自動重新連接到服務器，則無法區(qū)分臨時表和永久表。然后，您可以發(fā)出一條DROP TABLE 語句刪除永久表而不是臨時表，這是不期望的。為避免此問題，可以使用該DROP TEMPORARY TABLE語句刪除臨時表。

檢查是否存在臨時表

MySQL不提供直接檢查臨時表是否存在的函數(shù)或語句。但是，我們可以創(chuàng)建一個存儲過程來檢查臨時表是否存在，如下所示：

DELIMITER //
CREATE PROCEDURE check_table_exists(table_name VARCHAR(100))?
BEGIN
? ? DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @err = 1;
? ? SET @err = 0;
? ? SET @table_name = table_name;
? ? SET @sql_query = CONCAT('SELECT 1 FROM ',@table_name);
? ? PREPARE stmt1 FROM @sql_query;
? ? IF (@err = 1) THEN
? ? ? ? SET @table_exists = 0;
? ? ELSE
? ? ? ? SET @table_exists = 1;
? ? ? ? DEALLOCATE PREPARE stmt1;
? ? END IF;
END //
DELIMITER ;
//在此過程中，我們嘗試從臨時表中選擇數(shù)據(jù)。如果臨時表存在，則將該@table_exists變量設置為1，否則，將其設置為0。
//該語句調(diào)用check_table_exists來檢查臨時表是否credits 存在：
CALL check_table_exists('credits');
SELECT @table_exists;
//根據(jù)輸出判斷臨時表是否存在

與MEMORY比較

我們知道還有一種通過存儲引擎創(chuàng)建臨時表的方式ENGINE|TYPE = MEMORY，這種方式創(chuàng)建的臨時表是在內(nèi)存中的，效率在理論上是比TEMPORARY更高的，因為MEMORY是在內(nèi)存中，TEMPORARY是同永久表在磁盤上的。

實例：

CREATE TEMPORARY TABLE table_name(
? ?column_1_definition,
? ?column_2_definition,
? ?...,
? ?table_constraints
) ENGINE|TYPE = MEMORY;

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論