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)