如何用SQL ROW_NUMBER实现稳定分页？

ROW_NUMBER()分页出错是因为ORDER BY字段重复导致行号分配不稳定，必须在排序末尾添加唯一列（如id）兜底；WHERE条件需用子查询或CTE套一层且rn别名不可省略；避免使用运行时函数或精度不足的时间字段。

ROW_NUMBER() 分页为什么总出错？

需知，ROW_NUMBER() 本身并不能保证稳定，它只是严格按照 ORDER BY 的结果进行编号。倘若排序字段存在重复值（例如多个 created_at 相同的记录），数据库每次的物理顺序可能会有所不同，这就导致同一行在不同的查询中会获得不同的序号。这并非是bug，而是SQL标准所允许的未定义行为。

常见错误现象包括：第 2 页出现第 1 页已返回过的 id；某条记录刷新后“消失”；用相同参数反复请求，返回结果不一致。

• 只写 ORDER BY created_at DESC —— 即使时间戳精度到微秒，高并发插入仍可能冲突
• 把 WHERE 条件写在外层子查询里，导致全表编号后再过滤，性能崩塌
• 用 rn > @start AND rn 而不是 rn BETWEEN @start AND @end，部分数据库优化器无法下推谓词

怎么写才真正稳定？必须含唯一列兜底

核心原则：让每行在排序序列中有唯一位置。不能靠“看起来差不多”，必须显式指定决胜字段。

正确写法是类似：ORDER BY created_at DESC, id DESC —— 第二字段必须是主键或非空唯一列，方向要与业务逻辑对齐（例如“最新时间 + 同时间取最大 id”）。

• 禁止用表达式包裹排序字段，如 ORDER BY DATE(created_at), id，索引失效且语义不稳定
• 如果常带 WHERE status = 'active'，联合索引应为 (status, created_at DESC, id DESC)，否则可能弃用索引
• EXPLAIN 中 key 显示 NULL 或不是你建的索引名？先检查类型是否匹配（TIMESTAMP 字段却建在 DATETIME 索引上会隐式转换）

WHERE 条件该放哪？别让全表扫描毁掉性能

ROW_NUMBER() 是投影阶段产物，WHERE 无法直接引用 rn 别名。但更重要的是：过滤条件必须尽可能早生效。

正确结构是三层嵌套：最内层查原始数据 + WHERE 过滤 + ORDER BY；中间层加 ROW_NUMBER()；最外层只做序号范围筛选。

• 错误：SELECT * FROM (SELECT *, ROW_NUMBER() OVER (...) AS rn FROM t) t2 WHERE t2.status = 'active' AND rn BETWEEN 101 AND 120 —— 先编号再过滤，全表扫描
• 正确：SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC) AS rn FROM t WHERE status = 'active') t2 WHERE t2.rn BETWEEN 101 AND 120
• 参数计算要防溢出：@offset BIGINT = CAST(@page_index AS BIGINT) - 1，避免 (@page_index - 1) * @page_size 超过 INT 上限

什么时候该放弃 ROW_NUMBER()？游标分页才是真解

如果你的 API 需要支持高频翻页、数据持续写入、或用户能随时刷新页面，ROW_NUMBER() 就是权宜之计。它解决不了“第 N 页”这个抽象概念在动态数据下的本质矛盾。

真实生产环境更推荐游标分页：WHERE (created_at, id) 配合 ORDER BY created_at DESC, id DESC。

• ROW_NUMBER() 只适合后台导出、审计日志等“绝对页码”不可绕开的场景
• 对外暴露游标（如 cursor=2024-05-01T12:00:00Z_12345），比传 page=37&size=20 更可靠、更高效
• 不要试图给游标加 ROW_NUMBER() 做“双重保险”——游标本身已足够稳定，多一层反而引入新风险

稳定性不在函数本身，而在排序键是否真正唯一。哪怕多加一列 id，也比依赖数据库“大概率不会变”的默认顺序强得多。