MySQL 查詢結果集生成行號 (ROW_NUMBER) 實現技巧

問題背景

MySQL 8.0 前不支援標準 ROW_NUMBER() 窗口函數，需變通實現結果集行號。8.0+ 已原生支援，但舊版本仍廣泛使用。

MySQL 8.0+ 標準寫法

SELECT 
  ROW_NUMBER() OVER (ORDER BY create_time) AS rn,
  id, username, create_time
FROM users;
-- 支援 PARTITION BY 分組編號
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rn

MySQL 5.7 及以下：用戶變數模擬

基本語法

SET @rownum = 0;
SELECT 
  (@rownum := @rownum + 1) AS rn,
  id, username, create_time
FROM users
ORDER BY create_time;

進階：子查詢確保順序

SELECT * FROM (
  SELECT 
    (@rownum := @rownum + 1) AS rn,
    t.*
  FROM (SELECT * FROM users ORDER BY create_time) t
  CROSS JOIN (SELECT @rownum := 0) r
) ranked
WHERE rn BETWEEN 11 AND 20;  -- 分頁應用

應用場景：查詢特定記錄的排名

SET @rn = 0;
SELECT * FROM (
  SELECT 
    (@rn := @rn + 1) AS rank,
    id, score
  FROM scores
  ORDER BY score DESC
) t
WHERE id = 1001;  -- 返回 id=1001 的排名

注意事項與避坑

1. ORDER BY 必須在外層或子查詢中保證，否則行號與預期順序不符。
2. 用戶變數賦值順序不保證 (優化器可能調整)，複雜查詢建議用 CROSS JOIN (SELECT @rn := 0) 初始化。
3. 不支援 PARTITION BY，分組編號需多次查詢或應用層處理。
4. 複制/主從環境：用戶變數基於語句複制 (SBR) 可能導致主從不一致，建議 ROW 格式或升級 8.0 使用窗口函數。
5. 效能： 全表掃描 + 排序，大數據集慎用，配合 LIMIT 下推優化。

替代方案：臨時表 + AUTO_INCREMENT

CREATE TEMPORARY TABLE tmp_ranked (
  rn INT AUTO_INCREMENT PRIMARY KEY,
  id INT,
  username VARCHAR(50),
  create_time DATETIME
) ENGINE=Memory;

INSERT INTO tmp_ranked (id, username, create_time)
SELECT id, username, create_time FROM users ORDER BY create_time;

SELECT * FROM tmp_ranked WHERE rn BETWEEN 11 AND 20;

優點：行號持久化、可索引。缺點：需建表權限、臨時表在主從複制可能有問題 (STATEMENT 格式)。

總結