mysql limit collapse, which result in data interaction

Question

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      |         |               |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Get the first page 10 rows;

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 |
+-----------+----------+

Get the second page 7 rows

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 |
+-----------+----------+

Answer 1

Use 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;

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.