MySQL 用戶變數與臨時表：查詢中間結果暫存的兩種模式對比

查詢結果需要「暫存」給後續語句用？用戶變數 vs 臨時表

場景：後續查詢依賴前一查詢的聚合結果

如：查出最高分數，再查該分數的所有用戶。

方案一：用戶變數（記憶體級，會話級）

-- 1. 將聚合結果存入用戶變數
SET @max_score = (SELECT MAX(FScoreCount) FROM Tbl_User);

-- 2. 後續查詢直接引用
SELECT * FROM Tbl_User WHERE FScoreCount = @max_score;

-- 變數特性：
-- @var_name 格式，會話私有
-- 類型自動推斷
-- 連線斷開自動銷毀

方案二：臨時表（磁碟/內存級，會話級）

-- 1. 建立臨時表存儲中間結果
CREATE TEMPORARY TABLE tmp_max_score AS 
SELECT MAX(FScoreCount) AS max_score FROM Tbl_User;

-- 2. 後續查詢關聯或子查詢
SELECT u.* FROM Tbl_User u
JOIN tmp_max_score t ON u.FScoreCount = t.max_score;

-- 或子查詢
SELECT * FROM Tbl_User 
WHERE FScoreCount = (SELECT max_score FROM tmp_max_score);

-- 臨時表特性：
-- CREATE TEMPORARY TABLE ... 只在當前會話可見
-- 會話結束/斷開自動 DROP
-- 可建索引、支援複雜查詢
-- 同名永久表被隱藏

深度對比：選哪個？

進階：MySQL 8.0 CTE (Common Table Expressions) — 現代最佳選擇

WITH max_score_cte AS (
    SELECT MAX(FScoreCount) AS max_score FROM Tbl_User
)
SELECT u.* FROM Tbl_User u
JOIN max_score_cte m ON u.FScoreCount = m.max_score;

• 語句級作用域，無需建表/清理
• 可遞迴、可多次引用
• 優化器可內聯/物化，效能通常優於臨時表
• 首選方案（MySQL 8.0+）

實戰選擇決策樹

需要暫存中間結果？
├─ MySQL 8.0+？ → WITH CTE (首選)
├─ 單一標量值（MAX/COUNT/FLAG）？ → @用戶變數
├─ 結果集需多次複用/建索引/JOIN？ → CREATE TEMPORARY TABLE
└─ 老版本/跨語句複用？ → 臨時表

用對暫存方式，讓複雜查詢邏輯清晰、效能最優。