MySQL 模擬 ROW_NUMBER() 分頁排序：變量賦值法與初始化陷阱

MySQL 模擬 ROW_NUMBER() 分頁排序：變量賦值法與初始化陷阱

MySQL 8.0 前不支持窗口函數 ROW_NUMBER()，常用用戶變量賦值模擬行號生成。本文記錄建表測試、變量作用域陷阱、初始化最佳實踐、以及 8.0 原生窗口函數遷移指南。

一、測試環境準備

CREATE TABLE `test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Col1` varchar(50) DEFAULT NULL,
  `Col2` varchar(50) DEFAULT NULL,
  `Col3` int(11) DEFAULT NULL,
  `Col4` float DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test(Col1,Col2,Col3,Col4) VALUES 
(1,2,3,4), (1,2,6,5), (1,2,7,8), (1,'激發',5,7);

二、變量賦值法基礎語法

-- 核心模式
SELECT @row := CASE WHEN @row IS NULL THEN 1 ELSE @row + 1 END AS RN, 
       ID, Col1, Col2, Col3, Col4 
FROM test, (SELECT @row := 0) r;  -- 關鍵：衍生表初始化

三、實測陷阱：變量跨會話殘留導致行號錯誤

3.1 首次執行未顯式初始化

SELECT @row := CASE WHEN @row IS NULL THEN 1 ELSE @row + 1 END AS RN, 
       ID, Col1, Col2, Col3, Col4 
FROM test;

結果： 所有行 RN 均為 1

原因： @row 為會話級用戶變量，首次引用時為 NULL，CASE WHEN @row IS NULL THEN 1 生效，但 MySQL 優化器可能將整個表達式視為常量折疊，導致每行計算時 @row 均視為初始 NULL，未能正確遞增。

3.2 再次執行 (變量已殘留值)

-- 同樣語句再執行一次
-- 結果：RN 變為 2,3,4,5 (接上次殘留值遞增)

驗證： 用戶變量 生命週期 = 會話連接期間，不隨查詢結束重置。

四、正確姿勢：顯式初始化 + 衍生表/聯接

4.1 方法一：衍生表初始化 (推薦、單條 SQL 完成)

SELECT @row := @row + 1 AS RN, t.*
FROM test t, (SELECT @row := 0) r
ORDER BY t.ID;  -- 如需按特定列排序再分頁，必加 ORDER BY

原理： 衍生表 (SELECT @row := 0) r 在查詢執行前單次執行，保證變量歸零。

4.2 方法二：雙語句分離初始化 (調試友好)

SET @row := 0;
SELECT @row := @row + 1 AS RN, t.* FROM test t ORDER BY t.ID;

4.3 方法三：CROSS JOIN 明確語義 (8.0+ 推薦寫法)

SELECT @row := @row + 1 AS RN, t.*
FROM test t
CROSS JOIN (SELECT @row := 0) r
ORDER BY t.ID;

五、進階場景：分組內行號 (PARTITION BY 模擬)

-- 需求：按 Col1 分組，組內按 ID 排序生成行號
SET @row := 0, @prev_col1 := '';
SELECT 
  @row := CASE WHEN @prev_col1 = Col1 THEN @row + 1 ELSE 1 END AS RN,
  @prev_col1 := Col1 AS Col1,
  ID, Col2, Col3, Col4
FROM test
ORDER BY Col1, ID;

六、MySQL 8.0 原生窗口函數遷移指南

MySQL 8.0 完整支持 SQL:2003 標準窗口函數，強烈建議升級後直接使用原生語法：

6.1 ROW_NUMBER() 直接替代

函數用途示例
RANK()並列同名次，跳過下一名次1,1,3,4...
DENSE_RANK()並列同名次，不跳過1,1,2,3...
LAG(col, n)取前 n 行的 col 值環比計算
LEAD(col, n)取後 n 行的 col 值同比/預測
FIRST_VALUE()窗口第一行值基準對比
LAST_VALUE()窗口最後一行值邊界分析
NTILE(n)分桶 (分位數)四分位數


6.3 窗口函數性能優勢

優化器原生支持，可利用索引排序避免 Filesort
無需用戶變量，並發安全、邏輯清晰
支持 FRAME 子句 (RANGE/ROWS BETWEEN) 實現滑動窗口聚合


七、總結與最佳實踐

場景
推薦方案
注意點
MySQL 5.6/5.7 單表行號
衍生表初始化 CROSS JOIN (SELECT @row:=0)
必加 ORDER BY、變量會話級隔離
MySQL 5.6/5.7 分組行號
雙變量 @row, @prev_val + ORDER BY 分組鍵
嚴格保證排序順序
MySQL 8.0+ 所有場景
原生窗口函數 ROW_NUMBER/RANK/LAG/LEAD...
優先使用、性能最優、語法標準
大數據分頁
游標法/ID標記法/覆蓋索引延遲關聯
避免 LIMIT 大偏移量


核心教訓： 用戶變量 必須顯式初始化，依賴隱式 NULL 判斷極其不可靠。MySQL 8.0 窗口函數是標準化、高性能、可維護的終極解法，條件允許請盡早遷移。