MySQL 使用 LIMIT 时获取总行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12887266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get total number of rows when using LIMIT?
提问by Favourite Onwuemene
Possible Duplicate:
Find total number of results in mySQL query with offset+limit
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.
我有一个非常复杂的 sql 查询，它返回分页的结果。问题是在 LIMIT 之前获得总行数我必须运行两次 sql 查询。第一次没有限制子句来获取总行数。sql 查询真的很复杂，我认为它们必须是一种更好的方法，而无需两次运行查询。
回答by Favourite Onwuemene
Luckily since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWSoption in your query which will tell MySQL to count total number of rows disregarding LIMITclause. 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_ROWSoption just after SELECTand in second query you need to use FOUND_ROWS()function to get total number of rows. Queries would look like this:
幸运的是，从 MySQL 4.0.0 开始，您可以SQL_CALC_FOUND_ROWS在查询中使用选项，它会告诉 MySQL 计算总行数而忽略LIMIT子句。您仍然需要执行第二个查询来检索行数，但它是一个简单的查询，不像检索数据的查询那么复杂。用法很简单。在您的主查询中，您需要在第二个查询SQL_CALC_FOUND_ROWS之后添加选项SELECT，您需要使用FOUND_ROWS()函数来获取总行数。查询将如下所示：
SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;
SELECT FOUND_ROWS();
The only limitation is that you must call second query immediately after the first one because SQL_CALC_FOUND_ROWSdoes 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不会在任何地方保存行数。尽管此解决方案还需要两次查询，但速度要快得多，因为您只执行一次主查询。您可以在 MySQL 文档中阅读有关SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 的更多信息。
EDIT:You should note that in most cases running the query twice is actually faster than SQL_CALC_FOUND_ROWS. see here
编辑：您应该注意到，在大多数情况下，两次运行查询实际上比SQL_CALC_FOUND_ROWS. 看这里
EDIT 2019:
编辑 2019：
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.
SQL_CALC_FOUND_ROWS 查询修饰符和随附的 FOUND_ROWS() 函数自 MySQL 8.0.17 起已弃用，并将在未来的 MySQL 版本中删除。
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows
It's recommended to use COUNTinstead
建议COUNT改用
SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT COUNT(*) WHERE id > 100;
