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

問題重現

直接在 IN (subquery) 中加 LIMIT 報錯：

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

錯誤寫法

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

正確寫法：衍生表包裹

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 先執行，產生結果集。
2. 外層 SELECT aid FROM ( ... ) AS tp 將其物質化為**衍生表** (臨時表)。
3. 最外層 IN 讀取衍生表，此時無 LIMIT，語法合法。

更優替代：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;

• ✅ 語義更清晰：直接關聯前 10 筆申請單。
• ✅ 避免 IN 子查詢潛在優化劣勢 (早期版本)。
• ✅ 可直接使用 tp 的其他欄位 (如排序鍵)。

優化建議

• 確保 mapply(state, atime) 有**複合索引**支援 WHERE state=1 ORDER BY atime DESC LIMIT 10。
• MySQL 5.7/8.0 derived_merge=on 會自動嘗試合併衍生表，效能等同 JOIN。
• 大偏移分頁 (LIMIT 100000, 10) 需改用游標/延遲關聯，不適用此法。

總結

IN 子查詢 + LIMIT → 包一層衍生表 (AS tp) 繞過語法限制；實務首選 JOIN 衍生表 寫法，優化器友善、語義明確。