随机MySQL查询？ 或随机化结果？

Question

So, I want to pull a query on a MySQL database, however, I want the result to be randomized - that is, I don't want the same thing to come up in the same order every time. This is because I will only be listing the first 6 items of the query (or 2 items, in some cases), and I want all the contents of the database to have a chance to appear.

Is it possible to do this in MySQL or would I have to use PHP to do it?

Answer 1

For a fast approach try this:

SELECT  *
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 10
        FROM    t_random
        ) vars
STRAIGHT_JOIN
        (
        SELECT  r.*,
                @lim := @lim - 1
        FROM    t_random r
        WHERE   (@cnt := @cnt - 1)
                AND RAND(20090301) < @lim / @cnt
        ) i

See this article for a detailed description of how it works:

• MySQL: Selecting a number of random rows fast

---

A simpler (but slow way) is to use ORDER BY RAND():

 SELECT *
 FROM yourtable
 ORDER BY RAND()
 LIMIT 6

From the manual:

... you can retrieve rows in random order like this:

mysql> SELECT * FROM tbl_name ORDER BY RAND();

ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows:

mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d
    -> ORDER BY RAND() LIMIT 1000;

RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version.

---

If you have a unique id field which is incrementing from 1 to n without any gaps you can improve performance even more by choosing six random numbers in [1, n] and fetching the six rows with those ids. This avoids scanning the full table.