MySQL查询结果加序号：窗口函数、用户变量与分页避坑指南

刚开始接触 MySQL 的同学，基本都会遇到一个需求：查出来一张表的数据，希望前面带一个序号，比如 1、2、3…… 用于报表展示、排名显示、或者前端表格的序号列。我经常收到类似“MySQL 如何给查出的数据加上序号”的私信，其实这个操作看起来简单，真正落地的时候门道很多。它涉及的方案至少有三种以上，而且不同方案在不同 MySQL 版本、不同数据量、不同业务场景下的表现差异很大。

这篇文章就围绕“查询结果加序号”这个场景展开，把思路、SQL 写法、底层原理、性能对比、坑点排查一次性讲透。适合刚接触 SQL 的初学者，也适合在旧版本 MySQL 环境下被“序号问题”折磨过的开发同学，哪怕你已经有几年经验，我相信里面关于排序稳定性、派生表优化、分页序号正确性这几个点，也值得重新过一遍。我不喜欢讲那种“一行代码带你搞定”的标题党，因为实际业务中，序号从来不是孤立的，它背后牵着排序规则、分页逻辑、查询性能这三件事。

1. 需求场景与方案选型

1.1 首先要搞清楚“序号”到底想表达什么

很多人写 SQL 的时候，上来就想要序号，但没想清楚这个序号的用途。我给模拟项目 X 做过一个订单报表，当时业务方提的需求是“按金额从高到低排，给每个订单一个名次”。这个需求背后其实是两个不同的东西：一个是纯粹的展示序号，不管数据怎么排序，只要从 1 开始往下数就行；另一个是业务意义上的排名，必须和某个排序字段强关联，比如金额大的排第 1，金额小的排第 100。

这两种需求的 SQL 写法完全不同。

纯粹的展示序号，在 MySQL 8.0 以下的老版本里，最常见的做法是通过用户变量累加实现；而在 MySQL 8.0 及以上版本，直接用窗口函数ROW_NUMBER()是最优解。如果是排名需求，就要区分是同名次并列（RANK()）、还是密集排名（DENSE_RANK()）、还是单纯的行号（ROW_NUMBER()）。这几个函数的结果差异，我在后面第五节会专门对比。

所以说，写 SQL 之前，跟产品经理确认清楚“这个序号是干嘛的”非常关键。我见过不少开发同学用ROW_NUMBER()实现了行号，结果业务方说要的是那种“并列排名”的效果，两个人还吵了半天。实际上只要在窗口函数里换个函数名，RANK 和 ROW_NUMBER 的结果就有细微差别，但业务含义完全不同。

1.2 一句话判断你该用哪种方案

判断标准其实很简单，我列个速查表，你可以直接按这个选：

这个表基本覆盖了日常 90% 以上的需求，照着选就能避免踩坑。

2. MySQL 8.0 的优雅解法：窗口函数

2.1 ROW_NUMBER() 基础语法与执行逻辑

MySQL 8.0 引入窗口函数后，加序号变成了一个非常标准化的操作。核心语法是：

SELECT ROW_NUMBER() OVER (ORDER BY create_time DESC) AS row_num, order_id, user_name, amount FROM orders LIMIT 20;

这条 SQL 的意思是：按create_time倒序排序后，给每一行分配一个从 1 开始的连续行号。这里的关键认知是，ROW_NUMBER()是在排序结果集之上做“编号”动作，它的执行时机晚于WHERE、GROUP BY、HAVING，晚于ORDER BY的具体排序操作。你可以理解成先排好队，然后从头到尾报数。

有一个细节很多人忽略了：OVER子句里的ORDER BY和查询最外层的ORDER BY谁先执行。窗口函数里的ORDER BY决定了编号的依据，而外层ORDER BY决定结果的最终展示顺序。两者可以不一样。举个实际例子：

SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rank_no, student_name, score FROM exam_scores ORDER BY student_id;

这个查询先按分数倒序编号，学号字段排序的学生是第 2 名，但在结果展示时，又是按学号顺序排列。这种写法在排名报表中很常见，只是容易让新手懵，大家要注意区分编号顺序和展示顺序这两个概念。

