基于 MySQL 游标的多列分页
我想使用基于游标的分页来查询一些表，但它需要申请多列.
I have some table that I want to query using cursor based pagination, but it needs to apply for multiple columns.
让我们举一个使用 2 列的简化示例 - 我像这样获取第一页:
Let's take a simplified example of using 2 columns - I fetch the first page like this:
SELECT column_1, column_2
FROM table_name
ORDER BY column_1, column_2
LIMIT 10
得到结果后，我可以根据最后一行获取下一页.假设最后一行是 column_1 = 5, column_2 = 8.我想做这样的事情:
After I get the results, I can fetch the next page based on the last row. Let's say the last row was column_1 = 5, column_2 = 8. I'd like to do something like this:
SELECT column_1, column_2
FROM table_name
WHERE column_1 > 5 AND column_2 > 8
ORDER BY column_1, column_2
LIMIT 10
但这显然是错误的.它将过滤掉具有 column_1 = 5, column_2 = 9 的行(因为 column_1 上的过滤器)或具有 column_1 = 6, column_2 的行= 6(因为column_2 上的过滤器)
But this is obviously wrong. It would filter out a row that has column_1 = 5, column_2 = 9 (because of the filter on column_1) or a row that has column_1 = 6, column_2 = 6 (because of the filter on column_2)
我可以做这样的事情来避免这个问题:
I can do something like this to avoid the problem:
SELECT column_1, column_2
FROM table_name
WHERE column_1 > 5
OR (column_1 = 5 AND column_2 > 8)
ORDER BY column_1, column_2
LIMIT 10
但是对于超过 2 列，这变得非常麻烦且容易出错...
But this becomes very cumbersome and error prone for more than 2 columns...
此外，我的用例包括多种类型的列(INT UNSIGNED 和 BINARY)，但都具有可比性
Also, my use case includes columns of multiple types (INT UNSIGNED and BINARY), but all are comparable
您有什么建议吗?
谢谢！
推荐答案
如果(不幸命名的)列 Column_1 是唯一的，你可以这样做:
If (the unfortunately named) column Column_1 is unique, you could just do:
WHERE Column_1 > :last_retrieved_value
从问题来看，Column_1 似乎不是唯一的，但是 (Column_1,Column_2) 元组是唯一的.
From the question, it appears that Column_1 is not unique, but the (Column_1,Column_2) tuple is unique.
下一页"查询的一般形式，按这两列排序，使用这两列的最后检索值，将是...
The general form for a "next page" query, ordering by those two columns, using the last retrieved values for those two columns, would be...
(Column1,Column2) > (:lrv_col1,:lrv_col2)
(lrv = 从上一个查询检索到的最后一行保存的值)
(lrv = value saved from the last row retrieved by the previous query)
要在 MySQL 中编写该条件，我们可以像您展示的那样:
To write that condition in MySQL, we can do that like you have shown:
WHERE t.Column_1 > :lrv_col1
OR ( t.Column_1 = :lrv_col1 AND t.Column_2 > :lrv_col2 )
或者，我们可以这样写，我更喜欢这样，因为 MySQL 被 OR 条件混淆并使用错误索引的可能性要小得多......
Or, we could write it like this, which I prefer, because there's much less of a chance for MySQL to get confused by the OR condition and use the wrong index...
WHERE t.Column_1 >= :lrv_col1
AND ( t.Column_1 > :lrv_col1 OR t.Column_2 > :lrv_col2 )
ORDER BY t.Column_1, t.Column_2
LIMIT n
要将其扩展到三列，检查条件...
To extend that to three columns, to check the condition...
(c1,c2,c3) > (:lrv1,:lrv2,:lrv3)
我们像处理两列一样处理它，首先处理 c1，然后像两列一样将其分解:
We handle it just like in the case of two columns, handling c1 first, breaking it out just like the two columns:
WHERE c1 >= :lrv1
AND ( c1 > :lrv1 OR ( ... ) )
ORDER BY c1, c2, c3
LIMIT n
现在占位符 ...(之前只检查 c2 的地方，实际上又只是两列的另一种情况.我们需要检查: (c2,c3) > (lrv2,lrv3)，所以我们可以使用相同的模式扩展它:
And now that placeholder ... (where would have had just the check on c2 before, is really again just another case of two columns. We need to check: (c2,c3) > (lrv2,lrv3), so we can expand that using the same pattern:
WHERE c1 >= :lrv1
AND ( c1 > :lrv1 OR ( c2 >= :lrv2
AND ( c2 > :lrv2 OR c3 > :lrv3 )
)
)
ORDER BY c1,c2,c3
LIMIT n
我同意扩展可能看起来有点乱.但它确实遵循一个非常规律的模式.类似地，我们可以在四列上表达条件...
I agree that the expansion may look a little messy. But it does follow a very regular pattern. Similarly, we could express the condition on four columns...
(c1,c2,c3,c4) > (:lrv1,:lrv2,:lrv3,:lrv4)
我们只取三列的内容，我们需要展开 c3 >:lrv3 将其替换为 ( c3 >= :lrv3 AND ( c3 > :lrv3 OR c4 > :lrv4 ) )
We just take what we have for the three columns, and we need to expand c3 > :lrv3 to replace it with ( c3 >= :lrv3 AND ( c3 > :lrv3 OR c4 > :lrv4 ) )
WHERE c1 >= :lrv1
AND ( c1 > :lrv1 OR ( c2 >= :lrv2
AND ( c2 > :lrv2 OR ( c3 >= :lrv3
AND ( c3 > :lrv3 OR c4 > :lrv4 )
)
)
)
)
ORDER BY c1,c2,c3,c4
LIMIT n
作为对未来读者的帮助，我会评论这个块，并表明意图......
As an aid the the future reader, I would comment this block, and indicate the intent ...
-- (c1,c2,c3,c4) > (lr1,lr2,lr3,lr4)
如果 MySQL 允许我们像这样表达比较，那就太好了.不幸的是，我们必须将其扩展为 MySQL 能够理解的内容.
And it would be nice if MySQL would allow us to express the comparison just like that. Unfortunately, we have to expand that into something MySQL understands.
相关文章