基于MySQL游标的多列分页 [英] MySQL cursor based pagination with multiple columns

问题描述

我有一些要使用基于游标的分页查询的表，但它需要适用于多列.

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.

这篇关于基于MySQL游标的多列分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！