获取使用LIMIT时的总行数? [英] Get total number of rows when using LIMIT?
问题描述
Possible Duplicate:
Find total number of results in mySQL query with offset+limit
我有一个非常复杂的sql查询，它返回分页的结果.问题是要在LIMIT之前获取总行数，我必须两次运行sql查询.第一次不使用limit子句获取总行数. sql查询确实很复杂，我认为它们必须是一种更好的方法，而无需两次运行查询.
I have a very complex sql query which returns results that are paginated. The problem is to get the total row count before LIMIT I have to run the sql query twice. The first time without the limit clause to get the total row count. The sql query is really complex and I think they must be a better way of doing this without running the query twice.
推荐答案
幸运的是，自MySQL 4.0.0起，您可以在查询中使用SQL_CALC_FOUND_ROWS选项，该选项将告诉MySQL忽略LIMIT子句来计算总行数.您仍然需要执行第二个查询才能检索行数，但这是一个简单的查询，并不像检索数据的查询那样复杂.
用法很简单.在主查询中，您需要在SELECT之后添加SQL_CALC_FOUND_ROWS选项，在第二个查询中，您需要使用FOUND_ROWS()函数来获取总行数.查询将如下所示:
Luckily since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWS option in your query which will tell MySQL to count total number of rows disregarding LIMIT clause. You still need to execute a second query in order to retrieve row count, but it’s a simple query and not as complex as your query which retrieved the data.
Usage is pretty simple. In you main query you need to add SQL_CALC_FOUND_ROWS option just after SELECT and in second query you need to use FOUND_ROWS() function to get total number of rows. Queries would look like this:
SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;
SELECT FOUND_ROWS();
唯一的限制是您必须在第一个查询之后立即调用第二个查询，因为SQL_CALC_FOUND_ROWS不会在任何地方保存行数.
尽管此解决方案还需要两个查询，但速度要快得多，因为您只执行一次主查询.
您可以阅读有关 SQL_CALC_FOUND_ROWS和FOUND_ROWS()的更多信息>在MySQL文档中.
The only limitation is that you must call second query immediately after the first one because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.
Although this solution also requires two queries it’s much faster, as you execute the main query only once.
You can read more about SQL_CALC_FOUND_ROWS and FOUND_ROWS() in MySQL docs.
编辑:您应该注意，在大多数情况下，两次运行查询实际上比SQL_CALC_FOUND_ROWS快.参见此处
You should note that in most cases running the query twice is actually faster than SQL_CALC_FOUND_ROWS. see here
EDIT 2019:
从MySQL 8.0.17开始不推荐使用SQL_CALC_FOUND_ROWS查询修饰符和附带的FOUND_ROWS()函数，并将在以后的MySQL版本中将其删除.
The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version.
https://dev.mysql. com/doc/refman/8.0/en/information-functions.html#function_found-rows
建议改用COUNT
SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT COUNT(*) WHERE id > 100;
这篇关于获取使用LIMIT时的总行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
