MySQL：在同一查询中动态获取结果索引位置用于OFFSET参数

要在单条SQL中完成“获取指定行的位置并以此作为偏移量取后续数据”的需求，核心是先给符合条件的行按排序规则编号，再基于目标行的编号筛选数据，以下分两种场景给出方案：

方案一：支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数给符合条件的行按_date ASC排序并分配行号，再通过子查询定位目标行的行号，最后筛选行号大于等于目标行号的记录并取前10条：

WITH ranked_messages AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY _date ASC) AS row_num
    FROM tbl_messages
    WHERE _user = '2' AND _instance = '5'
)
SELECT *
FROM ranked_messages
WHERE row_num >= (SELECT row_num FROM ranked_messages WHERE _post_id = 'R5G96H5D85S9')
LIMIT 10;

如果需要跳过目标行，从下一行开始取数据，只需将>=改为>即可。

方案二：不支持窗口函数的旧版本数据库（如MySQL 5.x） #

通过用户变量手动计算行号，逻辑与方案一一致，只是用变量代替窗口函数：

SELECT *
FROM (
    SELECT *,
           @row_num := @row_num + 1 AS row_num
    FROM tbl_messages,
         (SELECT @row_num := 0) AS init_var
    WHERE _user = '2' AND _instance = '5'
    ORDER BY _date ASC
) AS ranked_data
WHERE row_num >= (
    SELECT row_num
    FROM (
        SELECT *,
               @target_row := @target_row + 1 AS row_num
        FROM tbl_messages,
             (SELECT @target_row := 0) AS init_var
        WHERE _user = '2' AND _instance = '5'
        ORDER BY _date ASC
    ) AS target_data
    WHERE _post_id = 'R5G96H5D85S9'
)
LIMIT 10;

补充说明 #

• 如果存在多条_post_id相同的记录，ROW_NUMBER()会给每条记录分配唯一行号；若需将相同_post_id的记录视为同一位置，可替换为RANK()或DENSE_RANK()函数。
• 上述方案均无需额外PHP处理，完全在SQL层面实现需求。

内容的提问来源于stack exchange，提问作者Jean Kássio