MySQL 查詢結果添加序列號：用戶變量兩種寫法與 8.0 窗口函數對比

業務場景

SELECT 結果集需增加行號 (1,2,3...)，用於報表展示、分頁標識、應用層處理。

MySQL 5.6/5.7：用戶變量模擬

寫法一：JOIN 初始化 (單條語句，推薦)

SELECT 
    (@row := @row + 1) AS row_num,
    t.*
FROM table_name t
JOIN (SELECT @row := 0) r;

寫法二：SET + 查詢分離 (交互式調試)

SET @rownum := 0;
SELECT 
    (@rownum := @rownum + 1) AS row_num,
    username
FROM auth_user
LIMIT 1, 5;

MySQL 8.0+：原生 ROW_NUMBER() 窗口函數

SELECT 
    ROW_NUMBER() OVER (ORDER BY id) AS row_num,
    *
FROM table_name;

-- 分組內排序
SELECT 
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank,
    *
FROM employees;

對比總結

避坑指南 (用戶變量)

1. 未初始化：@row 殘留上次值 → 序號錯誤。必須 JOIN (SELECT @row:=0) 或 SET @row:=0
2. ORDER BY 後賦值不保證順序：MySQL 不保證變量賦值順序 = 排序順序。

   -- 錯誤
      SELECT @row:=@row+1, * FROM t ORDER BY score DESC;
      -- 正確：子查詢先排序
      SELECT @row:=@row+1, * FROM (SELECT * FROM t ORDER BY score DESC) t2
      JOIN (SELECT @row:=0) r;

3. LIMIT 影響序號連續性：LIMIT 10, 5 序號會是 11-15 (正確) 或 1-5 (視寫法)
4. 多次引用同一變量：同一語句多個 @row:=@row+1 會多次遞增

最佳實踐建議

1. 新項目直接用 MySQL 8.0+：窗口函數是標準、高效、可維護的選擇
2. 存量 5.7 維護期：用戶變量僅限小結果集、報表導出、一次性腳本
3. 大數據集分頁：不要用序號模擬分頁，直接 LIMIT offset, size 或游標分頁
4. 應用層加行號：最簡單、零 DB 開銷、前端框架原生支援

序列號生成是「顯示層邏輯」下沉到 DB 的典型案例。**能在應用層解決，盡量不下推到 SQL**；必須在 SQL 解決，MySQL 8.0 ROW_NUMBER() 是唯一正解。