MySQL 中 ROW_NUMBER() 函数详解_mysql rownum

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 ordersWHERE (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) rankedWHERE 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_numFROM 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_rankFROM 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 -- 先过滤再排序) tWHERE rn <= 100;

3. 避免嵌套查询

-- 优化前（性能差）SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (...) AS rn FROM large_table) t WHERE rn <= 100;-- 优化后（直接使用LIMIT，若逻辑允许）SELECT *, ROW_NUMBER() OVER (...) AS rnFROM large_tableORDER 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 dummyFROM employeesORDER BY department, salary DESC;

---

七、常见错误与排查

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

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

2. 错误：性能低下

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

3. 错误：结果集为空

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

---

八、最佳实践

1. ​​明确排序规则​​：始终显式指定 ORDER BY 的排序方向（ASC/DESC）
2. ​​慎用全局排序​​：避免无 PARTITION BY 的大数据集操作
3. ​​监控内存使用​​：窗口函数可能消耗大量临时内存
4. ​​版本验证​​：生产环境确认 MySQL 版本 >= 8.0
5. ​​结合 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查询等操作，但需注意其对性能的影响，尤其在处理海量数据时需结合索引优化。