您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 模擬 ROW_NUMBER() 分頁排序：用戶變量實戰與避坑指南

編輯：MySQL 資料庫相關知識

背景：MySQL 8.0 前無原生窗口函數

MySQL 5.6/5.7 缺乏 ROW_NUMBER()，常用用戶變量模擬。但變量生命周期與會話綁定，**未顯式初始化會導致結果錯誤**。

問題複現

-- 建表測試
CREATE TABLE test (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Col1 VARCHAR(50), Col2 VARCHAR(50), Col3 INT, Col4 FLOAT
) 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);

-- 錯誤寫法：未初始化 @row
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，每行判斷都走 THEN 分支）

正確寫法：顯式初始化

-- 方法一：子查詢初始化（推薦，單條語句）
SELECT 
    @row:=@row+1 AS RNr,
    t.ID, t.Col1, t.Col2, t.Col3, t.Col4
FROM test t
JOIN (SELECT @row:=0) r;

-- 方法二：前置 SET 語句（分兩步，交互式調試用）
SET @row:=0;
SELECT @row:=@row+1 AS RNr, ID, Col1, Col2, Col3, Col4 FROM test;

進階應用：分組內排序（PARTITION BY 模擬）

-- 需求：按 Col1 分組，組內按 Col3 降序編號
SELECT 
    @rn:=CASE WHEN @prev=Col1 THEN @rn+1 ELSE 1 END AS row_num,
    @prev:=Col1 AS grp,
    t.*
FROM (
    SELECT * FROM test ORDER BY Col1, Col3 DESC
) t
JOIN (SELECT @rn:=0, @prev:='') vars;

MySQL 8.0+ 原生窗口函數寫法（對比）

-- 簡潔、標準、優化器友好
SELECT 
    ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col3 DESC) AS row_num,
    ID, Col1, Col2, Col3, Col4
FROM test;

避坑清單

坑點	現象	根因	解決
變量未初始化	序號全 1 或亂序	會話級變量殘留上次值	JOIN (SELECT @var:=0) 強制初始化
ORDER BY 後賦值	序號不按預期順序	MySQL 不保證賦值順序 = 排序順序	子查詢先排序，外層賦值
複雜查詢多次引用	序號跳躍/重複	優化器可能多次執行選項	用 CTE 物化中間結果
主從複製不一致	從庫序號不同	基於語句複製時變量非確定性	啟用 ROW 格式 / 升級 8.0 用原生函數