目錄

MySQL 臨時表詳細說明

1. 定義

2. 核心特性

3. 創建與使用

4. 典型應用場景

5. 生命周期管理

6. 注意事項

7. 性能優化建議

---

MySQL 臨時表詳細說明

1. 定義

臨時表是存儲在內存或磁盤上的臨時性數據表，僅在當前數據庫會話中存在。會話結束時自動銷毀，適合存儲中間計算結果或臨時數據集。其名稱以#開頭（如#TempTable）。

2. 核心特性

• 會話隔離性：每個會話獨立維護自己的臨時表，互不可見。
• 自動清理：會話結束（連接斷開）時自動刪除。
• 存儲位置：
  • 內存引擎（如MEMORY）：小數據量時高效
  • 磁盤存儲（默認）：數據量大時自動切換
• 作用域：
  • 局部臨時表（#前綴）：僅當前會話可見
  • 全局臨時表（##前綴）：所有會話可見，但會話結束后自動刪除

3. 創建與使用

創建語法：

-- 局部臨時表
CREATE TEMPORARY TABLE #EmployeeTemp (id INT PRIMARY KEY,name VARCHAR(50),salary DECIMAL(10,2)
);-- 全局臨時表
CREATE TEMPORARY TABLE ##GlobalTemp (log_id INT,message TEXT
);

數據操作：

-- 插入數據
INSERT INTO #EmployeeTemp VALUES (1, '張三', 8500.00);-- 查詢
SELECT * FROM #EmployeeTemp WHERE salary > 8000;-- 關聯其他表
SELECT e.name, d.department 
FROM #EmployeeTemp e
JOIN departments d ON e.dept_id = d.id;

4. 典型應用場景

• 復雜查詢優化：存儲子查詢結果，避免重復計算

  CREATE TEMPORARY TABLE #HighSalary 
  SELECT * FROM employees WHERE salary > 10000;SELECT d.name, COUNT(*) 
  FROM #HighSalary h
  JOIN departments d ON h.dept_id = d.id
  GROUP BY d.name;
  

• 批量數據處理：ETL過程中的臨時存儲
• 會話級緩存：存儲用戶會話的中間狀態（如購物車數據）
• 遞歸查詢：實現層次結構遍歷

  WITH RECURSIVE cte AS (SELECT id, parent_id FROM categories WHERE parent_id IS NULLUNION ALLSELECT c.id, c.parent_id FROM categories cJOIN cte ON c.parent_id = cte.id
  )
  SELECT * INTO #Hierarchy FROM cte;  -- 存儲遞歸結果
  

5. 生命周期管理

階段	行為
創建	CREATE TEMPORARY TABLE 執行時生成
會話活躍期	可正常讀寫，支持索引、觸發器等對象
會話結束	自動刪除表結構及數據
異常中斷	連接意外斷開時由MySQL自動清理

6. 注意事項

• 命名沖突：避免與持久表同名，臨時表優先級更高
• 事務行為：
  • 未提交事務中創建的臨時表，回滾時不會刪除
  • 數據修改操作（INSERT/UPDATE）可回滾
• 復制環境：
  • 主從復制中，臨時表操作不寫入二進制日志（binlog）
  • 級聯刪除場景需顯式處理外鍵約束
• 內存限制：
  • 超過tmp_table_size（默認16MB）時轉為磁盤存儲
  • 監控語句：SHOW STATUS LIKE 'Created_tmp%';
• 連接池影響：連接復用可能導致臨時表殘留，需顯式DROP TEMPORARY TABLE

7. 性能優化建議

1. 索引策略：

   CREATE INDEX idx_salary ON #EmployeeTemp(salary);  -- 臨時表索引
   

2. 控制規模：僅保留必要字段，避免SELECT * INTO
3. 替代方案：
   • 簡單查詢優先使用子查詢或CTE（公共表表達式）
   • 頻繁使用考慮內存表（ENGINE=MEMORY）

最佳實踐：在存儲過程中使用臨時表后顯式刪除，避免長期連接的內存累積：

DROP TEMPORARY TABLE IF EXISTS #EmployeeTemp;