对 SQL ORDER BY RAND() 的理解

如何随机获取数据库中的行呢?

https://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql

Solutions like Jeremies:

1
2
> SELECT * FROM table ORDER BY RAND() LIMIT 1
>

>

work, but they need a sequential scan of all the table (because the random value associated with each row needs to be calculated - so that the smallest one can be determined), which can be quite slow for even medium sized tables.

MySQL select random records using ORDER BY RAND()

MySQL does not have any built-in statement to select random records from a database table. In order to accomplish this, you use the RAND function. The following query selects a random record from a database table:

1
2
3
4
5
6
SELECT 
*
FROM
tbl
ORDER BY RAND()
LIMIT 1;

Let’s examine the query in more detail.

  • The RAND() function generates a random value for each row in the table.
  • The ORDER BY clause sorts all rows in the table by the random number generated by the RAND()function.
  • The LIMIT clause picks the first row in the result set sorted randomly.

用中文来说:相当于为当前表加了一个列,这个列储存的是随机数,执行 SQL 时为每一行都分配一个随机数,然后根据随机数这一列排序。