2.2 RANK 与 DENSE_RANK 的区别

如果业务要的是“并列排名”而不是单纯的行号，窗口函数之间的差异就体现出来了。我能用一张表说明它们对重复分数的处理：

ROW_NUMBER()不管分数是否重复，强制给每一行一个唯一的递增号；RANK()遇到相同分数会给相同名次，但下一个名次会跳跃（1、2、2、4）；DENSE_RANK()也是相同分数相同名次，但下一个名次不跳跃（1、2、2、3）。

我当年给某高校做一个竞赛成绩排行时，就用过这三种函数做对比。业务方要求“并列名次必须有，但是名次不能断”，那就直接用DENSE_RANK()。如果只是要一个展示用的序号，不关心重复，那就ROW_NUMBER()最合适。这三个函数的性能差异可以忽略不计，选择完全由业务语义决定。

2.3 PARTITION BY 分组编号

还有一种高频场景，不是对整张表编号，而是按某个维度分组后组内编号。比如每个用户按时间顺序的第 1 笔订单、第 2 笔订单；每个班级按分数排第 1 名、第 2 名。这类需求用PARTITION BY子句实现最舒服：

SELECT student_name, class_name, score, ROW_NUMBER() OVER (PARTITION BY class_name ORDER BY score DESC) AS rank_in_class FROM student_scores ORDER BY class_name, rank_in_class;

这里PARTITION BY class_name的意思是“按班级把结果切分成多个窗口”，每个窗口内部再按score DESC排序并独立编号。执行效果等价于“先按班级分组，每个分组内从 1 开始编号”。有了这个写法，你就不再需要去搞复杂的自连接或者临时表了。窗口内部的行数是可控的，所以大数据量下性能也比较稳定。我实测过百万行级别的分组编号，内存占用和耗时可接受。

3. 老版本方案：用户变量与派生表的玩法

3.1 用户变量做行号的核心原理

在 MySQL 8.0 之前，没有窗口函数，那大家怎么给查询结果加序号呢？答案是用户变量。思路是借助@rownum := @rownum + 1这种“每读取一行就自增一次”的机制。经典写法是这样：

SET @rownum := 0; SELECT @rownum := @rownum + 1 AS row_num, order_id, amount FROM orders ORDER BY amount DESC LIMIT 20;

这条 SQL 执行时，MySQL 一行一行地返回数据，每返回一行就先取当前@rownum的旧值，加 1，然后重新赋值给@rownum，同时把新值作为row_num返回。这个操作是“逐行执行、即时生效”的，也正是因为这个特点，用户变量方案有很多隐蔽的坑。

第一个坑是SET @rownum := 0这一句不能少。如果你在同一个会话里之前设置过别的值，那么行号就可能从某个非 1 的数字开始。实际开发中最常见的问题是，连接池复用了数据库连接，上次@rownum停留在 1000，这次查询没重新初始化，序号就从 1001 开始了。解决办法是不要单独用SET语句初始化，改成(SELECT @rownum := 0) r这种派生表初始化，把变量初始化跟查询放在同一条 SQL 里：

SELECT @rownum := @rownum + 1 AS row_num, orders.* FROM orders CROSS JOIN (SELECT @rownum := 0) r ORDER BY amount DESC LIMIT 20;

CROSS JOIN一个只有一行数据的派生表，相当于给整个查询提供了一个“初始化变量”的机会，并且这个派生表的执行优先级很高，变量在正式读取数据前就被重置为 0 了。我在某公司维护一个 5.7 的报表库，这个写法用了很多年，稳定可靠。

3.2 为什么说“先排序再编号”是老版本方案的命门

用用户变量给结果加序号，最怕的就是编号顺序和业务想要的顺序不一致。举个翻车例子：

-- 错误示范：先编号后排序 SELECT @rownum := @rownum + 1 AS row_num, order_id, amount FROM orders, (SELECT @rownum := 0) r ORDER BY amount DESC;

