基于Cursor的无Offset分页问题：TV观看数据分页异常修复

Let's break down what's wrong with your current approach and fix the cursor logic properly.

The Core Issue #

Your WHERE clause isn't aligned with your sort order (mins_watching_tv DESC, id ASC). Using mins_watching_tv <= 60 OR id=10 pulls all rows with mins_watching_tv <=60 (including ones from earlier batches) instead of targeting only rows that come after (or at) your cursor position in the sorted result set.

Correct Cursor Logic #

When paginating with a composite sort key ([column1 DESC, column2 ASC]), your cursor needs to use both columns to define the "starting point" correctly. For your sort order, a row should be included in the next batch if:

• Its mins_watching_tv is less than the cursor's mins_watching_tv (since we're sorting descending, smaller values come later), OR
• Its mins_watching_tv is equal to the cursor's value, and its id is greater than or equal to the cursor's id (since same-duration rows are sorted by ascending id, so higher ids come after the cursor).

Fixed Queries for Each Batch #

Let's apply this to your batches:

Batch 1 (Initial Load)

This part was correct—fetch the first 6 records, return the first 5 to the user, and use the 6th as your cursor:

-- Batch 1: Returns 2,17,1,16,15,5 (return first 5, cursor is id=5, mins=60)
SELECT * FROM tv_watchers 
ORDER BY mins_watching_tv DESC, id ASC 
LIMIT 6;

Batch 2 (Cursor: id=5, mins=60)

Replace your incorrect WHERE clause with the composite condition:

-- Batch 2: Returns 5,6,7,8,9,10 (return first 5, cursor is id=10, mins=60)
SELECT * FROM tv_watchers 
WHERE (mins_watching_tv < 60) OR (mins_watching_tv = 60 AND id >= 5)
ORDER BY mins_watching_tv DESC, id ASC 
LIMIT 6;

Batch 3 (Cursor: id=10, mins=60)

Now this will correctly fetch rows starting from the cursor position:

-- Batch 3: Returns 10,11,12,13,20,3 (return first 5, cursor is id=3, mins=XX)
SELECT * FROM tv_watchers 
WHERE (mins_watching_tv < 60) OR (mins_watching_tv = 60 AND id >= 10)
ORDER BY mins_watching_tv DESC, id ASC 
LIMIT 6;

Batch 4 (Final Batch)

Using the cursor from Batch 3 (id=3, whatever its mins_watching_tv value is), the query will return the remaining rows:

-- Batch 4: Returns 3,4,14,19,18
SELECT * FROM tv_watchers 
WHERE (mins_watching_tv < [cursor_mins]) OR (mins_watching_tv = [cursor_mins] AND id >= 3)
ORDER BY mins_watching_tv DESC, id ASC 
LIMIT 6;

Since there are only 5 rows left, this will return exactly the final batch you expect.

Why This Works #

By combining both sort columns in the WHERE clause, you're precisely defining the boundary of your pagination. This avoids pulling duplicate rows from earlier batches and ensures you only get records that come after your cursor in the sorted result set—critical when you have duplicate values in your primary sort column (like multiple rows with mins_watching_tv=60).

内容的提问来源于stack exchange，提问作者Wonka