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_numFROM 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 rnFROM 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_rankFROM 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, 4 |
RANK() | 相同值共享排名，后续跳过序号 | 1, 1, 3, 4 |
DENSE_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 rnFROM ordersWHERE YEAR(order_date) = 2023 -- 先过滤再排序
) t
WHERE rn <= 100;3. 避免嵌套查询
-- 优化前（性能差）
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (...) AS rnFROM 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 rnFROM products ) SELECT * FROM ranked_products WHERE rn = 1;
总结：ROW_NUMBER() 是处理行级序号分配的利器，特别适合需要精确控制行顺序的场景。合理使用可显著简化分页、去重、Top N查询等操作，但需注意其对性能的影响，尤其在处理海量数据时需结合索引优化。
 overlay)
