使用 LIMIT 时获取总行数? [英] Get total number of rows when using LIMIT?

问题描述

可能的重复:
使用 offset+limit 查找 mySQL 查询中的结果总数

我有一个非常复杂的 sql 查询，它返回分页的结果.问题是在 LIMIT 之前获得总行数我必须运行两次 sql 查询.第一次没有限制子句来获取总行数.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 计算总行数，而不考虑 SQL_CALC_FOUND_ROWScode>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

编辑 2019 年:

SQL_CALC_FOUND_ROWS 查询修饰符和随附的 FOUND_ROWS() 函数自 MySQL 8.0.17 起已弃用，并将在未来的 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屋！