MySQL 查詢結果集添加行號：用戶變量模擬 ROW_NUMBER()

MySQL 查詢結果集添加行號：用戶變量模擬 ROW_NUMBER()

MySQL 5.x/8.0 前無內置 ROW_NUMBER()，通過會話用戶變量 @rownum 實現結果集行號生成，適用於分頁定位、排名顯示、異常行定位。

基礎語法

SET @mycnt = 0;
SELECT 
    (@mycnt := @mycnt + 1) AS rownum,
    othercol
FROM tblname
ORDER BY othercol;

• @mycnt 會話級變量，連接斷開自動釋放。
• := 賦值運算符，在 SELECT 列表中按行遞增。
• 必須配合 ORDER BY，否則行號順序不確定。

進階應用：定位特定記錄在排序後的位置

SET @mycnt = 0;
SELECT * FROM (
    SELECT 
        (@mycnt := @mycnt + 1) AS rownum,
        othercol
    FROM tblname
    ORDER BY othercol
) AS ranked
WHERE othercol = 'TargetValue';

返回該記錄在全量排序中的行號，避免應用層全量拉取計算。

對比方案：臨時表 + AUTO_INCREMENT

CREATE TEMPORARY TABLE tmp_ranked (
    rownum INT AUTO_INCREMENT PRIMARY KEY,
    othercol VARCHAR(255)
) ENGINE=Memory;

INSERT INTO tmp_ranked (othercol)
SELECT othercol FROM tblname ORDER BY othercol;

SELECT * FROM tmp_ranked WHERE othercol = 'TargetValue';

MySQL 8.0+ 標準寫法

SELECT 
    ROW_NUMBER() OVER (ORDER BY othercol) AS rownum,
    othercol
FROM tblname;

• 窗口函數標準、高效、可直接用於 WHERE（通過子查詢/QUALIFY）。
• 5.7 及以下版本堅持用戶變量方案。

行號生成是「分析型查詢」標配。變量法輕量、臨時表法重型、窗口函數法標準。按版本與場景選型，拒絕應用層全量拉取計算行號。