如果代码写成上面这样，MySQL 的执行计划很可能是先对orders表做全表扫描，扫描过程中把@rownum已经累加完了，最后才做ORDER BY amount DESC排序。结果就是：序号 1 对应的是表里物理存储的第一行，而不是金额最大的第一行。这就是经典的“编号错乱”问题。

正确做法是先让子查询把排序做完，外层再对排好序的结果编号：

SELECT @rownum := @rownum + 1 AS row_num, t.order_id, t.amount FROM ( SELECT order_id, amount FROM orders ORDER BY amount DESC ) t CROSS JOIN (SELECT @rownum := 0) r;

你可能会觉得多套一层子查询很绕，但这正是为了解决执行顺序问题。在 MySQL 5.7 中，派生表（子查询）的结果会先物化成一个临时表，物化过程中ORDER BY可能已经生效，外层再对结果集编号，顺序就稳了。这个“先排序后编号”的心法，是整个老版本加序号方案里最核心的经验，值得刻在脑子里。

3.3 通过自连接生成序号的老思路

还有一种更古老但很有意思的思路：自连接计数。原理是，对于每一行，去统计“满足某个条件且排在自己前面的行数”，这个数量加 1 就是序号。例如：

SELECT a.order_id, a.amount, COUNT(*) AS row_num FROM orders a JOIN orders b ON b.amount >= a.amount GROUP BY a.order_id, a.amount ORDER BY a.amount DESC;

这里JOIN orders b ON b.amount >= a.amount表示把每一行 a 与所有金额大于等于它的行 b 关联，COUNT(*)统计出来的就是“比自己大或相等的行数”，也就是该行按金额倒序的排名。这个方案不需要变量，不依赖版本，但有一个致命缺陷：性能极差。两张大表做非等值连接，是 O(n²) 级别的复杂运算，我实测几万行的表就会卡到无法接受。

所以这个方案我一般不推荐，只把它当作理解“排名本质”的入门例子，它揭示了一个核心思想：序号 = 比自己更满足排序条件的行数 + 1。理解了这一点，再去理解窗口函数就容易多了。

4. 分页场景下加序号的正确姿势与逻辑陷阱

4.1 最常见的分页序号 Bug

给查询结果加序号，一旦和分页结合，问题就变得非常微妙。我举一个典型失误案例：

-- 典型错误写法（MySQL 8.0 同样不推荐） SELECT ROW_NUMBER() OVER (ORDER BY create_time DESC) AS row_num, order_id, amount FROM orders LIMIT 10, 20;

这个 SQL 的逻辑问题是，LIMIT的执行时机在窗口函数之后。也就是说，窗口函数先对整个结果集编号，编号到第 30 行，然后LIMIT才把第 11 到第 30 行取出来。最终返回结果的row_num是 11 到 30，不是 1 到 20。如果前端表格拿这个列展示序号，第一页就是 1-20，第二页直接跳成 11-30，用户会一脸懵。

正确做法是先分页，后编号：

SELECT ROW_NUMBER() OVER (ORDER BY t.create_time DESC) AS row_num, t.order_id, t.amount FROM ( SELECT order_id, amount, create_time FROM orders WHERE create_time >= '2024-01-01' ORDER BY create_time DESC LIMIT 10, 20 ) t;

关键点是：分页操作在子查询里完成，外层拿到的是确定的一页数据，然后对这个页内的 20 行重新编号。这样不管翻到哪一页，row_num都是 1 到 20，符合前端表格展示的预期。

4.2 “全局连续序号”和“每页独立序号”是两种需求

你可能会发现，有时候业务方要的不是“每页都从 1 开始”，而是“跨页连续”，比如第一页最后一个是 20，第二页第一个必须是 21。这种需求多半是想把序号当作真正的记录编号导出，而不是单纯给表格看。

实现思路也很清晰：先用子查询算出全局行号，再从这个子查询里取分页段。注意子查询里要先加行号，再LIMIT：

SELECT t.row_num, t.order_id, t.amount FROM ( SELECT ROW_NUMBER() OVER (ORDER BY create_time DESC) AS row_num, order_id, amount FROM orders ) t LIMIT 10, 20;

