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)
复制代码

 

posted @   __Yoon  阅读(403)  评论(0编辑  收藏  举报
编辑推荐:
· 从 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 热点速览」
点击右上角即可分享
微信分享提示