mysql臨時表插入資料方式

2022-09-28 14:01:20

mysql臨時表插入資料

臨時表

1.在資料庫斷開連線的時候會自動刪除臨時表。

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

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

例如：

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

2.臨時表插入（select語句查出的資料集合）。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)

首先來看看官網給的有關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子句的資料時，臨時表非常方便。

語法

大致同CREATE TABLE語法，建立臨時表則是CREATE TEMPORARY TABLE，增加了TEMPORARY關鍵字表示臨時表。

1.建立

（1）建立不同於現有表的臨時表

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

範例：

//首先，建立一個新的臨時表，稱為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）建立結構基於現有表的臨時表

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

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table
LIMIT 0;

範例：

//下面的範例建立一個臨時表，該表按收入儲存前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臨時表中查詢資料，就像從永久表中查詢一樣：
SELECT 
    customerNumber, 
    customerName, 
    sales
FROM
    top_customers
ORDER BY sales;

2.刪除

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

功能

（1）通過使用CREATE TEMPORARY TABLE語句建立一個臨時表。請注意，該關鍵字TEMPORARY已新增到CREATE和TABLE關鍵字之間。

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

（3）臨時表僅對建立它的使用者端可用並且可以存取。不同的使用者端可以建立具有相同名稱的臨時表而不會導致錯誤，因為只有建立臨時表的使用者端才能看到它。但是，在同一對談中，兩個臨時表不能共用相同的名稱。

（4）臨時表可以與資料庫中的普通表具有相同的名稱。例如，如果您建立一個employees在範例資料庫中命名的臨時表，則現有employees表將變得不可存取。您針對該employees表發出的每個查詢現在都參照該臨時表 employees。當您刪除employees臨時表時，永久employees表可用且可存取。

但即使臨時表可以與永久表具有相同的名稱，也不建議使用。因為這可能導致混亂並可能導致意外的資料丟失。例如，如果與資料庫伺服器的連線丟失並且您自動重新連線到伺服器，則無法區分臨時表和永久表。然後，您可以發出一條DROP TABLE 語句刪除永久表而不是臨時表，這是不期望的。為避免此問題，可以使用該DROP TEMPORARY TABLE語句刪除臨時表。

檢查是否存在臨時表

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

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 ;
//在此過程中，我們嘗試從臨時表中選擇資料。如果臨時表存在，則將該@table_exists變數設定為1，否則，將其設定為0。
//該語句呼叫check_table_exists來檢查臨時表是否credits 存在：
CALL check_table_exists('credits');
SELECT @table_exists;
//根據輸出判斷臨時表是否存在

與MEMORY比較

我們知道還有一種通過儲存引擎建立臨時表的方式ENGINE|TYPE = MEMORY，這種方式建立的臨時表是在記憶體中的，效率在理論上是比TEMPORARY更高的，因為MEMORY是在記憶體中，TEMPORARY是同永久表在磁碟上的。

範例：

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

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

mysql臨時表插入資料方式

目錄