MySQL 臨時表 Can't reopen 錯誤原因與解決方案

臨時表基礎

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 ... FROM ... WHERE ID = current_id;

核心特性

• 僅當前連接可見，連接關閉自動 DROP
• 不同連接可同名不衝突
• 隱藏同名永久表，臨時表刪除後永久表重新可見
• 需 CREATE TEMPORARY TABLES 權限

四大限制

1. 引擎限制：僅支援 MEMORY、MyISAM、MERGE、InnoDB
2. 不支援 Cluster
3. 同一查詢不可多次引用同一臨時表 (核心坑點)

   SELECT * FROM temp_table, temp_table AS t2;
   -- ERROR 1137: Can't reopen table: 'temp_table'
   

   存儲函數/過程中不同別名/語句多次引用同樣報錯。官方 Bug #10327

4. SHOW TABLES 不顯示，RENAME TABLE 不支援 (用 ALTER TABLE ... RENAME 代替)

解決「多次引用」方案

方案一：複製表 (小數據量)

CREATE TEMPORARY TABLE tmp2 SELECT * FROM tmp1;
-- 之後用 tmp1、tmp2 分別引用

方案二：改用永久表 + 唯一前綴

CREATE TABLE `tmp_${session_id}_${random}` ...;
-- 使用後手動 DROP

方案三：子查詢/CTE 重構 (MySQL 8.0+)

WITH tmp AS (SELECT ... FROM ...)
SELECT * FROM tmp t1 JOIN tmp t2 ON ...;

清理最佳實踐

DROP TEMPORARY TABLE IF EXISTS sp_output_tmp;

顯式清理避免連接池復用時殘留數據干擾。