MySQL 中 ROW_NUMBER() 函數(shù)最佳實(shí)踐
更新時(shí)間：2025年06月26日 15:09:06 作者：比特森林探險(xiǎn)記
MySQL中ROW_NUMBER()函數(shù),作為窗口函數(shù)為每行分配唯一連續(xù)序號(hào),區(qū)別于RANK()和DENSE_RANK(),特別適合分頁(yè)、去重、TopN等需要精確順序控制的場(chǎng)景,本文給大家介紹MySQL中ROW_NUMBER()函數(shù),感興趣的朋友一起看看吧
MySQL 中 ROW_NUMBER() 函數(shù)詳解
ROW_NUMBER() 是 SQL 窗口函數(shù)中的一種，用于為查詢結(jié)果集中的每一行分配一個(gè)??唯一的連續(xù)序號(hào)??。與 RANK() 和 DENSE_RANK() 不同，ROW_NUMBER() 不會(huì)處理重復(fù)值，即使排序字段值相同，也會(huì)嚴(yán)格按行順序遞增編號(hào)。
一、基礎(chǔ)語(yǔ)法
ROW_NUMBER() OVER (
[PARTITION BY 分組字段]
ORDER BY 排序字段 [ASC|DESC]
)- ??PARTITION BY??：按指定字段分組，每組內(nèi)重新從1開(kāi)始編號(hào)。
- ??ORDER BY??：決定排序邏輯，影響行號(hào)的分配順序。
二、核心特點(diǎn)
| ??特性?? | ??說(shuō)明?? |
|---|---|
| 唯一性 | 每行序號(hào)嚴(yán)格遞增，不重復(fù)（即使排序字段值相同） |
| 靈活性 | 可結(jié)合分組（PARTITION BY）實(shí)現(xiàn)復(fù)雜場(chǎng)景 |
| 兼容性 | MySQL 8.0+ 原生支持，低版本需用變量模擬 |
| 性能影響 | 未優(yōu)化時(shí)可能導(dǎo)致全表掃描，需合理使用索引 |
三、典型應(yīng)用場(chǎng)景
1. 數(shù)據(jù)分頁(yè)查詢
-- 查詢第3頁(yè)數(shù)據(jù)（每頁(yè)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. 刪除重復(fù)數(shù)據(jù)
-- 保留最新記錄（假設(shè) create_time 為時(shí)間戳）
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 -- 刪除重復(fù)項(xiàng)，保留最新一條
);3. 分組取Top N記錄
-- 獲取每個(gè)部門(mé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. 生成唯一流水號(hào)
-- 按日期生成訂單流水號(hào)（格式：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;四、與其他排序函數(shù)對(duì)比
| 函數(shù) | 重復(fù)值處理 | 示例結(jié)果（排序字段值相同） |
|---|---|---|
ROW_NUMBER() | 強(qiáng)制分配不同序號(hào) | 1, 2, 3, 4 |
RANK() | 相同值共享排名，后續(xù)跳過(guò)序號(hào) | 1, 1, 3, 4 |
DENSE_RANK() | 相同值共享排名，后續(xù)連續(xù)遞增 | 1, 1, 2, 3 |
-- 對(duì)比三種函數(shù)
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;五、性能優(yōu)化技巧
1. 索引設(shè)計(jì)
為 PARTITION BY 和 ORDER BY 涉及的字段創(chuàng)建聯(lián)合索引：
CREATE INDEX idx_dept_salary ON employees(department, salary DESC);
2. 減少計(jì)算范圍
-- 僅處理2023年數(shù)據(jù)
SELECT *
FROM (
SELECT
order_id, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
FROM orders
WHERE YEAR(order_date) = 2023 -- 先過(guò)濾再排序
) t
WHERE rn <= 100;3. 避免嵌套查詢
-- 優(yōu)化前（性能差）
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (...) AS rn
FROM large_table
) t WHERE rn <= 100;
-- 優(yōu)化后（直接使用LIMIT，若邏輯允許）
SELECT *, ROW_NUMBER() OVER (...) AS rn
FROM large_table
ORDER BY ...
LIMIT 100;六、MySQL低版本兼容方案（5.7及以下）
使用會(huì)話變量模擬 ROW_NUMBER()
-- 按部門(mén)分組排序
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;七、常見(jiàn)錯(cuò)誤與排查
1. 錯(cuò)誤：序號(hào)不符合預(yù)期
- ??原因??：未正確指定
ORDER BY或PARTITION BY - ??解決??：檢查排序字段是否明確，分組條件是否合理
2. 錯(cuò)誤：性能低下
- ??原因??：未使用索引導(dǎo)致全表掃描
- ??解決??：使用
EXPLAIN分析執(zhí)行計(jì)劃，添加必要索引
3. 錯(cuò)誤：結(jié)果集為空
- ??原因??：外層查詢條件與子查詢中的
WHERE沖突 - ??解決??：驗(yàn)證過(guò)濾條件邏輯
八、最佳實(shí)踐
- ??明確排序規(guī)則??：始終顯式指定
ORDER BY的排序方向（ASC/DESC） - ??慎用全局排序??：避免無(wú)
PARTITION BY的大數(shù)據(jù)集操作 - ??監(jiān)控內(nèi)存使用??：窗口函數(shù)可能消耗大量臨時(shí)內(nèi)存
- ??版本驗(yàn)證??：生產(chǎn)環(huán)境確認(rèn) MySQL 版本 >= 8.0
- ??結(jié)合 CTE 使用??：提高復(fù)雜查詢的可讀性
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;??總結(jié)??：ROW_NUMBER() 是處理行級(jí)序號(hào)分配的利器，特別適合需要精確控制行順序的場(chǎng)景。合理使用可顯著簡(jiǎn)化分頁(yè)、去重、Top N查詢等操作，但需注意其對(duì)性能的影響，尤其在處理海量數(shù)據(jù)時(shí)需結(jié)合索引優(yōu)化。
到此這篇關(guān)于MySQL 中 ROW_NUMBER() 函數(shù)詳解的文章就介紹到這了,更多相關(guān)mysql row_number()函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL死鎖是線上經(jīng)常遇到的現(xiàn)象,MySQL死鎖日志分析方法有助于研發(fā)快速提取信息,提高分析效率,通過(guò)了解死鎖觸發(fā)條件、檢測(cè)機(jī)制及鎖類(lèi)型,結(jié)合日志分析工具,可以更有效地解決死鎖問(wèn)題2024-11-11
window10中mysql8.0修改端口port不生效的解決方法
mysql配置文件默認(rèn)位置,端口號(hào)等信息需要在my.ini文件中修改,若修改安裝位置的my-default文件文件或新建my.ini文件是不生效的,本文主要介紹了window10中mysql8.0修改端口port不生效的解決方法,感興趣的可以了解一下2023-11-11
優(yōu)化mysql數(shù)據(jù)庫(kù)的經(jīng)驗(yàn)總結(jié)
本篇文章是對(duì)優(yōu)化mysql數(shù)據(jù)庫(kù)的經(jīng)驗(yàn)進(jìn)行了詳細(xì)的總結(jié)介紹，需要的朋友參考下2013-06-06
這篇文章主要介紹了MySQL自增ID用完了的四種解決方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2025-06-06
這篇文章主要介紹了mysql中insert與select的嵌套使用方法,代碼功能非常實(shí)用,需要的朋友可以參考下2014-07-07
本文介紹了在Hive中進(jìn)行行轉(zhuǎn)列的幾種方法,包括使用CASE?WHEN/IF、Get_Json_Object、Str_To_Map以及UNION?ALL和EXPLODE函數(shù),每種方法都有其適用場(chǎng)景,感興趣的可以了解一下2024-12-12
Mysql大表全表update的的實(shí)現(xiàn)
有些時(shí)候在進(jìn)行一些業(yè)務(wù)迭代時(shí)需要我們對(duì)Mysql表中數(shù)據(jù)進(jìn)行全表update,本文主要介紹了Mysql大表update的的實(shí)現(xiàn)2024-08-08
MySQL的UUID_SHORT()函數(shù)是一個(gè)用于生成短UUID的函數(shù),該函數(shù)返回一個(gè)64位的整數(shù),可以用于唯一標(biāo)識(shí)一條數(shù)據(jù)記錄,本文介紹了MySQL使用UUID_SHORT()的問(wèn)題解決,感興趣的可以了解一下2023-08-08
Mysql關(guān)于數(shù)據(jù)庫(kù)是否應(yīng)該使用外鍵約束詳解說(shuō)明
MySQL 外鍵約束（FOREIGN KEY）是表的一個(gè)特殊字段，經(jīng)常與主鍵約束一起使用。對(duì)于兩個(gè)具有關(guān)聯(lián)關(guān)系的表而言，相關(guān)聯(lián)字段中主鍵所在的表就是主表，外鍵所在的表就是從表。外鍵用來(lái)建立主表與從表的關(guān)聯(lián)關(guān)系，為兩個(gè)表的數(shù)據(jù)建立連接，約束兩個(gè)表中數(shù)據(jù)的一致性和完整性2021-10-10
詳解MySQL中事務(wù)隔離級(jí)別的實(shí)現(xiàn)原理
這篇文章主要介紹了MySQL中事務(wù)隔離級(jí)別的實(shí)現(xiàn)原理，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2021-01-01