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)