Skip to main content

分页

Prisma Client 支持偏移分页和基于光标的分页。

¥Prisma Client supports both offset pagination and cursor-based pagination.

偏移分页​

¥Offset pagination

偏移分页使用 skip 和 take 跳过一定数量的结果并选择有限的范围。以下查询跳过前 3 条 Post 记录并返回记录 4 - 7:

¥Offset pagination uses skip and take to skip a certain number of results and select a limited range. The following query skips the first 3 Post records and returns records 4 - 7:

const results = await prisma.post.findMany({
  skip: 3,
  take: 4,
})

要实现结果页，你只需将页数乘以每页显示的结果数 skip 即可。

¥To implement pages of results, you would just skip the number of pages multiplied by the number of results you show per page.

✔ 偏移分页的优点​

¥✔ Pros of offset pagination

  • 你可以立即跳转到任何页面。例如，你可以 skip 200 条记录和 take 10，这模拟直接跳转到结果集的第 21 页（底层 SQL 使用 OFFSET）。这对于基于游标的分页来说是不可能的。

    ¥You can jump to any page immediately. For example, you can skip 200 records and take 10, which simulates jumping straight to page 21 of the result set (the underlying SQL uses OFFSET). This is not possible with cursor-based pagination.

  • 你可以按任何排序顺序对同一结果集进行分页。例如，你可以跳转到按名字排序的 User 条记录列表的第 21 页。这对于基于游标的分页来说是不可能的，它需要按唯一的连续列进行排序。

    ¥You can paginate the same result set in any sort order. For example, you can jump to page 21 of a list of User records sorted by first name. This is not possible with cursor-based pagination, which requires sorting by a unique, sequential column.

✘ 偏移分页的缺点​

¥✘ Cons of offset pagination

  • 偏移分页不会在数据库级别扩展。例如，如果你跳过 200,000 条记录并获取前 10 条记录，则数据库仍然必须遍历前 200,000 条记录，然后才能返回你要求的 10 条记录 - 这会对性能产生负面影响。

    ¥Offset pagination does not scale at a database level. For example, if you skip 200,000 records and take the first 10, the database still has to traverse the first 200,000 records before returning the 10 that you asked for - this negatively affects performance.

偏移分页的用例​

¥Use cases for offset pagination

  • 小结果集的浅分页。例如，博客界面允许你按作者过滤 Post 条记录并对结果进行分页。

    ¥Shallow pagination of a small result set. For example, a blog interface that allows you to filter Post records by author and paginate the results.

示例：过滤和偏移分页​

¥Example: Filtering and offset pagination

以下查询返回 email 字段包含 prisma.io 的所有记录。查询跳过前 40 条记录并返回记录 41 - 50.

¥The following query returns all records where the email field contains prisma.io. The query skips the first 40 records and returns records 41 - 50.

const results = await prisma.post.findMany({
  skip: 40,
  take: 10,
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
})

示例：排序和偏移分页​

¥Example: Sorting and offset pagination

以下查询返回 email 字段包含 Prisma 的所有记录，并按 title 字段对结果进行排序。该查询跳过前 200 条记录并返回记录 201 - 220.

¥The following query returns all records where the email field contains Prisma, and sorts the result by the title field. The query skips the first 200 records and returns records 201 - 220.

const results = await prisma.post.findMany({
  skip: 200,
  take: 20,
  where: {
    email: {
      contains: 'Prisma',
    },
  },
  orderBy: {
    title: 'desc',
  },
})

基于光标的分页​

¥Cursor-based pagination

基于游标的分页使用 cursor 和 take 在给定游标之前或之后返回一组有限的结果。游标为你在结果集中的位置添加书签，并且必须是唯一的连续列 - 例如 ID 或时间戳。

¥Cursor-based pagination uses cursor and take to return a limited set of results before or after a given cursor. A cursor bookmarks your location in a result set and must be a unique, sequential column - such as an ID or a timestamp.

以下示例返回包含单词 "Prisma" 的前 4 条 Post 记录，并将最后一条记录的 ID 保存为 myCursor：

¥The following example returns the first 4 Post records that contain the word "Prisma" and saves the ID of the last record as myCursor:

注意：由于这是第一个查询，因此没有要传入的游标。

¥Note: Since this is the first query, there is no cursor to pass in.

const firstQueryResults = await prisma.post.findMany({
  take: 4,
  where: {
    title: {
      contains: 'Prisma' /* Optional filter */,
    },
  },
  orderBy: {
    id: 'asc',
  },
})

// Bookmark your location in the result set - in this
// case, the ID of the last post in the list of 4.

const lastPostInResults = firstQueryResults[3] // Remember: zero-based index! :)
const myCursor = lastPostInResults.id // Example: 29

下图为前 4 条结果的 ID - 或第 1 页。下一个查询的光标是 29：

¥The following diagram shows the IDs of the first 4 results - or page 1. The cursor for the next query is 29:

第二个查询返回在提供的游标之后包含单词 "Prisma" 的前 4 个 Post 记录（换句话说 - 大于 29 的 ID）：

¥The second query returns the first 4 Post records that contain the word "Prisma" after the supplied cursor (in other words - IDs that are larger than 29):

