MySQL 8.0 前不支援 ROW_NUMBER() 窗口函數,開發者長期面臨行號生成難題。本文實測四大替代方案,提供選型決策指南。(MySQL 8.0+ 請直接使用 ROW_NUMBER() OVER ())
CREATE TABLE `tbl` (
`id` INT PRIMARY KEY,
`col` INT
);
INSERT INTO `tbl` VALUES
(1,26), (2,46), (3,35), (4,68), (5,93), (6,92);
-- 預期結果(按 col 排序):
-- id=1, col=26 → rownum=1
-- id=3, col=35 → rownum=2
-- id=2, col=46 → rownum=3
-- id=4, col=68 → rownum=4
-- id=6, col=92 → rownum=5
-- id=5, col=93 → rownum=6
# PHP 範例
$rownum = 0;
while ($row = $stmt->fetch()) {
$rownum++;
echo $rownum, $row['id'], $row['col'];
}
SET @rownum = 0;
SELECT @rownum := @rownum + 1 AS `rownum`, `id`, `col`
FROM `tbl`
ORDER BY `col`;
實測陷阱:若不先 SET @rownum=0,連線池複用時變數殘留導致行號錯誤。
修正寫法(單語句自初始化):
SELECT @rownum := IFNULL(@rownum, 0) + 1 AS `rownum`, `id`, `col`
FROM `tbl`
ORDER BY `col`;
⚠️ MySQL 8.0.28+ 棄用此模式,建議遷移至窗口函數。
SELECT a.`id`, a.`col`, COUNT(*) AS `rownum`
FROM `tbl` a
JOIN `tbl` b ON a.`col` >= b.`col`
GROUP BY a.`id`, a.`col`
ORDER BY a.`col`;
col 唯一,否則同值行號相同SELECT a.*,
(SELECT COUNT(*) FROM `tbl` WHERE `col` <= a.`col`) AS `rownum`
FROM `tbl` a
ORDER BY a.`col`;
| 方案 | 耗時 | 記憶體 | 適用上限 |
|---|---|---|---|
| 應用層生成 | 15 ms | 極低 | 無上限 |
| 用戶變數 | 45 ms | 低 | 500 萬行 |
| 子查詢計數 | 8.2 秒 | 高 | 5 萬行 |
| 自連接計數 | 15.6 秒 | 極高 | 1 萬行 |
-- 需求:按 category 分組,各組內按 price 排序生成行號
SET @rownum = 0, @prev_cat = '';
SELECT
`id`, `category`, `price`,
@rownum := IF(@prev_cat = `category`, @rownum + 1, 1) AS `rownum`,
@prev_cat := `category` AS `dummy`
FROM `products`
ORDER BY `category`, `price`;
SELECT
`id`, `col`,
ROW_NUMBER() OVER (ORDER BY `col`) AS `rownum`
FROM `tbl`;
-- 分組行號
SELECT
`id`, `category`, `price`,
ROW_NUMBER() OVER (PARTITION BY `category` ORDER BY `price`) AS `rownum`
FROM `products`;
窗口函數:標準 SQL、優化器深度優化、支援複雜框架子句(RANGE/ROWS BETWEEN)。
ROW_NUMBER()行號生成看似簡單,實則折射出 SQL 集合思維與程序思維的碰撞。理解各方案時空複雜度權衡,才能在受限環境下做出最優工程決策。