没有双重查询的 MySQL 分页? [英] MySQL pagination without double-querying?

问题描述

我想知道是否有办法从 MySQL 查询中获取结果数量，同时限制结果.

分页的工作方式(据我所知)，首先我做类似的事情

query = SELECT COUNT(*) FROM `table` WHERE `some_condition`

在我得到 num_rows(query) 之后，我得到了结果的数量.但是为了真正限制我的结果，我必须做第二个查询，如:

query2 = SELECT COUNT(*) FROM `table` WHERE `some_condition` LIMIT 0, 10

我的问题:无论如何都可以检索将给出的结果总数，并限制在单个查询中返回的结果?或者任何更有效的方式来做到这一点.谢谢！

解决方案

不，这是许多想要分页的应用程序必须这样做的.它可靠且防弹，尽管它进行了两次查询.但是您可以将计数缓存几秒钟，这会有很大帮助.

另一种方法是使用SQL_CALC_FOUND_ROWS 子句，然后调用SELECT FOUND_ROWS().除了您必须在之后调用 FOUND_ROWS() 之外，还有一个问题:MySQL 中的一个错误，这会影响ORDER BY 查询，使其在大型表上比两个查询的幼稚方法慢得多.>

I was wondering if there was a way to get the number of results from a MySQL query, and at the same time limit the results.

The way pagination works (as I understand it), first I do something like

query = SELECT COUNT(*) FROM `table` WHERE `some_condition`

After I get the num_rows(query), I have the number of results. But then to actually limit my results, I have to do a second query like:

query2 = SELECT COUNT(*) FROM `table` WHERE `some_condition` LIMIT 0, 10

My question: Is there anyway to both retrieve the total number of results that would be given, AND limit the results returned in a single query? Or any more efficient way of doing this. Thanks!

解决方案

No, that's how many applications that want to paginate have to do it. It's reliable and bullet-proof, albeit it makes the query twice. But you can cache the count for a few seconds and that will help a lot.

The other way is to use SQL_CALC_FOUND_ROWS clause and then call SELECT FOUND_ROWS(). apart from the fact you have to put the FOUND_ROWS() call afterwards, there is a problem with this: There is a bug in MySQL that this tickles that affects ORDER BY queries making it much slower on large tables than the naive approach of two queries.

这篇关于没有双重查询的 MySQL 分页?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！