MySQL subquery 的限制...

官方文件參考 MySQL 5.5 的「Restrictions on Subqueries」這篇。

直接拿官方的範例:

SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);

這會出現 subquery 不支援 LIMIT 的錯誤訊息:

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

如果試著用 Google 找解法,會找到用 temporary table 解決的方法:

SELECT * FROM t1 WHERE s1 IN (SELECT * FROM (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1) AS t);

這個方法是可以用啦,但還是讓人很囧啊 XD

另外一個 subquery 對效能的影響是 SQL-92 定義 WHERE 裡 subquery 的行為:(參考 sql1992.txt)

2) Each <subquery> in the <search condition> is effectively executed for each row of T and the results used in the application of the <search condition> to the given row of T. If any executed <subquery> contains an outer reference to a column of T, then the reference is to the value of that column in the given row of T.

因為如此,並不是想像中「先算完 subquery 再拿結果算前面」...

在「MySQL Limitations Part 3: Subqueries」也有一些討論可以看 (在 comment 裡),雖然文章有點舊了... 把這部份 subquery 的結果自己拉出來組 SQL query 可能會比較快。

This entry was posted in Computer, Database, Murmuring, MySQL, Software and tagged , , , . Bookmark the permalink.

One Response to MySQL subquery 的限制...

  1. 健瑋 黃 says:

    這裡的 IN 不能直接用 '=' 嗎

Leave a Reply

Your email address will not be published. Required fields are marked *