如何在每个查询中使带有LIMIT的SQL查询的结果不同? [英] How I make result of SQL querys with LIMIT different in each query?

问题描述

我有以下SQL:

SELECT id, url 
FROM link 
WHERE visited = false  
ORDER BY id 
LIMIT 500; 

-* 500仅是一个示例

--*500 is only a example

我正在做一个网络爬虫，并且有一张带有链接的表格.该SQL返回的链接仅是limit子句中定义的数量，而不是所有要访问的链接.

I'm making a webcrawler and there is a table with links. This SQL returns the links to visit, but dont all them, only the quantitiy defined in the limit clause.

我将使用线程，如果第一个执行此查询，它将获取前500个链接，如果第二个线程执行相同的查询，它将获取下500个链接.换句话说，第一个主题获得链接1到500，第二个线程获得501到1000，第三个线程获得1001到1500，依此类推.

I will use threads and if the first execute this query, it will obtains the first 500 links, if the second thread execute the same query, it will obtains the next 500 links. In other words, first thead obtains links 1 to 500, second thread obtains 501 to 1000, third thread obtains 1001 to 1500 and so on.

MAYBE不需要使用线程，但是可以在运行同一应用程序的不同计算机上使用.我不知道是否需要在表中创建一个字段来设置该行被另一个线程/应用程序使用，或者我只能通过SQL/DBMS来执行此操作.我正在使用PostgreSQL.

MAYBE it's dont need works with threads, but with different computers running the same application. I dont know if a need create a field in the table to set that row was in use by another thread/application or I can do this only with SQL/DBMS. I'm using PostgreSQL.

换句话说，我将需要锁定一个被查询的行，以使其不会出现在另一个查询中.

推荐答案

您是否尝试过更新/返回?

Have you tried for update/returning?

update link
set visiting = true
from (
    select id
    from link
    where visiting = false
    and visited = false
    limit 500
    for update
    ) as batch
where batch.id = link.id
returning *;

这篇关于如何在每个查询中使带有LIMIT的SQL查询的结果不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！