SQL:计算使用LIMIT查询时匹配结果的数量 [英] SQL: Counting number of matching results when using a LIMIT query
问题描述
说明我的MySQL数据库中的 users
表包含非常多的条目。
Say the users
table in my MySQL DB contains a very large number of entries.
通过所有的用户,但我想一次只做一个块(即使用 LIMIT
和 OFFSET
) :
I need to go through all the users, but I want to do it only chunks at a time (i.e. using LIMIT
and OFFSET
):
SELECT * FROM users LIMIT 100 OFFSET 200
是否可以知道在查询中匹配的用户总数,但只返回其中的LIMIT个数?
Is it possible to know the total number of users matched in the query, but only return a LIMIT number of them ?
换句话说,提前知道用户的总数,而不进行单独的查询?
In other words, is it possible for me to know in advance the total number of users there are, without making a separate query?
推荐答案
您可以使用 SQL_CALC_FOUND_ROWS
和 FOUND_ROWS )
:
You can do it in (almost) one query, using SQL_CALC_FOUND_ROWS
and FOUND_ROWS()
:
SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 100 OFFSET 200;
SELECT FOUND_ROWS();
虽然最终还是有两个结果集,但实际查询只执行一次,从重复的编码和可能的一些浪费的CPU周期。
While you still end up with two result sets, the actual query is only executed once, which saves you from repetitive coding and possible some wasted CPU cycles.
这篇关于SQL:计算使用LIMIT查询时匹配结果的数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!