在 MySQL 查询中使用 SELECT 和 LIMIT 时如何计算所有行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2439829/
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
How to count all rows when using SELECT with LIMIT in MySQL query?
提问by SaltLake
I've got a mysql query like this:
我有一个像这样的 mysql 查询：
SELECT A.ID, A.NAME, B.ID, B.NAME
FROM table1 A
JOIN table2 B ON ( A.ID = B.TABLE1_ID )
WHERE
cond1, cond2, ..., condN
LIMIT 10
I've got many where clauses in query. How to improve this query to get also full row count? I don't want to use one more request without LIMIT.
我在查询中有很多 where 子句。如何改进此查询以获得完整的行数？我不想在没有 LIMIT 的情况下再使用一个请求。
回答by Shuriken
回答by animuson
You can use the SQL_CALC_FOUND_ROWS with FOUND_ROWS()to count the number of results while that query is executing. Basically you just add 'SQL_CALC_FOUND_ROWS' after 'SELECT' and then run another query 'SELECT FOUND_ROWS()' after that. It is not possible to send back the count in the same query because it cannot know the count until the query is finished.
您可以使用带有 FOUND_ROWS()的SQL_CALC_FOUND_ROWS来计算执行该查询时的结果数。基本上，您只需在“SELECT”之后添加“SQL_CALC_FOUND_ROWS”，然后再运行另一个查询“SELECT FOUND_ROWS()”。不可能在同一个查询中发回计数，因为在查询完成之前它无法知道计数。
回答by Alan N
'tis 4 years since the last answer, but this is how I resolved the problem. Although SaltLake's answer produced an error for me, it did lead me to the correct answer.
自上次回答以来已经过去了 4 年，但这就是我解决问题的方式。尽管 SaltLake 的回答给我带来了错误，但它确实让我找到了正确的答案。
SELECT SQL_CALC_FOUND_ROWS * FROM wholedatabase LIMIT 0,10 UNION
SELECT 'TotalRows', FOUND_ROWS(), NULL, NULL, NULL, NULL
ORDER BY IssueDate, VolumeNo
The UNION part is very important, because it tags your desired answer (Total number of rows) that is retrieved in the SECOND Select result onto the FIRST Select results.
UNION 部分非常重要，因为它将在 SECOND Select 结果中检索到的所需答案（总行数）标记到 FIRST Select 结果上。
Another very important point is that, because a UNION is taking place, both tables must have the same number of columns in them. This usually means that you have to pad the SECOND Select with the all-important FOUND_ROWS() value, and then lots of NULL values.
另一个非常重要的一点是，因为正在发生 UNION，所以两个表中的列数必须相同。这通常意味着您必须使用非常重要的 FOUND_ROWS() 值填充 SECOND Select，然后填充大量 NULL 值。
The final result will be one command that will return 11 rows of information, with one of these rows containing the total number of rows. Obviously, you will need to exclude the additional TotalRows row when you come to using the result.
最终结果将是一个将返回 11 行信息的命令，其中一行包含总行数。显然，当您开始使用结果时，您需要排除额外的 TotalRows 行。
回答by SaltLake
Solution from http://is.php.net/manual/en/function.mysql-num-rows.php#83647
来自http://is.php.net/manual/en/function.mysql-num-rows.php#83647 的解决方案
SELECT SQL_CALC_FOUND_ROWS
'0', z.id
FROM
zoom AS z
LIMIT 0,6
UNION
SELECT
'1', FOUND_ROWS()
ORDER BY `0` DESC , RAND()
回答by Krishna
You should use
你应该使用
SELECT SQL_CALC_FOUND_ROWS A.ID, A.NAME, B.ID, B.NAME, FOUND_ROWS() as rCount
FROM table1 A
JOIN table2 B ON ( A.ID = B.TABLE1_ID )
WHERE
cond1, cond2, ..., condN
LIMIT 10
