使用相同的查询获取总数在mysql中的限制? [英] get total for limit in mysql using same query?

问题描述

我正在使用分页方法，我所做的是: 第一个查询将计算所有结果，第二个查询将使用LIMIT

I am making a pagination method, what i did was: First query will count all results and the second query will do the normal select with LIMIT

从技术上讲，有什么方法可以完成我所完成的工作，但是只需一个查询?

Is there technically any way to do this what I've done, but with only one query?

我现在所拥有的:

SELECT count(*) from table
SELECT * FROM table LIMIT 0,10

推荐答案

没有人真正提到这一点，但是使用SQL_CALC_FOUND_ROWS技术的正确方法是这样的:

No one really mentions this, but the correct way of using the SQL_CALC_FOUND_ROWS technique is like this:

1. 执行查询:SELECT SQL_CALC_FOUND_ROWS * FROM `table` LIMIT 0, 10
2. 然后在此之后直接运行此查询:SELECT FOUND_ROWS().该查询的结果包含前一个查询的全部计数，即好像您没有使用LIMIT子句一样.第二个查询立即很快，因为结果已经被缓存.

1. Perform your query: SELECT SQL_CALC_FOUND_ROWS * FROM `table` LIMIT 0, 10
2. Then run this query directly afterwards: SELECT FOUND_ROWS(). The result of this query contains the full count of the previous query, i.e. as if you hadn't used the LIMIT clause. This second query is instantly fast, because the result has already been cached.

这篇关于使用相同的查询获取总数在mysql中的限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！