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)
