如何在每次执行SQL查询时选择随机唯一记录 [英] how to select random unique records on each execution of the SQL Query

问题描述

我有一个表"masterurls"，它有超过100万条记录.我想在每次执行查询时获取随机记录.它不应具有先前执行中获取的任何记录.我已经有了这个查询:

SELECT m.url FROM masterurls ORDER BY RAND() LIMIT 200

问题是上述查询仅返回前200条记录，并且每次都将其随机化.

解决方案

由于可以将种子参数传递给RAND()函数，因此可以通过在第一页之前生成种子来分页"随机结果.

示例代码: 对于首页(因语言而异):

int seed = Math.abs(new Random().nextInt());

SQL查询:

SELECT url FROM masterurls ORDER BY RAND({seed}) LIMIT 200;

将种子存储在某个地方(对于基于Web的应用程序，您可以使用url参数或会话). 对于下一页:

SELECT url FROM masterurls ORDER BY RAND({seed}) LIMIT 200 * {pageNumber}, 200;

注意:按RAND()排序是一项繁重的操作，您最好将索引列与url的哈希码一起存储，然后再使用基于模块的函数或其他随机函数.

I have a table "masterurls" it has morethan 1 million records. I want to fetch random records each time the query executed. It should not have any of the records that were fetched in previous executions. I'm already have this query:

SELECT m.url FROM masterurls ORDER BY RAND() LIMIT 200

The problem is the above query returns only first 200 hundred records and randomizes it each time.

解决方案

Since you can pass a seed parameter to the RAND() function, you can "paginate" the random results by generating a seed before the first page.

Sample code: For the first page (varies by language):

int seed = Math.abs(new Random().nextInt());

SQL query:

SELECT url FROM masterurls ORDER BY RAND({seed}) LIMIT 200;

Store the seed somewhere (for web-based applications you can use a url parameter or session). For the next pages:

SELECT url FROM masterurls ORDER BY RAND({seed}) LIMIT 200 * {pageNumber}, 200;

Note: Sorting by RAND() is a heavy operation, you might be better off storing a indexed column with the Hash Code of the url, then using a module-based or other random functions.

这篇关于如何在每次执行SQL查询时选择随机唯一记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！