MySQL 快速从 600K 行中随机选择 10 行

2021-11-20 00:00:00 random sql mysql

如何最好地编写从总共 600k 行中随机选择 10 行的查询?

How can I best write a query that selects 10 rows randomly from a total of 600k?

推荐答案

处理多种情况的好帖子，从简单到间隙，再到非均匀间隙.

A great post handling several cases, from simple, to gaps, to non-uniform with gaps.

http://jan.kneschke.de/projects/mysql/order-by-rand/

对于大多数一般情况，您的操作方法如下:

For most general case, here is how you do it:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

这里假设 id 的分布是相等的，并且 id 列表中可以有间隙.更高级的例子见文章

This supposes that the distribution of ids is equal, and that there can be gaps in the id list. See the article for more advanced examples

相关文章