Solutions like Jeremies:
> 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 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:
Let’s examine the query in more detail.
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
- The LIMIT clause picks the first row in the result set sorted randomly.
用中文来说：相当于为当前表加了一个列，这个列储存的是随机数，执行 SQL 时为每一行都分配一个随机数，然后根据随机数这一列排序。