MySQL 中 ROW_NUMBER() 函数详解

ROW_NUMBER() 是 SQL 窗口函数中的一种，用于为查询结果集中的每一行分配一个​​唯一的连续序号​​。与 RANK() 和 DENSE_RANK() 不同，ROW_NUMBER() 不会处理重复值，即使排序字段值相同，也会严格按行顺序递增编号。

一、基础语法

ROW_NUMBER() OVER (

[PARTITION BY 分组字段]

ORDER BY 排序字段 [ASC|DESC]

)

​​PARTITION BY​​：按指定字段分组，每组内重新从1开始编号。​​ORDER BY​​：决定排序逻辑，影响行号的分配顺序。

二、核心特点

​​特性​​​​说明​​唯一性每行序号严格递增，不重复（即使排序字段值相同）灵活性可结合分组（PARTITION BY）实现复杂场景兼容性MySQL 8.0+ 原生支持，低版本需用变量模拟性能影响未优化时可能导致全表扫描，需合理使用索引

三、典型应用场景

1. 数据分页查询

-- 查询第3页数据（每页10条）

WITH paged_data AS (

SELECT

id, name,

ROW_NUMBER() OVER (ORDER BY id) AS row_num

FROM users

)

SELECT *

FROM paged_data

WHERE row_num BETWEEN 21 AND 30;

2. 删除重复数据

-- 保留最新记录（假设 create_time 为时间戳）

DELETE FROM orders

WHERE (id, product_id) IN (

SELECT id, product_id FROM (

SELECT

id, product_id,

ROW_NUMBER() OVER (

PARTITION BY product_id

ORDER BY create_time DESC

) AS rn

FROM orders

) t

WHERE rn > 1 -- 删除重复项，保留最新一条

);

3. 分组取Top N记录

-- 获取每个部门薪资前3名

SELECT *

FROM (

SELECT

name, department, salary,

ROW_NUMBER() OVER (

PARTITION BY department

ORDER BY salary DESC

) AS dept_rank

FROM employees

) ranked

WHERE dept_rank <= 3;

4. 生成唯一流水号

-- 按日期生成订单流水号（格式：YYYYMMDD-0001）

SELECT

order_id,

CONCAT(

DATE_FORMAT(create_time, '%Y%m%d'),

'-',

LPAD(ROW_NUMBER() OVER (

PARTITION BY DATE(create_time)

ORDER BY create_time

), 4, '0')

) AS serial_num

FROM orders;

四、与其他排序函数对比

函数重复值处理示例结果（排序字段值相同）ROW_NUMBER()强制分配不同序号1, 2, 3, 4RANK()相同值共享排名，后续跳过序号1, 1, 3, 4DENSE_RANK()相同值共享排名，后续连续递增1, 1, 2, 3

-- 对比三种函数

SELECT

score,

ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,

RANK() OVER (ORDER BY score DESC) AS rank,

DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank

FROM exam_scores;

五、性能优化技巧

1. 索引设计

为 PARTITION BY 和 ORDER BY 涉及的字段创建联合索引：

CREATE INDEX idx_dept_salary ON employees(department, salary DESC);

2. 减少计算范围

-- 仅处理2023年数据

SELECT *

FROM (

SELECT

order_id, amount,

ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn

FROM orders

WHERE YEAR(order_date) = 2023 -- 先过滤再排序

) t

WHERE rn <= 100;

3. 避免嵌套查询

-- 优化前（性能差）

SELECT * FROM (

SELECT *, ROW_NUMBER() OVER (...) AS rn

FROM large_table

) t WHERE rn <= 100;

-- 优化后（直接使用LIMIT，若逻辑允许）

SELECT *, ROW_NUMBER() OVER (...) AS rn

FROM large_table

ORDER BY ...

LIMIT 100;

六、MySQL低版本兼容方案（5.7及以下）

使用会话变量模拟 ROW_NUMBER()

-- 按部门分组排序

SELECT

department, name, salary,

@row_num := IF(

@current_dept = department,

@row_num + 1,

1

) AS row_num,

@current_dept := department AS dummy

FROM employees

ORDER BY department, salary DESC;

七、常见错误与排查

1. 错误：序号不符合预期

​​原因​​：未正确指定 ORDER BY 或 PARTITION BY​​解决​​：检查排序字段是否明确，分组条件是否合理

2. 错误：性能低下

​​原因​​：未使用索引导致全表扫描​​解决​​：使用 EXPLAIN 分析执行计划，添加必要索引

3. 错误：结果集为空

​​原因​​：外层查询条件与子查询中的 WHERE 冲突​​解决​​：验证过滤条件逻辑

八、最佳实践

​​明确排序规则​​：始终显式指定 ORDER BY 的排序方向（ASC/DESC）​​慎用全局排序​​：避免无 PARTITION BY 的大数据集操作​​监控内存使用​​：窗口函数可能消耗大量临时内存​​版本验证​​：生产环境确认 MySQL 版本 >= 8.0​​结合 CTE 使用​​：提高复杂查询的可读性

WITH ranked_products AS (

SELECT

product_id,

ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn

FROM products

)

SELECT * FROM ranked_products WHERE rn = 1;

​​总结​​：ROW_NUMBER() 是处理行级序号分配的利器，特别适合需要精确控制行顺序的场景。合理使用可显著简化分页、去重、Top N查询等操作，但需注意其对性能的影响，尤其在处理海量数据时需结合索引优化。