快速更改数据库内容的分页 [英] Pagination on fast changing database content

问题描述

我需要使用分页读取MS SQL数据库表的内容，即获取N行的第一页，然后是N行的第二页等。

I need to read the content of a MS SQL database table using pagination, i.e. fetching the first page of N rows, then the second page of N rows and so on.

如果数据库的内容在分页期间发生了显着变化，那么一个简单的分页查询如下：

If the content of the database is changing significantly during the pagination, a simple pagination query like:

SELECT *
FROM (SELECT a.*,
    ROW_NUMBER() OVER (ORDER BY id) AS rnum
    FROM articles a)
WHERE rnum <= 10
AND   rnum >= 6;

可能无法正常工作。已插入的行可以跳过，也可能导致后续行重复，删除的行可能会导致后续行被跳过。

may not work reliably. Inserted rows can be skipped or can cause subsequent rows to be repeated and deleted rows can cause subsequent rows to be skipped.

我可以通过执行以下操作来避免此类问题：

I could avoid such problems by doing any of the following:

1. 在整个分页期间锁定行以进行更新 - 太
   限制


2. 在分页之前将行复制到临时表中


3. 通过行号和排序值的组合选择
   显示在上一页的结尾，根据更改的表格在
   适当的位置恢复，但仍然获得
   只有后N行

1. Lock the rows against update during the entire pagination - too restrictive
2. Copy the rows to a temporary table before paging - too slow
3. Select by a combination of row number and the sorted value that was displayed at the end of the previous page, resuming at an appropriate place based on the changing table, but still getting only the next N rows

我喜欢第三个解决方案，但是当排序列中有重复的值时，我很难实现。

I kind of like the 3rd solution, but I find it difficult to implement when there are duplicate values in the sort column(s).

例如，假设我有一个按降序排序的文章列表。如果评分相同，则按升序ID（ID是唯一的）排序：

For example, let's assume I have a list of articles sorted by descending rating. If the rating is the same, they are sorted by ascending ID (the IDs are unique):

ID      RATING
9       34
3       32
6       32
8       32
12      32
1       25
2       23

现在，我想要3篇文章的页面，这意味着第一页将有第9,3和6条。这是通过查询前3篇文章表单

Now, I want pages of 3 articles, which means the first page will have articles 9, 3 and 6. This is done by querying the top 3 articles form the sorted list.

现在，我想从接下来的3篇文章中恢复第8条，使用文章ID作为标记来恢复。

Now, I want to take the next 3 articles resuming from article 8, using the article ID as marker for where to resume.

如果我告诉数据库采取第8条的声誉，而不是采取3个声誉低于这个的文章，我会跳过第12条。

If I told the database to take the reputation of article 8 and than take the 3 articles which have reputation lower than that, I would skip article 12.

如果我告诉数据库采取第8条的声誉，而不是采取3条声誉低于或等于的文章，我将重复第3和第6条。

If I told the database to take the reputation of article 8 and than take the 3 articles which have reputation lower than or equal to that, I would repeat article 3 and 6.

我可以使用什么SQL查询（或查询组合）从第8条恢复分页，使用文章ID作为恢复标记？

What SQL query (or combination of queries) can I use to resume the pagination from article 8, using the article ID as marker for where to resume?

推荐答案

将注释转换为answer，因为它似乎解决了用户的问题。

Converting comment to answer, since it seems to have solved the user's question.

该用户的结果（例如您可以将所有ID发送到应用程序，并且每次抓取时一次只能拉取3篇文章），但也可以添加一个免责声明，如果他们花半个小时滚动列表，列表可能不再准确。

So it seems like you should cache the result for that user (e.g. can you send all the IDs to the app and have it just pull 3 articles at a time on each fetch), but also add a disclaimer that if it takes them half an hour to scroll through the list, the list may no longer be accurate.

这篇关于快速更改数据库内容的分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！