SQL LIMIT 和 OFFSET：前 N 行与分页

第 2.7 课 · 阅读时间：约 5 分钟 · 练习题 ↓

SQL LIMIT 子句限制查询返回的行数，OFFSET 则在返回其余行之前跳过指定数量的行。在本课中，你将学习如何获取前 N 行、如何实现分页、为什么 LIMIT 需要 ORDER BY，以及其他数据库如何实现同样的功能。

表中往往有成千上万甚至数百万行，而我们很少需要一次性获取所有行。报表只需要前 10 项，应用程序每页显示 20 条结果。LIMIT 和 OFFSET 只返回你需要的那一部分结果。

LIMIT 子句

LIMIT 设置结果中的最大行数。它写在查询的末尾，位于 ORDER BY 之后。

基本语法

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT row_count;

SELECT title, length
FROM film
ORDER BY length DESC
LIMIT 5;

结果：时长最长的 5 部电影。

为什么 LIMIT 需要 ORDER BY

没有 ORDER BY 时，数据库会按任意方便的顺序返回行，所以“前 5 行”每次都可能不同。请始终先排序再限制行数。

排序还必须是唯一的。在上面的查询中，有几部电影的时长都是 185 分钟，哪几部能进入前 5 由数据库决定。添加一个用于区分相同值的列：

SELECT title, length
FROM film
ORDER BY length DESC, title
LIMIT 5;

结果：时长最长的 5 部电影；时长相同的电影按片名排序，因此每次运行的结果都相同。

OFFSET 子句

OFFSET 在 LIMIT 开始计数之前跳过指定数量的行：

SELECT actor_id, first_name, last_name
FROM actor
ORDER BY actor_id
LIMIT 5 OFFSET 10;

结果：按 actor_id 排序的第 11 到第 15 位演员：跳过前 10 行，返回接下来的 5 行。

MySQL 和 MariaDB 也接受简写形式 LIMIT 10, 5，其中第一个数字是偏移量，第二个数字是行数。显式写法 LIMIT 5 OFFSET 10 更易读，并且在 PostgreSQL 和 SQLite 中同样有效。

使用 LIMIT 和 OFFSET 分页

分页把一个很长的结果分成多页。对于每页 S 行的第 N 页：

• LIMIT S
• OFFSET (N - 1) * S

SELECT film_id, title
FROM film
ORDER BY title, film_id
LIMIT 10 OFFSET 20;

结果：按片名排序的电影列表的第 3 页。

数据库仍然要读取并跳过偏移量之前的所有行，因此偏移量非常大时（例如第 10 000 页）会变慢。对于深度分页，应用程序通常会记住上一页的最后一个值，并用 WHERE 进行过滤。

其他数据库中的 LIMIT

MySQL、MariaDB、PostgreSQL 和 SQLite 支持 LIMIT。其他数据库使用不同的语法：

要点：

• LIMIT n 最多返回 n 行。
• OFFSET n 在返回结果之前跳过 n 行。
• 始终将 LIMIT 与 ORDER BY 一起使用，并让排序唯一，以保证结果稳定。
• 每页 S 行的第 N 页：LIMIT S OFFSET (N - 1) * S。
• SQL Server 使用 TOP，SQL 标准使用 FETCH FIRST n ROWS ONLY。

常见问题

LIMIT 和 OFFSET 有什么区别？

LIMIT 设置返回多少行；OFFSET 设置先跳过多少行。LIMIT 5 OFFSET 10 跳过 10 行并返回接下来的 5 行。

SQL Server 中与 LIMIT 等价的写法是什么？

获取前几行用 SELECT TOP 5 ...，分页用 ORDER BY ... OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY。

为什么带 LIMIT 的查询每次返回的行都不同？

因为查询没有 ORDER BY，或者排序列中有相同的值。请按能唯一标识每一行的列排序，例如把主键作为最后一个排序键。