各方言的分页：LIMIT、OFFSET FETCH、TOP 和 ROWNUM

按页获取结果的语法因数据库而异。本课比较五种取得同一页的方法，以及更适合大表的键集分页。

下面每个例子返回的都是同样十行：按 id 排的第 21 到 30 个商品。

SQLite、MySQL、MariaDB、PostgreSQL

这四个数据库都使用 LIMIT <返回行数> OFFSET <跳过行数>。因此，为其中一个写的查询通常可以原样在另外三个上执行。

-- Rows 21 to 30, ordered by id.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

在 Planche 中运行

结果 — 共 10 行，这里是前 5 行

id	name	price
21	Onyx Sofa	1637.9
22	Zen Soundbar	1991.58
23	Onyx Catan Plus	525.91
24	Onyx Sleeping Bag X	2019.45
25	Pixel Lean Startup	188.86

浏览器会另开一个标签页打开 Planche，示例数据库已经载入，这条查询也已经放好。本页其他示例都会复用同一个标签页；在你按下 F9 之前，什么都不会执行。

MySQL / MariaDB

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

-- The older comma form means the same thing, with the arguments reversed:
-- offset first, then count. Easy to misread, so prefer the form above.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 20, 10;

MySQL / MariaDB — 放在这里供对照。浏览器里的示例库是 SQLite，这条语句在这里跑不了。

MySQL 和 MariaDB 也接受 LIMIT 20, 10。在这种写法里，20 是跳过的行数，10 是返回的行数，等价于 LIMIT 10 OFFSET 20。因为数字的顺序相反，通常用带 OFFSET 的写法更清楚。

PostgreSQL

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

-- PostgreSQL also accepts the SQL standard spelling.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

PostgreSQL — 放在这里供对照。浏览器里的示例库是 SQLite，这条语句在这里跑不了。

SQL Server

-- OFFSET / FETCH requires an ORDER BY. Without one it is a syntax error.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- TOP caps the result but cannot skip, so it only gives you the first page.
SELECT TOP 10 id, name, price
FROM products
ORDER BY id;

SQL Server — 放在这里供对照。浏览器里的示例库是 SQLite，这条语句在这里跑不了。

和上面那一组有两点不同。OFFSET … FETCH 是 ORDER BY 的后缀，不是独立子句，所以没有 ORDER BY 的查询是语法错误，而不是"一页无序的数据"。而更老更短的 TOP 没有办法跳过行：它能给你第一页，之后就无能为力了。

这也是 Planche 为 SQL Server 生成两种形状的原因。编辑器语句上的安全行数上限，是在 SELECT 后面插入 TOP n 实现的，查询里不需要别的东西；而会翻页的表格视图发出的是 OFFSET n ROWS FETCH NEXT m ROWS ONLY，并补上一个 ORDER BY 让它合法。

Oracle

-- Oracle 12c and later.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Before 12c, ROWNUM had to be applied to an already-sorted subquery.
SELECT * FROM (
  SELECT t.*, ROWNUM AS rn FROM (
    SELECT id, name, price FROM products ORDER BY id
  ) t
  WHERE ROWNUM <= 30
)
WHERE rn > 20;

Oracle — 放在这里供对照。浏览器里的示例库是 SQLite，这条语句在这里跑不了。

Oracle 12c（2013）加入了标准子句，不分页的形式就是单独一个 FETCH FIRST n ROWS ONLY。12c 之前根本没有这个子句，只有 ROWNUM——而 ROWNUM 是在行被产生时、也就是排序之前就分配的，所以 WHERE ROWNUM <= 10 ORDER BY price 是把随便取的十行排了个序，而不是给你前十名。例子里的嵌套写法就是绕法：最里层排序，中间层编号，最外层筛选。

OFFSET 到底要付什么代价

OFFSET 很大时，引擎仍需先走过前面的行。OFFSET 10000 要读取 10,010 行，才能返回其中的 10 行。因此，在大表中，页码越靠后，响应时间通常越长。

如果行上有一个唯一且有序的键，就改成按这个键要下一页：

-- The same page without OFFSET: continue after the last id of the
-- previous page. The database jumps straight there through the index.
SELECT id, name, price
FROM products
WHERE id > 20
ORDER BY id
LIMIT 10;