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

问题描述

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

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`

在我得到num_rows（查询）后，我有结果数。但是实际上要限制我的结果，我必须做一个第二个查询，如：

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

我的问题：将给出的结果数，AND限制在单个查询中返回的结果？或者这样做的任何更有效的方式。谢谢！

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.

另一种方法是使用 SQL_CALC_FOUND_ROWS 子句，然后调用 SELECT FOUND_ROWS（）。除了你以后必须放置 FOUND_ROWS（）调用的事实，这是一个问题：有一个 MySQL中的一个错误，这个错误影响了 ORDER BY 查询，使得它更慢大桌子比天真的两种查询方法。

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屋！