PostgreSQL OFFSET 用法与实例
本文介绍了在 PostgreSQL 中如何在 SELECT 语句中使用 OFFSET 子句跳过指定的行数。
有时候，您可能想让 SELECT 语句跳过指定的行数，返回剩余的行。比如，您想要获取除成绩前 10 名之外的所有人的信息。
在 PostgreSQL 中，您可以在 SELECT 语句中使用 OFFSET 子句跳过指定的行数。
PostgreSQL OFFSET 子句介绍
PostgreSQL OFFSET 是 SELECT 语句的可选子句，用于将查询跳过指定的行数。
下面说明了 OFFSET 子句的语法：
OFFSET skipped_rows
这里，skipped_rows 指定要跳过的行数。比如 OFFSET 10 表示跳过 10 行。
带有 OFFSET 子句的 SELECT 语句完整用法如下：
SELECT column_list
FROM table_name
[other_clauses]
OFFSET skipped_rows;
other_clauses 表示可以在 SELECT 语句中使用的其他子句，比如 WHERE, ORDER BY, LIMIT 等
通常，您需要在带有 OFFSET 子句的 SELECT 语句中一同使用 ORDER BY 子句，这样您可以得到一个按照指定的顺序排序的结果集。这是一个好的实践。
在一些分页的查询的应用中，您需要在 SELECT 语句使用 LIMIT 子句和 OFFSET 子句。如下：
SELECT column_list
FROM table_name
ORDER BY ...
LIMIT rows_count OFFSET skipped_rows;
比如:
- 第一页可以使用：
LIMIT 10 OFFSET 0表示最多返回 10 行。 - 第二页可以使用：
LIMIT 10 OFFSET 10表示跳过第一页的 10 行后最多返回 10 行。 - 第三页可以使用：
LIMIT 10 OFFSET 20表示跳过前两页的 20 行后最多返回 10 行。 - 以此类推…
PostgreSQL OFFSET 示例
我们将使用 PostgreSQL Sakila 示例数据库中的 film 表来演示 PostgreSQL OFFSET 的用法。
使用 PostgreSQL OFFSET 跳过示例
要从 film 表查询时跳过前面的 995 行，请使用下面的带有 OFFSET 子句的 SELECT 语句：
SELECT
film_id,
title,
release_year
FROM film
ORDER BY film_id
OFFSET 995;
film_id | title | release_year
---------+-------------------+--------------
996 | YOUNG LANGUAGE | 2006
997 | YOUTH KICK | 2006
998 | ZHIVAGO CORE | 2006
999 | ZOOLANDER FICTION | 2006
1000 | ZORRO ARK | 2006使用 PostgreSQL LIMIT 和 OFFSET 分页查询示例
film 表中共有 1000 行关于影片的信息。您可以通过以下带有 COUNT(*) 表达式的 SELECT 语句得到验证：
SELECT COUNT(*) FROM film;
count
-------
1000假设你有一个系统需要在前端展示所有的影片信息，将 1000 行信息全部展示在一页上并不是一个好的方案。因为这个方案如下的缺点：
- 数据库性能。 一个语句中返回大量的数据会带给数据库服务器更大的内存开销和 IO 消耗。
- 应用性能。大量的数据会导致应用占用更大的内容，甚至回导致应用卡顿、卡死。
- 用户的体验。用户面对大量的数据会眩晕。
一个更好的方案是分页显示所有的影片。您可以很轻松地使用 带有 LIMIT 和 OFFSET 子句的 SELECt 语句实现分页查询。
假设您需要每页显示 10 个影片信息，那么您可以使用如下的语句获取第一页的所有行：
SELECT
film_id,
title,
release_year
FROM film
ORDER BY film_id
LIMIT 10;
这里，我们使用 LIMIT 10 限制了此查询最多返回 10 行。以下是输出：
film_id | title | release_year
---------+------------------+--------------
1 | ACADEMY DINOSAUR | 2006
2 | ACE GOLDFINGER | 2006
3 | ADAPTATION HOLES | 2006
4 | AFFAIR PREJUDICE | 2006
5 | AFRICAN EGG | 2006
6 | AGENT TRUMAN | 2006
7 | AIRPLANE SIERRA | 2006
8 | AIRPORT POLLOCK | 2006
9 | ALABAMA DEVIL | 2006
10 | ALADDIN CALENDAR | 2006您可以使用如下的语句获取第二页的所有行：
SELECT
film_id,
title,
release_year
FROM film
ORDER BY film_id
OFFSET 10
LIMIT 10;
这里，我们使用 OFFSET 10 指示了跳过第一页的 10 行，并使用 LIMIT 10 限制了此查询最多返回 10 行。以下是输出：
film_id | title | release_year
---------+---------------------+--------------
11 | ALAMO VIDEOTAPE | 2006
12 | ALASKA PHANTOM | 2006
13 | ALI FOREVER | 2006
14 | ALICE FANTASIA | 2006
15 | ALIEN CENTER | 2006
16 | ALLEY EVOLUTION | 2006
17 | ALONE TRIP | 2006
18 | ALTER VICTORY | 2006
19 | AMADEUS HOLY | 2006
20 | AMELIE HELLFIGHTERS | 2006同样的，您可以使用如下的语句获取第三页的所有行：
SELECT
film_id,
title,
release_year
FROM film
ORDER BY film_id
OFFSET 20
LIMIT 10;
这里，我们使用 OFFSET 20 指示了跳过前两页的 20 行，并使用 LIMIT 10 限制了此查询最多返回 10 行。
结论
在本文中，您学习了如何使用 PostgreSQL OFFSET 子句跳过指定的行数。 LIMIT 子句，OFFSET 子句和 ORDER BY 子句经常用于解决分页查询问题。