MySQL 子查詢 IN 子句中使用 LIMIT 解決方案：衍生表包裹法

問題現象

直接在 IN (subquery) 中使用 LIMIT 會報錯：

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

MySQL 禁止在 IN/ALL/ANY/SOME 子查詢中出現 LIMIT。

錯誤示範

-- ❌ 報錯
SELECT * FROM mapply
WHERE aid IN (
    SELECT aid FROM mapply WHERE state = 1 ORDER BY atime DESC LIMIT 10
);

正確寫法：衍生表 (Derived Table) 包裹

-- ✅ 正確：子查詢外層套一層 SELECT * FROM ( ... ) AS t
SELECT 
    i.name, i.phone, i.addr,
    c.mname, a.acount, FROM_UNIXTIME(a.atime, '%Y-%m-%d') AS atime
FROM mapply a
RIGHT JOIN infor i ON a.uid = i.uid
INNER JOIN mconfig c ON a.mid = c.mid
WHERE a.aid IN (
    SELECT aid FROM (
        SELECT aid FROM mapply WHERE state = 1 ORDER BY atime DESC LIMIT 10
    ) AS tp
);

原理解析

1. 內層子查詢 SELECT aid ... LIMIT 10 先執行，產生最多 10 筆 aid 結果集。
2. 外層 SELECT aid FROM ( ... ) AS tp 將其物質化為**衍生表** (臨時表)。
3. 最外層 IN 子查詢讀取衍生表的 aid，此時**無 LIMIT 限制**，符合語法規則。

MySQL 5.6+ 會嘗試將衍生表合併 (Derived Merge)，若無法合併則物質化到臨時表，效能尚可接受。

替代方案：JOIN 替代 IN (通常更高效)

-- 使用 INNER JOIN 直接關聯限制後的結果集
SELECT 
    i.name, i.phone, i.addr,
    c.mname, a.acount, FROM_UNIXTIME(a.atime, '%Y-%m-%d') AS atime
FROM mapply a
RIGHT JOIN infor i ON a.uid = i.uid
INNER JOIN mconfig c ON a.mid = c.mid
INNER JOIN (
    SELECT aid FROM mapply WHERE state = 1 ORDER BY atime DESC LIMIT 10
) AS tp ON a.aid = tp.aid;

• ✅ 避免 IN 子查詢的潛在效能問題 (MySQL 早期版本 IN 子查詢優化較差)。
• ✅ 語義明確：只關聯前 10 筆申請單。
• ✅ 可直接利用 tp 的其他欄位 (如排序鍵)。

適用版本與優化提示

• MySQL 5.7 / 8.0：衍生表合併優化預設開啟 (optimizer_switch=derived_merge=on)，上述寫法多會被優化為 JOIN，效能極佳。
• 大資料量時，確保 mapply(state, atime) 有**複合索引**支援 WHERE state=1 ORDER BY atime DESC LIMIT 10 避免 filesort。
• LIMIT 10 等小數值影響微乎其微；若需分頁大偏移 (如 LIMIT 100000, 10) 需改用游標/延遲關聯優化。

總結

子查詢 IN + LIMIT → 包一層衍生表 (AS tp) 即可繞過語法限制。實務上優先考慮 JOIN 衍生表 寫法，語義更清晰、優化器友善度更高。