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

臨時(shí)表

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

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

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

例如：

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

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

例如：UNION ALL（將兩個(gè)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中臨時(shí)表(TEMPORARY)

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

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.

總結(jié)而來就是：

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

語法

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

1.創(chuàng)建

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

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

實(shí)例：

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

（2）創(chuàng)建結(jié)構(gòu)基于現(xiàn)有表的臨時(shí)表

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

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table
LIMIT 0;

實(shí)例：

//下面的示例創(chuàng)建一個(gè)臨時(shí)表，該表按收入存儲(chǔ)前10名客戶。臨時(shí)表的結(jié)構(gòu)是從以下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í)表中查詢數(shù)據(jù)，就像從永久表中查詢一樣：
SELECT?
? ? customerNumber,?
? ? customerName,?
? ? sales
FROM
? ? top_customers
ORDER BY sales;

2.刪除

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

推薦：

DROP TEMPORARY TABLE table_name;

注意：

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

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

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

功能

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

（2）當(dāng)會(huì)話結(jié)束或連接終止時(shí)，MySQL自動(dòng)刪除臨時(shí)表。當(dāng)然，您可以在DROP TABLE不再使用臨時(shí)表時(shí)使用該 語句顯式刪除該臨時(shí)表。

（3）臨時(shí)表僅對(duì)創(chuàng)建它的客戶端可用并且可以訪問。不同的客戶端可以創(chuàng)建具有相同名稱的臨時(shí)表而不會(huì)導(dǎo)致錯(cuò)誤，因?yàn)橹挥袆?chuàng)建臨時(shí)表的客戶端才能看到它。但是，在同一會(huì)話中，兩個(gè)臨時(shí)表不能共享相同的名稱。

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

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

檢查是否存在臨時(shí)表

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

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í)表中選擇數(shù)據(jù)。如果臨時(shí)表存在，則將該@table_exists變量設(shè)置為1，否則，將其設(shè)置為0。
//該語句調(diào)用check_table_exists來檢查臨時(shí)表是否credits 存在：
CALL check_table_exists('credits');
SELECT @table_exists;
//根據(jù)輸出判斷臨時(shí)表是否存在

與MEMORY比較

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

實(shí)例：

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

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

最新評(píng)論