MySQL指定ID前后记录查询及GraphQL前后分页实现咨询

我之前在做GraphQL API的游标分页时也碰到过完全一样的需求——比起传统页码分页，这种基于游标（指定ID）的分页在数据变动时确实能避免跳页或重复内容的问题，特别适合需要精准分页的场景。下面就详细说怎么实现，不管你的查询带多少JOIN、WHERE条件都能用。

先搞懂核心逻辑 #

这种分页的关键是依托你查询的ORDER BY规则——你得先保证排序的唯一性（最好把主键ID加到ORDER BY的最后，避免两条记录排序字段完全相同导致定位混乱），然后通过目标记录的排序字段值，来筛选出比它“更靠后”或“更靠前”的记录，而不是依赖页码。

一、获取指定ID「之后」的记录（after分页） #

举个实际例子，假设你有这么一个带JOIN和WHERE的基础查询：

SELECT u.id, u.name, p.post_title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.status = 'active'
ORDER BY u.created_at DESC, u.id DESC  -- 这里用created_at降序+ID降序保证排序唯一
LIMIT 10;

现在要拿ID=100的记录之后的10条数据，步骤分两步：

1. 先拿到目标记录的排序字段值 #

先查一下ID=100的那条记录对应的created_at和id（也就是你ORDER BY里的字段）：

SELECT created_at, id FROM users WHERE id = 100;

如果你的排序字段来自关联表（比如ORDER BY的是p.post_date），那就要从posts表查对应记录的post_date和主键。

2. 把筛选条件加到原查询里 #

因为我们的ORDER BY是created_at DESC, id DESC，“之后”的记录意味着要么created_at比目标记录小，要么created_at相同但id更小。把这个逻辑加到WHERE里：

SELECT u.id, u.name, p.post_title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.status = 'active'
-- 核心的游标筛选条件
AND (u.created_at < '2024-01-01 12:00:00' OR (u.created_at = '2024-01-01 12:00:00' AND u.id < 100))
ORDER BY u.created_at DESC, u.id DESC
LIMIT 10;

这里的'2024-01-01 12:00:00'就是第一步查到的目标记录的created_at值。

如果你的ORDER BY是升序（比如created_at ASC, id ASC），那“之后”的条件就要反过来：

AND (u.created_at > '2024-01-01 12:00:00' OR (u.created_at = '2024-01-01 12:00:00' AND u.id > 100))

二、获取指定ID「之前」的记录（before分页） #

逻辑和after刚好相反，还是用刚才的降序排序例子：
“之前”的记录是排序更靠前的，也就是created_at比目标记录大，或者created_at相同但id更大，所以条件改成：

SELECT u.id, u.name, p.post_title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.status = 'active'
AND (u.created_at > '2024-01-01 12:00:00' OR (u.created_at = '2024-01-01 12:00:00' AND u.id > 100))
ORDER BY u.created_at DESC, u.id DESC
LIMIT 10;

如果是升序排序，“之前”的条件就是：

AND (u.created_at < '2024-01-01 12:00:00' OR (u.created_at = '2024-01-01 12:00:00' AND u.id < 100))

三、复杂查询的简化技巧 #

如果你的查询特别复杂（多表JOIN、一堆WHERE条件），不想重复写两次查询，用CTE（公共表表达式）可以简化：

WITH target_record AS (
    SELECT created_at, id FROM users WHERE id = 100
)
SELECT u.id, u.name, p.post_title
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN target_record t ON 1=1  -- 关联目标记录的信息
WHERE u.status = 'active'
AND (u.created_at < t.created_at OR (u.created_at = t.created_at AND u.id < t.id))
ORDER BY u.created_at DESC, u.id DESC
LIMIT 10;

这样不用手动复制排序值，代码更干净，也不容易出错。

四、几个要注意的坑 #

• 排序必须唯一：一定要把主键ID加到ORDER BY的最后，不然如果两条记录的排序字段完全相同，游标定位就会出错，比如两条记录created_at一样，没加ID的话就没法区分谁在前谁在后。
• 加索引优化：为了让游标查询快起来，给ORDER BY的字段组合加个联合索引，比如刚才的例子，给users表加(status, created_at, id)的联合索引，这样WHERE和ORDER BY都能用到索引，查询速度会提升很多。
• GraphQL层的适配：在API里，你可以把after参数设为目标记录的ID，服务层先查该ID的排序字段值，再构造SQL返回结果；还可以返回hasNextPage字段——只要判断查询结果的数量等于LIMIT，就说明还有下一页，反之则没有。

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