MySQL 臨時表完整指南：創建語法、生命周期、內存表優化、存儲過程實戰、性能對比

前言

面對大表複雜查詢，將所需子集先導入臨時表，再在臨時表上操作，往往能大幅降低 I/O 與 CPU 開銷。MySQL 臨時表具有會話級隔離、自動清理、支援索引等特性，是複雜報表、ETL、多步驟邏輯的利器。

一、創建語法與類型

1. 顯式定義結構

CREATE TEMPORARY TABLE tmp_table (
  name VARCHAR(10) NOT NULL,
  value INTEGER NOT NULL
);

2. 從查詢結果創建（CTAS）

CREATE TEMPORARY TABLE tmp_table SELECT * FROM large_table WHERE create_time > '2023-01-01';

3. 內存引擎（HEAP/MEMORY）

CREATE TEMPORARY TABLE tmp_table (
  name VARCHAR(10) NOT NULL,
  value INTEGER NOT NULL
) ENGINE = MEMORY;

• 數據存儲於內存，讀寫極快
• 受 tmp_table_size/max_heap_table_size 限制，超限自動轉 MyISAM 磁碟表
• 不支援 BLOB/TEXT、不支援可變長度行（VARCHAR 實際按 CHAR 存儲）
• 重啟 MySQL 後數據消失

二、生命周期與可見性

• 會話私有：僅當前連接可見，不同連接可同名不衝突
• 自動清理：連接斷開（或連接池釋放）時自動 DROP
• 手動清理：DROP TEMPORARY TABLE tmp_table;
• 屏蔽同名永久表：臨時表存在期間，同名永久表不可訪問

三、存儲過程實戰模式

模式 1：IF NOT EXISTS + TRUNCATE（連接池友好）

CREATE PROCEDURE sp_test_tt(IN i_chars VARCHAR(50), OUT o_counts BIGINT)
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable (
    objChk VARCHAR(255) PRIMARY KEY,
    ModelName VARCHAR(50),
    Operator VARCHAR(500),
    PModelName VARCHAR(50)
  ) ENGINE = MEMORY;
  
  TRUNCATE TABLE tmpTable;  -- 每次調用前清空
  
  INSERT INTO tmpTable VALUES (i_chars, i_chars, i_chars, i_chars);
  INSERT INTO tmpTable VALUES (i_chars, i_chars, i_chars, i_chars);
  
  SELECT * FROM tmpTable;                    -- 結果集 1
  SELECT COUNT(*) INTO o_counts FROM tmpTable; -- 輸出參數
END;

模式 2：異常處理保證清理

CREATE PROCEDURE sp_test_tt(IN i_chars VARCHAR(50), OUT o_counts BIGINT)
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable (
    objChk VARCHAR(255) PRIMARY KEY,
    ModelName VARCHAR(50),
    Operator VARCHAR(500),
    PModelName VARCHAR(50)
  ) ENGINE = MEMORY;
  
  BEGIN
    DECLARE EXIT HANDLER FOR SQLWARNING, NOT FOUND, SQLEXCEPTION SET o_counts = -1;
    INSERT INTO tmpTable VALUES (i_chars, i_chars, i_chars, i_chars);
    SELECT * FROM tmpTable;
    SELECT COUNT(*) INTO o_counts FROM tmpTable;
  END;
  
  TRUNCATE TABLE tmpTable;  -- 正常/異常退出均執行清理
END;

關鍵點： TRUNCATE 放在 END 之後，即使 Handler 內拋出異常，外層流程仍會執行清理。結果集已返回客戶端，不受後續 TRUNCATE 影響（經 Java 驗證）。

四、性能優化建議

五、限制與避坑指南

• 不支援三維數組/複雜嵌套結構，僅適合二維表格數據
• 大批量寫入需應用層循環，SQL 無批量綁定語法
• 連接池環境下：臨時表隨連接釋放而非事務結束，長連接可能殘留舊數據 → 必須 TRUNCATE
• 主從複制：臨時表操作不寫 Binlog，從庫不會同步（正確行為）
• 權限：需 CREATE TEMPORARY TABLES 權限

六、對比：臨時表 vs 子查詢 vs CTE (MySQL 8.0+)

1. 上一篇文章： MySQL 自定義 Sequence 序列實現：函數封裝、多序列隔離、Java 調用完整教程
2. 下一篇文章： MySQL DESCRIBE 命令詳解：查看表結構、列信息、判斷欄位存在性

MySQL 資料庫相關知識

MySQL 5.1.26 Windows 安裝配置圖文詳細教程

安裝流程（21 步） 1-2. 啟動安裝、接受協議 雙擊

MySQL 日誌維護策略全攻略：錯誤、查詢、慢查詢、二進制、事務日誌最佳實踐

MySQL 多種日誌類型各有用途，合理配置與維護策略關乎效能

Perl DBI 命令列實戰：多格式目錄產生、會員續費通知與資料編輯

專案目標與腳本規劃 本章完成「歷史同盟」四大命令列任務：

利用 INFORMATION_SCHEMA 系統視圖查詢 SQL Server 表主外鍵完整資訊

核心視圖 INFORMATION_SCHEMA.COL