語法形式
LIMIT row_count
LIMIT offset, row_count
LIMIT row_count OFFSET offset
常用範例
| SQL | 含義 |
|---|
ORDER BY id DESC LIMIT 10按 id 降序取前 10 筆 (最新 10 筆) |
ORDER BY id DESC LIMIT 0, 10同上，明確 offset=0 |
ORDER BY id ASC LIMIT 5, 10按 id 升序跳過前 5 筆，取第 6-15 筆 |
SELECT cat_id FROM shop_goods_type ORDER BY cat_id DESC LIMIT 1取最大 cat_id (單行) |
分頁公式
第 page 頁，每頁 size 條 (page 從 1 開始)
offset = (page - 1) * size
LIMIT offset, size
效能陷阱與優化
- 深分頁問題：
LIMIT 1000000, 20 需掃描 100 萬行再丟棄 → 極慢
- 優化 1：游標分頁 (適合遞增主鍵)
WHERE id > last_seen_id ORDER BY id LIMIT 20
- 優化 2：覆蓋索引：
SELECT id FROM t WHERE ... ORDER BY id LIMIT ... 僅掃描索引
- 優化 3：延遲關聯：
SELECT * FROM t INNER JOIN (SELECT id FROM t WHERE ... ORDER BY id LIMIT 1000000, 20) tmp USING (id)
與 SQL Server/Oracle 對比
| 資料庫 | 語法 |
|---|
| MySQL / PostgreSQL / SQLite | LIMIT / OFFSET |
| SQL Server 2012+ | OFFSET ... ROWS FETCH NEXT ... ROWS ONLY |
| SQL Server 2008- | TOP / ROW_NUMBER() 窗口函數 |
| Oracle 12c+ | OFFSET ... ROWS FETCH NEXT ... ROWS ONLY |
| Oracle 11g- | ROWNUM / ROW_NUMBER() |
