不在mysql子查询限制中

Does anybody have any ideas how I can get around a #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' error?

My query is below ( I've read that I can upgrade mysql but this isn't possible):

$query = @mysql_query("SELECT * FROM posts 
                       WHERE postid NOT IN 
                       ( SELECT postid FROM log 
                         ORDER BY posted DESC
                         LIMIT 10) 
                       ORDER BY (RAND() * Multiplier) 
                       LIMIT 1");

According to this bug, you can use this ugly workaround:

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

You can rewrite your query using JOIN:

SELECT   *
FROM     posts NATURAL LEFT JOIN (
  SELECT postid FROM log ORDER BY posted DESC LIMIT 10
) t
WHERE    t.postid IS NULL
ORDER BY RAND()
LIMIT    1

Be aware, however, that ORDER BY RAND() is very expensive. Not only must a random value be calculated for each record, but then a sort must be performed on the results. Indexes are of no use.

You would fare better if you had a column col containing unique integers, then with an index on col you can very rapidly obtain a random record with:

SELECT    *
FROM      posts NATURAL LEFT JOIN (
  SELECT postid FROM log ORDER BY posted DESC LIMIT 10
) t JOIN (
  SELECT RAND() * MAX(col) AS rand FROM posts
) r ON posts.col >= r.rand
WHERE     t.postid IS NULL
LIMIT     1

Note that the uniformity of such "randomness" will depend on the distribution of the integers within col after any other filtering has taken place.