1. 为什么给查询结果加序号先搞清楚真实需求先说个直觉。很多时候我们写 MySQL 查询最后拿到结果集第一反应就是“能不能带个序号”这样前端表格第一列就能显示 1、2、3用户看着舒服导出的 Excel 也不会一上来就是一堆裸数据。但做久了就会发现加序号这个动作远没有想象中简单。你在 MySQL 5.7 里写得好好的 SQL换到 8.0 可能还能跑但如果你用了不支持的自定义变量写法或者在变量赋值顺序上踩了坑结果集可能完全乱掉。我整理了几个真实需求场景方便你判断自己属于哪一种分页列表后台管理系统的订单列表、用户列表每页 20 条第一页显示 120第二页要显示 2140而不是重新从 1 开始。排名统计按销售额、分数、时间对记录进行排序要求相同值的记录序号相同或者每一条记录都必须有一个唯一序号。报表导出导出的 Excel 表格第一列通常是行号方便客户核对数据量这个行号不需要参与业务逻辑纯粹是给人看的。分组编号比如按部门给员工编号每个部门内部从 1 开始跨部门又重置。不同场景对序号的要求完全不同。最怕的就是需求还没聊清楚上来就写SELECT id, ...然后把主键当作序号用结果翻到第 2 页发现序号不连续或者排序字段一变主键顺序跟展示顺序对不上用户马上来投诉。1.1 最常见的三个场景分页、排名、报表分页场景下序号本质上等于“当前页第一条记录的全局偏移量 当前页内序号”。如果你只在 SQL 里生成当前页的 120那前端需要额外做偏移计算如果你希望在 SQL 里直接生成全局序号那就要考虑翻页时如何接着上一页继续增加不能每页都重新计数。排名场景则更麻烦。比如查询成绩排名两个人的分数相同到底算并列第一还是按先后顺序各占一个名次这决定了你该用row_number()、rank()还是dense_rank()。很多新手在 MySQL 5.7 时代用变量法去模拟排名写出来的 SQL 看起来能出结果但遇到分数相同的记录就露馅了要么序号重复要么序号跳号怎么调都不对。报表场景一般是“数据导出时带序号”对序号的连续性要求最高而且要贴合人的阅读习惯。用户拿到 Excel 后可能会自己筛选、排序这时候序号是静态的比较好所以通常直接在导出逻辑里生成。你不能在导出的时候突然发现因为数据源里删过几条记录ID 断号了导致导出的行号也是断的。理解了场景才知道该选哪种方案。下面我按技术演进顺序把这几年在项目里用过的几种加序号方式都拆开讲。1.2 直接用自增主键当序号这里有个大坑先说说最容易踩的坑。有人图省事直接在查询里写SELECT id, name, ...然后把前端的序号列绑定到id字段上。表面看没什么问题因为主键一般来说都是连续的整数而且每一行都不一样。但只要你经历过一次真实的业务就明白它根本不能满足展示需求。第一个问题自增主键不连续。只要发生过DELETE主键就会出现空洞1、2、3、5、7。前端列表翻页时第一页可能显示 1、2、3、5第二页突然从 8 开始用户看到序号跳了立刻觉得数据有问题。第二个问题排序字段不是主键。比如你要按create_time DESC排序展示最新文章主键是 100、101、102但按时间倒序后顺序变成 102、101、100这时候直接把主键当序号恰好反过来了序号 1 对应的是主键 102看起来序号和主键混在一起完全没意义。第三个问题加了 WHERE 条件过滤后主键更加谈不上连续。比如只查状态为“已支付”的订单主键 1、2、3 里可能只有 2 和 3 符合条件序号一列显示 2、3怎么看怎么别扭。所以一句话主键是给数据库用的序号是给人用的两者不该混为一谈。接下来进入正题。2. 给查询结果加序号的几种主流方法我通常把加序号的方法分成三类应用层计数、MySQL 用户变量、窗口函数。这三类不是互相替代的关系而是适配不同版本、不同场景。老项目跑在 MySQL 5.7 上你没法用row_number()就得靠变量法新项目直接上 8.0窗口函数写起来又爽又不容易出错还有一些中间层框架的查询结果是流式返回的应用层计数反而是最方便的方案。2.1 应用层循环计数器简单直接适合所有版本如果你是用 Java、PHP、Python 这类语言写后端最稳妥的加序号方式其实是在应用层做计数器而不是在 SQL 里跟 MySQL 较劲。举个例子查询出来一批用户列表你可以在遍历结果集时这样加序号ListUserDTO list new ArrayList(); int rowNum 1; for (User user : userList) { UserDTO dto new UserDTO(); dto.setRowNum(rowNum); dto.setName(user.getName()); list.add(dto); }这样写的好处非常明显不依赖 MySQL 版本5.6、5.7、8.0 都能跑。序号逻辑肉眼可见出问题了一行行 debug 就行。分页偏移量计算方便只要在循环前把rowNum初始化成(page - 1) * pageSize 1就能保证第二页从 21 开始第三页从 41 开始。性能上几乎没有额外开销加个整数而已。缺点只有一个如果查询结果是直接在 SQL 里做了复杂排序和分组你需要把“重置序号”的规则也放到应用层代码里比如按部门分组编号时要判断部门是否变化了变化了就重置计数器。稍微麻烦一点但完全可控。所以我的建议是只要你的业务允许在应用层处理就别折腾 SQL。毕竟代码是给人维护的越直白越好。2.2 MySQL 用户变量法传统方案搞懂这招就能看懂老代码在 MySQL 8.0 之前想要在 SQL 里生成序号最常见的做法就是用户变量。它的核心思路很简单SET rn : 0; SELECT rn : rn 1 AS row_num, name, age FROM users ORDER BY age DESC;第一行把变量rn初始化为 0第二行每取出一行就把rn加 1然后作为row_num输出。原理是 MySQL 在执行 SELECT 时对每一行记录计算 SELECT 列表中的表达式而rn : rn 1这个表达式的执行顺序恰好是“先把旧的rn拿出来加 1再存回去”于是每一行都能拿到递增的序号。但这里有个很多人掉进去的坑MySQL 不保证 SELECT 列表中的表达式按你写的顺序从左到右执行。如果你在同一个 SELECT 列表里同时给多个变量赋值并且后面的变量依赖前面的变量那么结果极可能不按预期。我后面会在“常见问题”里专门讲。使用变量法还有一个常见的优化写法把初始化变量放进 CROSS JOIN 子查询里这样一条 SQL 就能完成任务不需要先执行 SETSELECT rn : rn 1 AS row_num, u.* FROM users u CROSS JOIN (SELECT rn : 0) r ORDER BY u.age DESC;这种写法在 5.7 下很常见但需要注意执行顺序如果ORDER BY在表扫描之后才排序序号可能是在排序之前生成的这会导致序号对应的顺序和最终排序结果不一致。要解决这个问题一般先把排序结果放到子查询里再在外层加序号下一章我会专门展开。2.3 MySQL 8.0 窗口函数法写起来最优雅性能也最好MySQL 8.0 引入了窗口函数加序号的 SQL 一下子变得干净利落SELECT ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num, name, age FROM users;ROW_NUMBER()就是行号函数OVER (ORDER BY age DESC)表示以age降序的顺序来编号。不再需要用户变量不需要 CROSS JOIN 初始化也没有执行顺序的歧义。窗口函数有几个明显的优势可读性极高一看就知道这段 SQL 是要做排序编号。支持分组编号PARTITION BY关键字按部门分组内部编号比变量法简单太多。支持多种排名语义row_number()、rank()、dense_rank()各有用途。执行计划由优化器统一生成不容易出现变量法那种因为评估顺序导致的诡异结果。所以如果你的项目已经跑在 MySQL 8.0 或以上我的建议非常明确新代码一律用窗口函数别再用老古董变量法了。除非你有特殊理由必须兼容 5.7。3. 实操细节变量法的参数计算与执行顺序虽然窗口函数很香但存量系统里跑着大量 5.7变量法不会很快消失。作为开发你还是得掌握变量法的正确姿势尤其要理解它的执行顺序否则别人留下的老代码你根本不敢动。3.1 内联初始化变量的正确姿势先看一个经典错误写法SELECT rn : rn 1 AS row_num, u.* FROM users u ORDER BY u.age DESC;这里rn从未初始化结果是整列 NULL。因为用户变量没有默认值第一次参与计算时rn是 NULLNULL 1还是 NULL。所以一定要先初始化。正确的做法有两种。第一种分开写SET rn : 0; SELECT rn : rn 1 AS row_num, u.* FROM users u ORDER BY u.age DESC;第二种用 CROSS JOIN 内联初始化SELECT rn : rn 1 AS row_num, u.* FROM users u CROSS JOIN (SELECT rn : 0) r ORDER BY u.age DESC;我推荐第二种因为很多连接池或 ORM 框架里执行多语句不太方便一条 SQL 解决最省事。但要注意内联初始化时CROSS JOIN 子查询(SELECT rn : 0)理论上先执行提供给外层使用。实际执行计划也基本保证这一行为但在极端复杂的 SQL 中为了保险起见建议把需要“先排序再编号”的逻辑做成子查询。来看一个需要按成绩排序后编号的例子SELECT rn : rn 1 AS row_num, s.* FROM ( SELECT name, score FROM students ORDER BY score DESC ) s CROSS JOIN (SELECT rn : 0) r;外层 CROSS JOIN 初始化变量内层子查询负责先排序这样能确保序号是按照score DESC顺序生成的。如果不套子查询直接在原表上排序加变量很可能序号是按原表物理顺序生成后再排的序结果序号顺序跟成绩顺序对不上。3.2 分页场景下的序号连续性分页列表加序号最尴尬的莫过于第一页显示 120第二页又显示 120。要解决这个问题有两个思路。思路一在 SQL 里生成全局序号。假如每页 20 条第二页的 SQL 写成SELECT rn : rn 1 AS row_num, u.* FROM ( SELECT u.* FROM users u ORDER BY u.create_time DESC LIMIT 20, 20 ) u CROSS JOIN (SELECT rn : 0) r;这里有个陷阱如果先 LIMIT 再编号每页拿到的数据是 20 条rn每次从 0 开始第二页还是 120。要生成全局序号必须在 LIMIT 之前把所有数据按顺序编号再截取当前页SELECT t.* FROM ( SELECT rn : rn 1 AS global_row_num, u.* FROM users u CROSS JOIN (SELECT rn : 0) r ORDER BY u.create_time DESC ) t LIMIT 20, 20;这种写法是 OK 的因为内层已经完成了全局编号外层 LIMIT 截取的是带编号的结果集第二页拿到的就是 2140。但代价是内层要给所有符合条件的记录都编上号数据量大的时候性能会受影响。思路二应用层做偏移。SQL 每页只负责取 20 条不关心序号应用层在遍历时用(page - 1) * 20 i计算出真实序号。这也是我最推荐的做法因为计算简单、性能损耗最小而且逻辑非常清晰。int startRow (page - 1) * pageSize; for (int i 0; i list.size(); i) { list.get(i).setRowNum(startRow i 1); }简单说别总想着一条 SQL 解决所有问题分页场景下应用层加偏移往往更省心。3.3 group by 和 order by 场景下怎么重置序号有些业务要求在分组内部重新编号例如按部门统计员工排名。部门 A 有 3 个员工序号从 1 到 3部门 B 有 5 个又是从 1 到 5。变量法可以这样写SELECT IF(dept dept, rn : rn 1, rn : 1) AS row_num, dept : dept AS dept, name, salary FROM employee e CROSS JOIN (SELECT rn : 0, dept : ) r ORDER BY dept, salary DESC;这个 SQL 的思路是每取一行先判断当前行的dept和上次记录的dept是否相同。相同说明还在同一个分组里rn继续加 1不同说明换了部门rn重置为 1。然后下一列再用dept : dept把当前部门保存到变量里供下一行比较。这里最容易犯的错误是把dept : dept写在了IF前面。一旦先更新了dept那么IF判断时比较的dept永远等于当前行的 dept导致判断一直为“相同”序号永远不重置全部累加。另外还要注意dept的初始值要选一个不可能跟业务数据重复的值。用空字符串可以用N/A也行关键是不能跟第一条数据的 dept 相同否则第一条记录也会被误判为“相同分组”导致第一个序号变成 2 而不是 1。4. 窗口函数的高级玩法如果你已经用上了 MySQL 8.0那么恭喜你的幸福感会比 5.7 时代高很多。窗口函数不仅语法漂亮更重要的是语义明确不会出现变量法那种“结果靠运气”的尴尬。4.1 row_number、rank、dense_rank 的区别很多人在这一步容易混。我给你做一个直白的对比。假设一个班级成绩如下姓名成绩张三90李四90王五80赵六70三种函数的结果分别是ROW_NUMBER()1、2、3、4。不管成绩相同与否编号始终唯一且连续。RANK()1、1、3、4。成绩相同则并列为 1但下一个名次跳过一个位置出现 3。DENSE_RANK()1、1、2、3。成绩相同则并列为 1下一个名次紧跟连续编号。用表格总结一下函数相同值表现后续名次典型用途ROW_NUMBER()强制区分连续给每一行一个唯一的行号RANK()并列相同跳号传统排名如体育比赛DENSE_RANK()并列相同连续密集排名如积分等级实际业务中如果你只是想要一个“行号列”那么请优先使用ROW_NUMBER()。如果你要做榜单排名并且允许并列再考虑RANK()或DENSE_RANK()。4.2 分组内编号 partition by窗口函数最大的亮点就是PARTITION BY一条 SQL 直接搞定分组内编号完全不用像变量法那样小心翼翼地维护变量。比如按部门给员工按工资降序编号SELECT dept, name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num FROM employee ORDER BY dept, salary DESC;执行结果里每个部门内部都会从 1 开始编号。PARTITION BY dept的意思就是“把数据按 dept 分成若干小组在每个小组内单独编号”ORDER BY salary DESC则决定小组内部的排序方向。你甚至可以在同一个查询里对同一个分组计算多个窗口函数比如同时输出部门内部行号和部门内部排名SELECT dept, name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_num FROM employee;这种写法在变量法时代几乎不敢想因为你要维护两组变量而且执行顺序稍一错乱结果全乱。窗口函数把复杂的逻辑封装在OVER子句里代码可读性和正确性都高了一个档次。4.3 什么时候必须用窗口函数而变量法很麻烦窗口函数和变量法能实现的功能大部分重叠但有些场景变量法写起来极其痛苦而窗口函数几乎是降维打击。第一个典型场景取每组前 N 条记录。比如“每个部门工资最高的前 2 名”。窗口函数可以这样写SELECT dept, name, salary FROM ( SELECT dept, name, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employee ) t WHERE t.rn 2;如果换成变量法你得先给全表编号再在外面套一层 WHERE 过滤而且编号逻辑一旦涉及多个分组和排序字段SQL 会变得很长、很难维护。窗口函数 子查询的组合逻辑一目了然。第二个典型场景同一个查询里要输出多个不同语义的序号。比如既要部门内编号又要全公司编号。窗口函数可以轻松做到SELECT dept, name, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_row_num, ROW_NUMBER() OVER (ORDER BY salary DESC) AS global_row_num FROM employee;变量法要同时维护两个变量、两个分组判断写出来很容易出错。第三个典型场景需要基于窗口函数结果做二次统计。比如找到每个部门工资第二高的员工。窗口函数的子查询嵌套非常自然而变量法几乎无法优雅处理这种需求。所以我的结论很简单MySQL 8.0 以后新写的查询优先考虑窗口函数只有遇到特别复杂、窗口函数也难写的聚合逻辑时才回头考虑变量法或临时表。5. 常见问题与排查技巧实录做开发最怕的不是不会写而是写完了结果不对还找不到原因。这一章我把这些年加序号过程中遇到的高频问题整理成了一份速查表并且附上我自己的排查思路。5.1 序号全部是 NULL 或一列全为 1先检查变量初始化假如你写了这样的查询SELECT rn : rn 1 AS row_num, u.* FROM users u;结果row_num全是 NULL几乎是必然的。原因就是我前面提到的rn从未初始化NULL 1 还是 NULL。但还有一种更隐蔽的情况结果里row_num全部是 1。你把 SELECT 列表写成了SELECT rn : rn 1 AS row_num, rn : rn 1 AS row_num2, u.* FROM users u CROSS JOIN (SELECT rn : 0) r;MySQL 对 SELECT 列表的表达式的执行顺序不做保证可能两列用的都是同一个rn的旧值也可能第一列先更新第二列再更新最终结果跟你的预期完全不一致。排查技巧把 SQL 拆开先单独验证变量赋值是否生效。比如先只查SELECT rn : rn 1, ...确保每行都在递增再逐步加其他字段。不要一次写一大堆还带乱七八糟的业务逻辑否则出了错你根本不知道是哪一步的问题。5.2 分页后序号不连续这几乎是后台管理系统最常被吐槽的 bug。第一页序号 120第二页变成了 120用户直接开骂说数据重复了。原因基本就是 SQL 写成了SELECT rn : rn 1 AS row_num, u.* FROM users u CROSS JOIN (SELECT rn : 0) r ORDER BY u.create_time DESC LIMIT 20, 20;这里rn每页都会初始化成 0LIMIT 截取的是当前页 20 条编号自然从 1 开始。解决办法我前面已经给过要么在子查询里先编号再 LIMIT要么在应用层加上偏移量(page - 1) * pageSize。从维护角度说我更推荐应用层加偏移因为 SQL 不用承担“全局编号”的额外开销改造成本也低。5.3 大数据量排序慢怎么优化使用变量法时MySQL 往往需要生成临时表来存储变量赋值的结果再加上ORDER BY很容易触发filesort。如果查询的数据有几百万行性能会肉眼可见地变差。排查时先用 EXPLAIN 看一下执行计划EXPLAIN SELECT rn : rn 1 AS row_num, u.* FROM users u CROSS JOIN (SELECT rn : 0) r ORDER BY u.create_time DESC;如果看到Using temporary; Using filesort就说明排序和变量赋值过程中产生了临时表。数据量越大性能越差。优化方向有两个。第一给排序字段加上合适的索引让排序走索引而不是 filesort。第二实在没办法时考虑在应用层做编号SQL 只负责排序和分页毕竟加序号本身就是一个“展示层操作”不一定非要在数据库里完成。5.4 快查表常见问题、原因与解决方案常见问题可能原因解决方案序号全是 NULL用户变量未初始化使用 SET 初始化或 CROSS JOIN 子查询序号全部相同SELECT 列表变量赋值顺序不确定将变量赋值拆到单独列表或改用窗口函数分页后序号不连续每页重新初始化变量应用层加偏移量或先编号再 LIMIT分组内序号不重置dept 赋值顺序写错先比较后赋值初始化值避免与业务值重复排名字段相同但序号不并列使用 ROW_NUMBER() 导致强制区分改用 RANK() 或 DENSE_RANK()变量法查询性能差临时表 filesort优化索引或改用窗口函数序号与排序结果不一致变量在排序前赋值先排序子查询再编号这张表我看着都觉得眼熟因为每一条都是我或者身边同事真实踩过的。碰到问题先对号入座能省不少排查时间。6. 我平时在项目里的使用习惯最后说点我自己的经验。新项目我几乎不用变量法因为 MySQL 8.0 的窗口函数太好用了性能稳定、语义清晰、代码好维护。遇到分页列表我倾向于在应用层加序号让 SQL 保持“只负责拉数据”的单一职责。但存量系统跑在 5.7 上又确实需要 SQL 内生序号的我会固定一个模板SELECT rn : rn 1 AS row_num, t.* FROM ( SELECT 你的字段 FROM 你的表 WHERE 过滤条件 ORDER BY 排序字段 ) t CROSS JOIN (SELECT rn : 0) r;这个模板的好处是先通过子查询锁定了排序结果再把编号逻辑放到外层基本不会出现“序号和排序结果错位”的问题。我会把它封装成笔记用到的时候直接套。另外提醒一句给结果集加序号时最好给row_num取一个不容易跟业务字段冲突的别名比如seq或row_no。原因很简单有时候业务表里已经有一个叫做row_num的字段两个字段重名应用层取值时会随机取到一个查半天查不出 bug特别浪费时间。加序号看似小事但写错了轻则前端显示难看重则数据错乱被用户投诉。希望这篇内容能帮你少踩几个坑也欢迎你把遇到的问题丢在评论里我看到了会再补充进来。