const secondQueryResults = await prisma.post.findMany({
  take: 4,
  skip: 1, // Skip the cursor
  cursor: {
    id: myCursor,
  },
  where: {
    title: {
      contains: 'Prisma' /* Optional filter */,
    },
  },
  orderBy: {
    id: 'asc',
  },
})

const lastPostInResults = secondQueryResults[3] // Remember: zero-based index! :)
const myCursor = lastPostInResults.id // Example: 52

下图显示了 ID 为 29 的记录之后的前 4 条 Post 记录。在此示例中，新光标为 52：

¥The following diagram shows the first 4 Post records after the record with ID 29. In this example, the new cursor is 52:

常见问题​

¥FAQ

我是否总是必须跳过：1?​

¥Do I always have to skip: 1?

如果你不选择 skip: 1，你的结果集将包括你之前的光标。第一个查询返回 4 个结果，游标为 29：

¥If you do not skip: 1, your result set will include your previous cursor. The first query returns four results and the cursor is 29:

如果没有 skip: 1，第二个查询将在游标之后（包括游标）返回 4 个结果：

¥Without skip: 1, the second query returns 4 results after (and including) the cursor:

如果你 skip: 1，则不包括光标：

¥If you skip: 1, the cursor is not included:

你可以根据所需的分页行为选择是否使用 skip: 1。

¥You can choose to skip: 1 or not depending on the pagination behavior that you want.

我能猜出光标的值吗？​

¥Can I guess the value of the cursor?

如果你猜测下一个光标的值，你将分页到结果集中的未知位置。尽管 ID 是连续的，但你无法预测增量速率（2、20、32 比 1、2、3 更有可能，特别是在筛选的结果集中）。

¥If you guess the value of the next cursor, you will page to an unknown location in your result set. Although IDs are sequential, you cannot predict the rate of increment (2, 20, 32 is more likely than 1, 2, 3, particularly in a filtered result set).

基于游标的分页是否使用底层数据库中游标的概念？​

¥Does cursor-based pagination use the concept of a cursor in the underlying database?

不，游标分页不使用底层数据库 (例如 PostgreSQL) 中的游标。

¥No, cursor pagination does not use cursors in the underlying database (e.g. PostgreSQL).

如果光标值不存在会发生什么？​

¥What happens if the cursor value does not exist?

使用不存在的游标将返回 null。Prisma 客户端不会尝试查找相邻值。

¥Using a nonexistent cursor returns null. Prisma Client does not try to locate adjacent values.

✔ 基于游标的分页的优点​

¥✔ Pros of cursor-based pagination

  • 基于光标的分页比例。底层 SQL 不使用 OFFSET，而是查询所有 ID 大于 cursor 值的 Post 记录。

    ¥Cursor-based pagination scales. The underlying SQL does not use OFFSET, but instead queries all Post records with an ID greater than the value of cursor.

✘ 基于游标的分页的缺点​

¥✘ Cons of cursor-based pagination

  • 你必须按光标排序，光标必须是唯一的连续列。

    ¥You must sort by your cursor, which has to be a unique, sequential column.

  • 仅使用光标无法跳转到特定页面。例如，如果不先请求第 1 页，则无法准确预测哪个光标代表第 400 页（页面大小 20）的开头 - 399.

    ¥You cannot jump to a specific page using only a cursor. For example, you cannot accurately predict which cursor represents the start of page 400 (page size 20) without first requesting pages 1 - 399.

基于游标的分页的用例​

¥Use cases for cursor-based pagination

  • 无限滚动 - 例如，按日期/时间降序对博客文章进行排序，并一次请求 10 篇博客文章。

    ¥Infinite scroll - for example, sort blog posts by date/time descending and request 10 blog posts at a time.

  • 批量分页整个结果集 - 例如，作为长期运行的数据导出的一部分。

    ¥Paging through an entire result set in batches - for example, as part of a long-running data export.

示例：过滤和基于光标的分页​

¥Example: Filtering and cursor-based pagination

const secondQuery = await prisma.post.findMany({
  take: 4,
  cursor: {
    id: myCursor,
  },
  where: {
    title: {
      contains: 'Prisma' /* Optional filter */,
    },
  },
  orderBy: {
    id: 'asc',
  },
})

排序和基于光标的分页​

¥Sorting and cursor-based pagination

基于游标的分页要求你按顺序、唯一的列（例如 ID 或时间戳）进行排序。这个值 - 称为光标 - 为你在结果集中的位置添加书签，并允许你请求下一组。

¥Cursor-based pagination requires you to sort by a sequential, unique column such as an ID or a timestamp. This value - known as a cursor - bookmarks your place in the result set and allows you to request the next set.

示例：使用基于光标的分页向后分页​

¥Example: Paging backwards with cursor-based pagination

要向后翻页，请将 take 设置为负值。以下查询返回 4 条 Post 记录，其中 id 小于 200，不包括游标：

¥To page backwards, set take to a negative value. The following query returns 4 Post records with an id of less than 200, excluding the cursor:

const myOldCursor = 200

const firstQueryResults = await prisma.post.findMany({
  take: -4,
  skip: 1,
  cursor: {
    id: myOldCursor,
  },
  where: {
    title: {
      contains: 'Prisma' /* Optional filter */,
    },
  },
  orderBy: {
    id: 'asc',
  },
})