MySQL 5.6 實現 ROW_NUMBER() 分組排序的變量技巧與避坑指南

MySQL 5.x 模擬 ROW_NUMBER()：用戶變量賦值法詳解與陷阱

MySQL 8.0 前無窗口函數，常用會話變量 @rownum 模擬 ROW_NUMBER()。但變量在會話級別**持久化**，若不顯式初始化會導致連續執行結果錯亂。本文通過實測揭示陷阱並給出標準寫法。

問題重現

CREATE TABLE test (ID INT AUTO_INCREMENT PRIMARY KEY, Col1 VARCHAR(50), Col2 VARCHAR(50), Col3 INT, Col4 FLOAT);
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 RNr, ID, Col1, Col2, Col3, Col4 FROM test;
-- 結果 RNr 全為 1（因 @row 初始為 NULL，CASE 只取 THEN 1）

再次執行同語句，RNr 變為 2,3,4,5（會話變量已保留上次值）。

根因：會話變量生命週期

• @row 屬於**當前連接會話**，連接不斷則值保留。
• 首次執行時 @row IS NULL 為真，所有行走 THEN 1。
• 語句結束後 @row 變為最後一行的值（4），下次執行直接走 ELSE @row+1。

正確寫法：查詢內聯初始化（推薦）

SELECT 
    @row := CASE WHEN @row IS NULL THEN 1 ELSE @row + 1 END AS RNr,
    ID, Col1, Col2, Col3, Col4
FROM test
JOIN (SELECT @row := 0) AS vars;  -- 衍生表保證每次查詢重新初始化

• JOIN (SELECT @row := 0) 在查詢啟動時執行，**每次查詢獨立初始化**，不受上次殘留影響。
• 適用於視圖、子查詢、存儲過程中多次調用。

分組編號（PARTITION BY 模擬）

SELECT 
    @rn := CASE WHEN @prev_col1 = Col1 THEN @rn + 1 ELSE 1 END AS rn,
    @prev_col1 := Col1 AS Col1,
    ID, Col2, Col3, Col4
FROM test
JOIN (SELECT @rn := 0, @prev_col1 := '') AS vars
ORDER BY Col1, ID;  -- 必須先按分組字段排序

輸出：每個 Col1 組內編號從 1 開始。

MySQL 8.0+ 現代寫法（強烈建議升級）

-- 全局編號
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS rn, * FROM test;

-- 分組編號
SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY ID) AS rn, * FROM test;

• 原生窗口函數效能優於變量法（避免臨時表、文件排序）。
• 語意清晰、標準 SQL、無會話殘留副作用。

避坑清單

1. **永遠在查詢中初始化變量**（JOIN (SELECT @var := init)），不要依賴外部 SET。
2. ORDER BY 必須放在變量賦值**之前**生效（子查詢/衍生表排序後再賦值）。
3. 變量名建議加前綴（@rn_）避免與其他查詢衝突。
4. 大數據量（>10萬行）變量法會退化為磁盤臨時表，**必須升級 8.0 使用窗口函數**。