MySQL 子查詢 IN 子句中使用 LIMIT 的正確姿勢

直接在 IN (SELECT ... LIMIT n) 中使用 LIMIT 會報錯：

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, uid DESC LIMIT 0,10
);

正確解法：套用衍生表（Derived Table）

將帶 LIMIT 的子查詢包裝在一層 SELECT ... FROM ( ... ) AS tmp 中：

SELECT infor.name, infor.phone, infor.add,
       mconfig.mname, mapply.acount,
       FROM_UNIXTIME(mapply.atime, '%Y-%m-%d') AS atime
FROM mapply
RIGHT JOIN infor ON mapply.uid = infor.uid
INNER JOIN mconfig ON mapply.mid = mconfig.mid
WHERE mapply.aid IN (
  SELECT aid FROM (
    SELECT `aid` FROM `mapply` 
    WHERE state = 1 
    ORDER BY `atime`, `uid` DESC 
    LIMIT 0,10
  ) AS `tp`
);

為什麼這樣可行？

• MySQL 將內層 SELECT ... LIMIT 物質化為一個臨時表（衍生表），外層 IN 則是對這個臨時表查詢，繞過了語法限制。
• 從 MySQL 8.0.21 起，優化器會嘗試將衍生表合併，但語法依然合法。

效能提示

• 衍生表無索引，若外層表很大，建議改用 JOIN：

SELECT ...
FROM mapply
JOIN (
  SELECT aid FROM mapply WHERE state = 1 ORDER BY atime, uid DESC LIMIT 10
) AS tmp ON mapply.aid = tmp.aid
RIGHT JOIN infor ON ...
INNER JOIN mconfig ON ...;

• 確保 mapply(state, atime, uid) 有合適的複合索引，避免 filesort。

總結

IN 子查詢不可直接 LIMIT → 用衍生表包裝 → 或改寫為 JOIN 提升效能。