MySQL 臨時表完整指南：創建類型、生命周期、內存優化與存儲過程實戰

一、臨時表核心特性

• 會話隔離：僅當前連接可見，不同連接可同名共存
• 自動清理：連接斷開時自動 DROP，釋放空間
• 隱藏同名永久表：同名時臨時表優先，永久表不可訪問直到臨時表刪除
• 權限：需要 CREATE TEMPORARY TABLES 權限

二、創建語法與類型

2.1 顯式定義列

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

2.2 從查詢結果創建（CTAS）

CREATE TEMPORARY TABLE tmp_table 
SELECT id, name, score FROM users WHERE active=1;

2.3 內存表（HEAP/MEMORY 引擎）

CREATE TEMPORARY TABLE tmp_table (
  id INT PRIMARY KEY,
  data VARCHAR(100)
) ENGINE=MEMORY;

• 數據純內存，讀寫極快（無磁盤 I/O）
• 受 max_heap_table_size、tmp_table_size 限制，超限自動轉磁盤
• 不支援 TEXT/BLOB、AUTO_INCREMENT（5.6+ 支援）、全文索引
• 重啟服務數據丟失（但臨時表本身隨連接消失，影響較小）

三、生命周期管理

四、存儲過程中應用臨時表實戰

4.1 基礎模式：輸入參數 → 臨時表 → 多步驟處理 → 結果集輸出

CREATE PROCEDURE sp_process_batch(IN p_ids VARCHAR(500), OUT p_count BIGINT)
BEGIN
  -- 1. 確保表存在（連接池復用場景）
  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_ids (
    id BIGINT PRIMARY KEY
  ) ENGINE=MEMORY;

  -- 2. 清空舊數據
  TRUNCATE TABLE tmp_ids;

  -- 3. 解析輸入字符串填充臨時表（這裡簡化，實際需循環或 split 函數）
  -- 假設已有 split_string_to_table 函數
  INSERT INTO tmp_ids (id) 
  SELECT CAST(val AS UNSIGNED) FROM split_string_to_table(p_ids, ',');

  -- 4. 業務邏輯：關聯查詢、聚合、更新等
  SELECT COUNT(*) INTO p_count FROM tmp_ids;

  -- 5. 返回結果集給調用者
  SELECT t.*, u.name, u.email 
  FROM tmp_ids t
  JOIN users u ON u.id = t.id;

  -- 6. 可選：結束前清理（結果集已發送給客戶端，不受影響）
  -- TRUNCATE TABLE tmp_ids;
END;

4.2 異常處理模式：確保臨時表清理

CREATE PROCEDURE sp_safe_process(IN p_data VARCHAR(100), OUT p_result INT)
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_work (
    item VARCHAR(100) PRIMARY KEY
  ) ENGINE=MEMORY;

  -- 異常處理器：任何錯誤/警告/未找到 設置輸出碼並繼續
  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
  BEGIN
    SET p_result = -1;
    TRUNCATE TABLE tmp_work;  -- 確保清理
  END;

  TRUNCATE TABLE tmp_work;
  INSERT INTO tmp_work VALUES (p_data), (CONCAT(p_data, '_bak'));
  
  SELECT COUNT(*) INTO p_result FROM tmp_work;
  SELECT * FROM tmp_work;  -- 返回結果集
  
  TRUNCATE TABLE tmp_work;  -- 正常結束清理
END;

五、進階應用場景

1. 替代數組參數：MySQL 無數組類型，臨時表模擬「輸入數組」
2. 中間結果緩存：複雜報表分步驟，每步結果存臨時表，避免重複計算
3. 分頁游標優化：大表分頁先 SELECT id INTO 臨時表，再關聯查詳情
4. 跨庫臨時關聯：FEDERATED 引擎或應用層拉取數據寫入臨時表，再本地 JOIN

六、限制與避坑

七、總結

1. 臨時表是**會話級私有工作空間**，善用於中間結果、參數傳遞、複雜邏輯分解
2. ENGINE=MEMORY 適合小數據高頻訪問，**注意大小限制與列類型限制**
3. 連接池環境下**CREATE IF NOT EXISTS + TRUNCATE 模式**效能最佳
4. 異常處理器中**必須清理臨時表**，避免連接復用時髒讀
5. 結果集返回後 TRUNCATE **不影響** 已發送給客戶端的結果集