MySQL的SQL_CALC_FOUND_ROWS 类似count(*)
在分页过程中,写法如下:
SELECT COUNT(*) FROM TABLE_NAME WHERE .....;
SELECT * FROM TABLE_NAME WHERE ..... LIMIT M,N;
SQL_CALC_FOUND_ROWS 写法如下:
SELECT SQL_CALC_FOUND_ROWS * FROM TABLE_NAME WHERE .... LIMIT M,N;
SELECT FOUND_ROWS();
查看hank表中总数据:
mysql> select count(*) from hank; +----------+ | count(*) | +----------+ | 12 | +----------+
# 没有where条件,在执行第一条SQL语句时,就会缓存所有记录数,并在FOUND_ROWS()后返回
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM hank limit 2,6; +----+------+ | id | name | +----+------+ | 3 | aaa | | 4 | aaa | | 5 | aaa | | 6 | bbb | | 7 | bbb | | 8 | bbb | +----+------+ 6 rows in set (0.00 sec) mysql> SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ | 12 | +--------------+ 1 row in set (0.00 sec)
# 有where条件,在执行第一条SQL语句时,就会缓存所有复合where条件的记录数,并在FOUND_ROWS()后返回
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM hank where name='ccc' limit 1,3; +----+------+ | id | name | +----+------+ | 10 | ccc | | 11 | ccc | | 12 | ccc | +----+------+ 3 rows in set (0.00 sec) mysql> SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ | 4 | +--------------+ 1 row in set (0.00 sec) mysql> select count(*) from hank where name='ccc'; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec)
在UNION中使用SQL_CALC_FOUND_ROWS:
1、SQL_CALC_FOUND_ROWS应该放在UNION的第一个SELECT后边(否则MySql将会提示Sql语句错误)
mysql> select SQL_CALC_FOUND_ROWS id from hank limit 5 union all select id from hank1 ; ERROR 1221 (HY000): Incorrect usage of UNION and LIMIT mysql> select SQL_CALC_FOUND_ROWS id from hank union all select id from hank1 limit 2,5; +----+ | id | +----+ | 3 | | 4 | | 5 | | 6 | | 7 | +----+ 5 rows in set (0.01 sec) mysql> select found_rows(); +--------------+ | found_rows() | +--------------+ | 27 | +--------------+ 1 row in set (0.00 sec)
2、FOUND_ROWS()函数返回的是UNION ALL的结果,如果你使用的是UNION,那么只能得到近似结果
mysql> select SQL_CALC_FOUND_ROWS id from hank union select id from hank1 limit 2,5; +----+ | id | +----+ | 3 | | 4 | | 5 | | 6 | | 7 | +----+ 5 rows in set (0.00 sec) mysql> select found_rows(); +--------------+ | found_rows() | +--------------+ | 15 | +--------------+ 1 row in set (0.00 sec)
3、如果在带UNION的SELECT语句中不包括LIMIT,那么SQL_CALC_FOUND_ROWS将会被忽略,后续使用FOUND_ROWS()函数将会返回MySql为UNION操作创建的临时表的行数
mysql> select SQL_CALC_FOUND_ROWS id from hank union select id from hank1; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | | 12 | | 13 | | 14 | | 15 | +----+ 15 rows in set (0.00 sec) mysql> select found_rows(); +--------------+ | found_rows() | +--------------+ | 15 | +--------------+ 1 row in set (0.00 sec)
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列:如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· winform 绘制太阳,地球,月球 运作规律
· AI与.NET技术实操系列(五):向量存储与相似性搜索在 .NET 中的实现
· 超详细:普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· 上周热点回顾(3.3-3.9)
· AI 智能体引爆开源社区「GitHub 热点速览」