分页查询一般都会出现俩次查询,此时会有如下情况:
要得到满足条件的那一页记录数。
要得到满足条件的总记录数。
如果在第一个查询和第二个查询之间新增或者删除了一些数据,那么查询的结果就不准备了。我想大家都能想象这个场景,在此就不举例。
有什么解决方法,不会出现上述的问题,经过一番搜索,发现MySQL可以使用
List-1
ELECT FOUND_ROWS();
我们来验证下:
1.不带Limit的查询情况
List-2得到7条记录
mysql> select * from student where id>5;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 8 | ?? | 20 |
| 9 | ?? | 20 |
| 10 | ?? | 20 |
| 11 | ?? | 20 |
| 12 | ?? | 20 |
| 13 | Jack | 16 |
| 14 | Alice | 16 |
+----+-------+------+
7 rows in set (0.00 sec)
List-3插入一条记录,此时满足List-2条件的记录是8了
mysql> insert into student(name,age) values('Wenlian',25);
Query OK, 1 row affected (0.01 sec)
List-4得到的是7,而不是8
mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 7 |
+--------------+
1 row in set (0.01 sec)
我们explain来查看下"select found_rows();"
List-5type值是null,表示不需要去查询table
mysql> explain select found_rows();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
来验证下删除的情况
List-6满足条件的有5条
mysql> select * from student where age=16;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | ?? | 16 |
| 2 | Steven | 16 |
| 3 | Han | 16 |
| 4 | Meituan | 16 |
| 13 | Jack | 16 |
+----+---------+------+
5 rows in set (0.00 sec)
List-7删除一条满足List-6条件的记录
mysql> delete from student where id=1;
Query OK, 1 row affected (0.00 sec)
List-8即使List-7中删除了一条满足条件的记录,结果还是5
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 5 |
+--------------+
1 row in set (0.01 sec)
2.带Limit的查询
这里就不再给出具体例子,看MySQL官网文档,上面有具体描述。
Reference: