臨時表 (TEMPORARY TABLE) 為會話級可見、自動清理的中間結果存儲利器,適用於複雜 ETL、報表聚合、大數據分步處理。本文全面解析其用法與坑點。
-- 基礎語法
CREATE TEMPORARY TABLE tmp_table (
name VARCHAR(10) NOT NULL,
passwd CHAR(6) NOT NULL
);
-- 從查詢結果創建(指定引擎)
CREATE TEMPORARY TABLE IF NOT EXISTS sp_output_tmp
ENGINE = MEMORY
SELECT id, name, SUM(amount) AS total
FROM orders
WHERE create_time > '2024-01-01'
GROUP BY id, name;
| 特性 | 說明 |
|---|---|
| 可見範圍 | 僅當前會話 (Connection) 可見 |
| 生命周期 | 會話斷開/關閉時自動 DROP |
| 命名隔離 | 不同會話可同名共存,互不干擾 |
| 遮蔽同名永久表 | 臨時表存在期間,同名永久表不可訪問 |
| 權限要求 | 需 CREATE TEMPORARY TABLES 權限 |
❌ 不支持:NDB Cluster、Archive、CSV 等
-- 錯誤示例
SELECT * FROM temp_table, temp_table AS t2;
-- ERROR 1137: Can't reopen table: 'temp_table'
-- 存儲過程/函數中同樣受限
-- 即使使用不同別名、不同語句也會報錯
SHOW TABLES 不顯示臨時表RENAME TABLE,可用 ALTER TABLE old_name RENAME new_name;-- 步驟1:提取基礎數據到臨時表
CREATE TEMPORARY TABLE tmp_daily_sales ENGINE=InnoDB
SELECT date, product_id, SUM(qty) AS total_qty, SUM(amount) AS total_amt
FROM order_details
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY date, product_id;
-- 步驟2:關聯維度表豐富信息
CREATE TEMPORARY TABLE tmp_enriched ENGINE=InnoDB
SELECT t.*, p.name, p.category, p.price
FROM tmp_daily_sales t
JOIN products p ON t.product_id = p.id;
-- 步驟3:最終聚合輸出
SELECT category, date, SUM(total_amt) AS revenue
FROM tmp_enriched
GROUP BY category, date
ORDER BY date DESC, revenue DESC;
-- 將大表 ID 分段存入臨時表
CREATE TEMPORARY TABLE id_chunks (id_start INT, id_end INT, PRIMARY KEY(id_start));
INSERT INTO id_chunks VALUES (1,10000), (10001,20000), ...;
-- 應用層循環讀取 id_chunks,分批處理
-- 每批在短事務中完成,避免長事務阻塞
-- 複雜 CTE 物化為臨時表,多次引用
CREATE TEMPORARY TABLE tmp_complex AS
WITH RECURSIVE cte AS (...)
SELECT * FROM cte;
-- 後續多個查詢復用 tmp_complex
SELECT ... FROM tmp_complex WHERE ...;
SELECT ... FROM tmp_complex JOIN ...;
-- 顯式刪除(推薦用完即刪,釋放資源)
DROP TEMPORARY TABLE IF EXISTS tmp_table;
tmp_、stage_ 便於識別max_heap_table_size/tmp_table_size 限制,超限自動轉 MyISAM 磁盤表| 特性 | 臨時表 | CTE (WITH) | 子查詢 |
|---|---|---|---|
| 可見範圍 | 會話級 | 語句級 | 語句級 |
| 多次引用 | ✅ (受限) | ✅ | ❌ 每次重算 |
| 物化存儲 | ✅ | MySQL 8.0+ 可選 | ❌ |
| 支持索引 | ✅ | ❌ | ❌ |
| 事務支持 | InnoDB ✅ | ✅ | ✅ |
| 適用場景 | 複雜多步、大結果集復用 | 單語句邏輯拆解 | 簡單嵌套過濾 |