萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

Oracle、MySQL、SQL Server 三大資料庫分頁查詢語法差異完整對照表

參數定義

  • currentPage:目前頁碼(從 1 開始)
  • pageSize:每頁筆數

語法對照

資料庫分頁語法第 2 頁範例 (pageSize=10)
SQL Server (2012+)
SELECT * FROM Table
ORDER BY Id
OFFSET (@pageSize * (@currentPage-1)) ROWS
FETCH NEXT @pageSize ROWS ONLY;
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
SQL Server (2005-2008 R2)
SELECT TOP @pageSize * FROM Table
WHERE Id NOT IN (
    SELECT TOP (@pageSize * (@currentPage-1)) Id FROM Table ORDER BY Id
)
ORDER BY Id;
TOP 10 WHERE Id NOT IN (TOP 10 Id)
MySQL / MariaDB / PostgreSQL / SQLite
SELECT * FROM Table
ORDER BY Id
LIMIT @pageSize OFFSET @pageSize * (@currentPage-1);
LIMIT 10 OFFSET 10
Oracle (12c+)
SELECT * FROM Table
ORDER BY Id
OFFSET @pageSize * (@currentPage-1) ROWS
FETCH NEXT @pageSize ROWS ONLY;
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
Oracle (11g 及舊版)
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM Table ORDER BY Id
    ) a WHERE ROWNUM <= @pageSize * @currentPage
) WHERE rn > @pageSize * (@currentPage-1);
WHERE rn > 10 AND rn <= 20

效能建議

  • 避免 OFFSET 深分頁:大位移會掃描大量前置資料列。改用 Keyset Pagination(游標分頁):WHERE Id > @lastSeenId ORDER BY Id LIMIT @pageSize。
  • 索引支援:ORDER BY 欄位必須有索引,否則全表排序極慢。
  • 統計資料:定期更新統計資料讓優化器選擇最佳計畫。

程式碼片段:通用分頁 Helper (C#)

public string BuildPageSql(string baseSql, DbType dbType, int page, int size) {
    switch (dbType) {
        case DbType.SqlServer2012: return $"{baseSql} ORDER BY Id OFFSET {size*(page-1)} ROWS FETCH NEXT {size} ROWS ONLY";
        case DbType.SqlServerLegacy: return $"SELECT TOP {size} * FROM ({baseSql}) t WHERE Id NOT IN (SELECT TOP {size*(page-1)} Id FROM ({baseSql}) ORDER BY Id) ORDER BY Id";
        case DbType.MySql: return $"{baseSql} ORDER BY Id LIMIT {size} OFFSET {size*(page-1)}";
        case DbType.Oracle12c: return $"{baseSql} ORDER BY Id OFFSET {size*(page-1)} ROWS FETCH NEXT {size} ROWS ONLY";
        case DbType.OracleLegacy: 
            return $"SELECT * FROM (SELECT a.*, ROWNUM rn FROM ({baseSql} ORDER BY Id) a WHERE ROWNUM <= {size*page}) WHERE rn > {size*(page-1)}";
        default: throw new ArgumentException("Unsupported DB");
    }
}
copyright © 萬盛學電腦網 all rights reserved