mysql中的found_rows() 与 row_count()函数

1. found_rows()

found_rows()用于查询同一连接下，上一条执行select查询返回的行数，包括show 语句返回的行数。中间可以插入执行dml语句，返回依然是上一条select语句返回的行数。

mysql> select * from t6;
+----+------+------+
| id | k1   | k2   |
+----+------+------+
|  1 |    1 |    1 |
|  3 |    3 |    3 |
+----+------+------+
2 rows in set (0.05 sec)

mysql> insert into t6 values(4,4,4);
Query OK, 1 row affected (0.02 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            2 |
+--------------+
1 row in set, 1 warning (0.00 sec)

 

使用sql_calc_found_rows 与 found_rows()组合，可以查询到去除limit限制后返回的总行数。

mysql> select sql_calc_found_rows * from test_t1 limit 3;
+------+------+
| ID   | C1   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|  100 |  100 |
+------+------+
3 rows in set, 1 warning (0.00 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            8 |
+--------------+
1 row in set, 1 warning (0.00 sec)

上面的语句相当于执行:

mysql> select * from test_t1 limit 3;
+------+------+
| ID   | C1   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|  100 |  100 |
+------+------+
3 rows in set (0.01 sec)

mysql> select count(*) from test_t1;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

因此使用sql_calc_found_rows 与 found_rows()组合相当于节省了一次查询count(*). 

不使用sql_calc_found_rows ，只使用found_rows()返回的是上一条select语句返沪的实际行数。

 

2. row_count()

row_count()查询同一连接上一条dml语句返回的行数，中间不能穿插其他select语句。如果有其他语句，结果返回-1。

 

mysql> insert into t6 values(4,4,4);
Query OK, 1 row affected (0.02 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            2 |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.01 sec)

mysql> update t6 set k1=2 ;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)