这个写法把ROW_NUMBER()放在最内层，全量编号完以后，外层用LIMIT切出对应区段。第二页拿到的就是 11 到 30 的全局连续序号。

这里我给一个非常实用的经验：先问清楚业务方是要页内序号还是全局序号，再决定 SQL 结构。如果是纯展示用表格，页内独立序号最常见；如果要导出 Excel 或者做数据对账，全局连续序号更合理。这两种需求的 SQL 结构恰好相反，前者先分页再编号，后者先编号再分页。我见过有同事在这两个写法之间来回改了三遍，就是因为没提前确认需求。

4.3 老版本分页加序号的稳妥组合

如果是 MySQL 5.7 环境，无法使用窗口函数，分页加序号就要把用户变量和子查询组合起来用，顺序同样是核心。

页内独立序号（先排序、再分页、后编号）：

SELECT @rownum := @rownum + 1 AS row_num, t.order_id, t.amount FROM ( SELECT order_id, amount FROM orders ORDER BY amount DESC LIMIT 10, 20 ) t CROSS JOIN (SELECT @rownum := 0) r;

全局连续序号（先排序、再编号、后分页）：

SELECT t.row_num, t.order_id, t.amount FROM ( SELECT @rownum := @rownum + 1 AS row_num, order_id, amount FROM orders CROSS JOIN (SELECT @rownum := 0) r ORDER BY amount DESC ) t LIMIT 10, 20;

等等，这个“先编号再排序”是不是又掉进 3.2 节说的“编号错乱”坑里了？这里有一个执行计划细节需要说明。当用户变量累加和ORDER BY同时存在于一个查询块里时，MySQL 可能会在排序完成后再对变量赋值，也可能在读取过程中赋值，行为不确定。为了让顺序绝对可靠，稳妥做法是再多套一层子查询：

SELECT @rownum := @rownum + 1 AS row_num, t.order_id, t.amount FROM ( SELECT order_id, amount FROM orders ORDER BY amount DESC ) t CROSS JOIN (SELECT @rownum := 0) r LIMIT 10, 20;

这样执行顺序是：最内层排序 → 中间层编号 → 最外层分页。每层职责单一，不容易出幺蛾子。虽然多套了一层，看起来繁琐，但在 5.7 版本里这就是最稳的写法。我维护过几个老项目，这套结构直接用 SQL 模板固化了下来，任何新需求来了改改字段就能上。

5. 排序稳定性与性能对比的实战经验

5.1 没有排序，序号就是“薛定谔的序号”

给查询结果加序号，有一个绕不开的前提：结果集的顺序必须是确定的。如果查询没有ORDER BY，MySQL 返回行的顺序不保证稳定，那你加上的序号就没有任何业务意义。

实际业务里经常出现“同一份数据，上午查和下午查，序号顺序不一样”的诡异问题，多数是因为底层数据发生了 UPDATE 或 DELETE，导致 InnoDB 扫描索引或回表顺序变化。针对这种情况，唯一有效的解决办法就是给ORDER BY加上唯一性字段做“决胜排序”。比如：

SELECT ROW_NUMBER() OVER (ORDER BY amount DESC, order_id ASC) AS row_num, order_id, amount FROM orders;

amount相同的情况下，用order_id做二次排序，这样每一行的先后顺序是唯一确定的。这个习惯非常重要，我曾经因为漏掉这个“决胜排序”，在翻页的时候出现同一行数据跳到不同的序号上的问题，排查了很久。最后定位到是排序不唯一，加一个自增主键列就彻底解决了。

5.2 窗口函数与用户变量的性能对比

我实际压测过同一张百万行订单表，分别用窗口函数和用户变量加序号，结果是这样的：

窗口函数虽然也会创建排序相关的内部结构，但优化器对它的处理比用户变量的“逐行赋值”更高效，尤其是在多个窗口函数同时使用的场景下，差距更明显。MySQL 8.0 既然已经普及，新项目我普遍建议直接上窗口函数，别再用老一套变量玩法自找麻烦。

