数据库动态增删数据，导致分页查询数据出现重复或遗漏的问题分析及解决方案

一、问题分析

1. 请求数据

    一般情况下，为了减少服务器的压力或方便展示，前端通过分页方式来请求数据，调用 API 接口时会带上参数 page 与 pageSize。例如请求某个班级的学生数据，获取第一页的 10 个学生的数据 ，假设按注册时间倒序排列：

https://api.domain.com/class/student/list?page=1&pageSize=10

    分页查询采用 SQL limit 语句来实现，limit 用法：

select * from table_name limit [offset，] rows

offset：指定第一个记录行的偏移量（即从哪一行开始返回），注意：初始行的偏移量为0。
rows：返回具体行数。

    请求第一页数据，page = 1，offset 就等于 (1 - 1) * 10, 也就是 0；

select * from tb_student order by register_time desc limit 0, 10;

    请求第二页数据，page = 2，offset 就等于 (2 - 1)*10, 也就是 10；

select * from tb_student order by register_time desc limit 10, 10;

2. 数据重复或缺失

    假设已经获取到第一页数据，在请求第二数据之前，后台新注册了一个学生，如果按注册时间倒序排列，这个新注册的学生就成了第一条数据，如果按「堆栈」的方式来理解：原先第一页的数据的最后一条，因为新数据的插入，被 PUSH 到第二页的第一条数据的位置上，因此当我们获取第二页数据时，就会「重复」获取到原先第一页数据的最后一条记录，当出现这种情况发生时，前端通常会显示两条紧挨着且完全相同的学生数据。

    同样的道理，假如我们获取第二页数据前，原先第一页的学生因为注销，从数据库里删除了，所有的数据的起始位置偏移量都减 1，第二页的第一条数据变成了第一页的最后一条数据，所以我们请求第二页数据返回的结果就会「缺失」这一条数据。

总结：当数据库数据发生了动态改变，如果我们的 offset 没有实时动态的修正，仍然固定不变的使用 offset  = (page - 1) * pageSize 的方式来获取数据，就会出现数据重复或缺失的情况。

二、解决方案

    经过上面的分析，我们知道每次请求数据，如果能获取到正确的 offset，就不会出现上诉的问题，这个 offset 应该是请求第一页学生数据的最后一条学生记录「在所有学生按注册时间倒序排列时」所处的位置。因此，我们获取到最后一条记录的 ID，通过参数 「fromId = ID」 上传给后台提供的接口，通过计算获取到请求下一页数据正确的 offset，这样不管数据库数据如何增删，都不会出现重复或遗漏的情况。通俗来说，每一次请求数据，都从 formIId 所代表的的记录的下一条记录开始获取 10 条新的记录。

    请求第一页数据，fromId = 0

https://api.domain.com/class/student/list?fromId=0&pageSize=10

    我们约定 fromId = 0，代表请求第一页数据，因此 offset 值为0；假设第一页数据的最后一条数据的 student_id = 1001;

    请求第二页数据，带上参数 fromId = 1001；

https://api.domain.com/class/student/list?fromId=1001&pageSize=10

    后台实时计算，获取到最后一条记录（fromId）的下一条记录的起始位置：

select count(*) from tb_student where register_time >= (select register_time from tb_student where student_id = 1001)；

注意事项：

1. 该语句里面嵌套了一个select 查询，内层嵌套查询语句只能返回唯一一条记录，不能出现多条记录，否则执行 sql 后会抛出错误。

2. 如果按注册时间升序排列，" >= " 应该替换替换为 " <= "。

3. 用于排序的字段 register_time 不能存在相同的值，否则也可能会导致查询的数据缺失（例如第 11 条数据与第 10 条数据的 register_time 值相同，执行上述 SQL 语句获取到的 offset 值会导致第 11 条数据不会出现在第二页请求的数据里）。

    用上面 SQL 查询语句返回的值作为请求第二页学生数据的 offset，查询语句如下：

select * from tb_student order by register_time desc limit [offset，] 10；