MySQL 臨時表完全指南：CREATE TEMPORARY TABLE、記憶體 HEAP 引擎、連線隔離、自動清理與實戰陷阱

前言：臨時表是複雜查詢分解、中間結果緩存的利器

面對超大表多次掃描，將所需子集提取至臨時表，後續查詢在小表上執行，可大幅降低 IO 與 CPU。本文系統講解建立語法、生命週期、HEAP 引擎加速、兩種建表方式、連線隔離機制與常見誤區。

基本建立語法

-- 方式 1：顯式定義欄位
CREATE TEMPORARY TABLE tmp_table (
  name VARCHAR(10) NOT NULL,
  value INT NOT NULL
);

-- 方式 2：CTAS 直接從查詢結果建表 (MySQL 3.23+)
CREATE TEMPORARY TABLE tmp_table SELECT * FROM large_table WHERE condition;

生命週期與隔離性

• 連線級隔離：不同連線建立同名臨時表互不干擾，各自可見自己的版本
• 自動清理：連線斷開 (正常/異常) 時，**MySQL 自動 DROP 臨時表並釋放空間**
• 手動清理：DROP TEMPORARY TABLE tmp_table (建議顯式調用，釋放當前連線資源)
• 遮蔽永久表：臨時表與永久表同名時，當前連線**僅可見臨時表**，永久表被隱藏

記憶體 HEAP 引擎加速

CREATE TEMPORARY TABLE tmp_table (
  name VARCHAR(10) NOT NULL,
  value INT NOT NULL
) ENGINE = MEMORY;  -- 或 TYPE=HEAP (舊語法)

關鍵配置：max_heap_table_size (全域)、tmp_table_size (會話)，取較小者為臨時表上限，預設 16MB。大臨時表需調大兩者。

實戰陷阱避坑指南

1. 連線池複用導致臨時表殘留：應用使用連線池 (如 HikariCP)，連線不斷開，**臨時表不會自動清理**，佔用記憶體/磁碟。解決：業務邏輯結束時顯式 DROP TEMPORARY TABLE，或啟用連線池 resetConnection 執行清理 SQL。
2. 同名永久表被遮蔽導致邏輯錯誤：非預期覆蓋永久表查詢。解決：臨時表命名加前綴 tmp_、session_。
3. MEMORY 引擎定長行導致空間浪費：VARCHAR(255) 實際存 2 字元也佔 255 字節。解決：臨時表欄位精確定長、或用磁碟臨時表。
4. 事務中臨時表行為：CREATE TEMPORARY TABLE **隱式提交** 當前事務。解決：在事務外建立臨時表。
5. 複製場景：主庫建立臨時表不會複製到從庫 (不寫 Binlog)，從庫同名永久表不受影響。

結語

臨時表以「連線隔離、自動清理、HEAP 極速」三大特性，成為複雜報表、多步驟 ETL、大表分頁優化的戰術利器。**核心守則：顯式 DROP、命名加前綴、事務外建立、連線池清理、精確定長欄位**。掌握這五點，即可安全享受臨時表紅利。