不过也别把用户变量一棍子打死。在某些非常特殊的场景里，比如想实现“上一行和下一行对比”这种行间计算，老版本用户变量反而更直接。我在早期做某图像处理 Demo 的数据分析模块时，遇到过需要计算“与上一行差值”的需求，5.7 环境里就是靠用户变量保存上一行的值硬算出来的。如今如果是在 8.0，这个场景直接用LAG()窗口函数即可。

5.3 大数据量下的排序字段选择

给序号排序时，排序字段的选择直接影响性能。如果排序字段没有索引，MySQL 需要把所有结果集加载到内存或者磁盘临时表做排序，量一大就慢。这里我有一个非常实际的建议：排序字段尽量选择有索引的列，或者与查询条件有关联的列，避免用表达式排序。

比如ORDER BY amount + 1这种写法，会让索引失效。应该写成业务上等价的ORDER BY amount，或者把表达式处理放到应用层。又比如排序字段选create_time，如果在WHERE里对create_time加了范围条件，那索引大概率能帮你跳过大量无关行。

我见过一个极端案例：某个报表查询，排序用了一个函数表达式ORDER BY DATE_FORMAT(create_time, '%Y-%m-%d')，结果几万行数据都要全表扫描加文件排序，耗时好几秒。改成直接用create_time排序后，查询时间降到几十毫秒。所以加序号之前，先低头看一眼EXPLAIN的执行计划，出现Using filesort就要警惕了，不是不能用，但至少要知道它意味着排序跑到了临时阶段。

6. 常见问题与排查技巧实录

6.1 序号从 0 开始系类问题

有同学跟我反馈，用窗口函数加序号，结果第一行是 0。其实大概率不是行号的问题，而是 SQL 里写了ROW_NUMBER() OVER (ORDER BY ...) - 1。有人想从 0 开始编号，这是非常常见的需求，比如数组下标风格的展示。但如果你没写减 1 却出现 0，就要检查是不是字段别名冲突了，比如原本就有一个值为 0 的列被选中，恰好在展示界面被当成了序号列。

排查方法很简单：把查询的SELECT列表只留序号列和主键，一条条比对。不要同时查一堆字段，容易看花眼。

6.2 用户变量在连接池中残留问题

这个我在 3.1 节提过一嘴，这里展开说说。连接池环境下，@rownum是会话级的，一个连接被多个请求复用，变量值并不会自动清零。解决办法不是每次手动SET，而是把初始化放进同一个查询的派生表里，一定要用CROSS JOIN (SELECT @rownum := 0) r这种形式。这个写法在 5.7 的派生表优化下表现得很好，不会产生额外的性能负担。

另外提醒一句，如果用了主从复制或者读写分离，某些中间件可能会把带用户变量的 SQL 路由到主库执行，这反而可能影响主库压力。这种情况就更应该优先考虑升级到 8.0，用窗口函数彻底摆脱用户变量。

6.3 翻页时序号实现差异的确认清单

最后我整理一个实操排查清单，按这个顺序查，基本能覆盖常见问题：

1. 确认 MySQL 版本。8.0 以上优先窗口函数，5.7 以下用用户变量。
2. 确认业务要的是页内序号还是全局序号。这一步能决定 SQL 是先分页还是先编号。
3. 确认排序的候选字段是否唯一。不唯一就补一个id做决胜排序。
4. 确认排序字段是否命中索引。看EXPLAIN有没有Using filesort。
5. 确认有没有GROUP BY或DISTINCT影响结果集行数。行数变了，序号含义可能会变。
6. 确认旧版本用户变量的初始化。不要用单独的SET语句，用派生表初始化。

按照这个清单排查下来，目前我见过的“加序号”异常问题，90% 都能定位到具体原因。剩下那 10% 多半是业务对“排名”本身的理解差异，这种就只能靠跟产品经理面对面讨论确认了。

就我个人而言，现在写新 SQL 已经默认用窗口函数了，毕竟 8.0 已经成为主流，代码可读性和维护性都远超变量方案。但在老项目里维护 5.7 代码时，用户变量加子查询那套组合拳依然是我最信任的底牌。这两种手段并存，才能应对各种环境下的加序号需求，希望这篇内容能帮你少踩几个坑。