mysql限制崩溃，导致数据交互 [英] mysql limit collapse, which result in data interaction

问题描述

我使用 limit offset，num 来逐页获取数据.但是页面之间的数据是交互的，这可以从主键course_id看到.

I use limit offset, num to fetch data by page. But the data between pages interact, which can be seen from the primay key course_id.

select version();
+---------------------+
| version()           |
+---------------------+
| 10.3.13-MariaDB-log |
+---------------------+

show index from sc_base_course;
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sc_base_course |          0 | PRIMARY   |            1 | course_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| sc_base_course |          1 | agency_id |            1 | agency_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| sc_base_course |          1 | agency_id |            2 | course_name | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

获取首页10行；

select course_id,grade_id from sc_base_course where  agency_id = 10000  limit 0,10;
+-----------+----------+
| course_id | grade_id |
+-----------+----------+
|        13 |        1 |
|         6 |        3 |
|        12 |        4 |
|         8 |        2 |
|         7 |        2 |
|         9 |        4 |
|        16 |        1 |
|         1 |        2 |
|        17 |        1 |
|        14 |        5 |
+-----------+----------+

获取第二页7行

select course_id,grade_id from sc_base_course where  agency_id = 10000  limit 10,10;
+-----------+----------+
| course_id | grade_id |
+-----------+----------+
|        11 |        4 |
|        12 |        4 |
|        13 |        1 |
|        14 |        5 |
|        15 |        1 |
|        16 |        1 |
|        17 |        1 |
+-----------+----------+

推荐答案

使用排序依据！

SQL表表示无序的行集.如果没有 order by 子句，则数据库可以自由返回其喜欢的顺序的行，并且在连续执行同一查询时结果可能不一致(因此，分页不稳定).

SQL tables represent unordered sets of rows. Without an order by clause, the database is free to return the rows in whichever order it likes, and the results may not be consistent over consecutive executions of the same query (hence, pagination is not stable).

select course_id, grade_id 
from sc_base_course 
where  agency_id = 10000  
order by course_id, grade_id
limit 10,10;

请注意，不仅您需要 order by 子句，而且此子句还必须具有确定性.也就是说，子句中的列(或列集)必须唯一地标识每条记录-否则，也将以未确定的顺序提取关系.

Note that not only you need an order by clause, but also this clause must be deterministic. That is, the column (or set of columns) in the clause must uniquely identify each record - otherwise, it is, again, undefined in which order ties will be fetched.

这篇关于mysql限制崩溃，导致数据交互的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！