MariaDB使用OFFSET和LIMIT分页丢失行的问题求助

问题根源 #

你当前使用的OFFSET分页是基于结果集行数进行偏移的，当表中有行被删除时，结果集的总行数与主键id的序列不再一一对应，导致偏移计算出错，出现跳行现象。比如id=1被删除后，整个结果集的行数比最大id值小1，此时按OFFSET偏移会跳过本应包含的行。

解决方案：改用键集分页（Keyset Pagination） #

放弃OFFSET，基于主键id的实际值进行分页，每次以上一页最后一条记录的id作为查询条件，而非依赖结果集行数偏移。这种方式不受行删除影响，能保证分页数据不丢失。

代码修改示例 #

• 首次查询（第一页）：

// 直接取前30行，无需OFFSET
$get_rows = $conn->prepare("SELECT * FROM my_table ORDER BY id ASC LIMIT 30");
$get_rows->execute();
// 处理结果...

• 后续AJAX查询（非第一页）：
  前端需要将上一页最后一条记录的id传递给后端（比如通过POST/GET参数），后端以此作为查询条件：

// 接收前端传来的上一页最后id
$last_id = $_REQUEST['last_id'];
// 查询last_id之后的30行数据
$get_rows = $conn->prepare("SELECT * FROM my_table WHERE id > ? ORDER BY id ASC LIMIT 30");
$get_rows->bind_param('i', $last_id);
$get_rows->execute();
// 处理结果...

注意事项 #

• 必须保证ORDER BY的字段是唯一且有序的，你的id是自增主键，完全满足要求。
• 前端渲染数据时，需记录当前页最后一条记录的id，用于下一次AJAX请求。
• 若需支持上翻页，可传递当前页第一条记录的id，使用WHERE id < ? ORDER BY id DESC LIMIT 30查询，再将结果反转顺序展示。

内容的提问来源于stack exchange，提问作者Cecel