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

MySQL 查詢結果集產生行號（ROW_NUMBER）三種實現方案：變數法、臨時表法、應用層處理

編輯：MySQL 資料庫相關知識

問題背景

MySQL 8.0 前無內建 ROW_NUMBER() 窗口函數（Oracle 8i+、SQL Server 2005+ 已支援）。開發常需「按某欄位排序後，取得某行在排序中的名次」或「分頁顯示行號」。

方案一：使用者變數法（推薦，無臨時表開銷）

SET @rownum := 0;
SELECT @rownum := @rownum + 1 AS row_num, t.*
FROM (
  SELECT * FROM articles ORDER BY publish_time DESC
) t;

進階：取得特定鍵值的排名

SET @rownum := 0;
SELECT * FROM (
  SELECT @rownum := @rownum + 1 AS row_num, id, title
  FROM articles ORDER BY publish_time DESC
) ranked
WHERE id = 12345;  -- 直接得到該文章在最新排序中的名次

方案二：臨時表 + AUTO_INCREMENT（直觀但有主從複寫風險）

CREATE TEMPORARY TABLE tmp_ranked (
  row_num INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  id INT UNSIGNED NOT NULL,
  title VARCHAR(200)
) ENGINE=Memory;

INSERT INTO tmp_ranked (id, title)
SELECT id, title FROM articles ORDER BY publish_time DESC;

SELECT * FROM tmp_ranked WHERE id = 12345;

風險： 主從複寫環境下，CREATE TEMPORARY TABLE 可能導致從庫缺表、INSERT 順序不一致，慎用。

方案三：應用層處理（最通用、零資料庫負擔）

在 PHP/Python/Java/Go 等應用程式中遍歷 ResultSet，自行維護計數器。適合：

結果集不大（< 1 萬行）
需複雜格式化輸出（JSON、CSV、Excel）
跨資料庫遷移相容性要求高

MySQL 8.0+ 原生窗口函數（最佳解）

SELECT
  ROW_NUMBER() OVER (ORDER BY publish_time DESC) AS row_num,
  id, title
FROM articles;

支援 PARTITION BY 分組編號、RANK()、DENSE_RANK() 等完整窗口函數族。

效能與選型建議

方案	適用版本	效能	主從安全	維護性
使用者變數	5.0+	高（單掃描）	✅ 安全	中（SQL 較長）
臨時表	所有	中（雙寫入）	⚠️ 風險	高（直觀）
應用層	所有	視網路/記憶體	✅ 安全	高（程式碼可控）
窗口函數	8.0+	最高（原生優化）	✅ 安全	最高（標準 SQL）