Drizzle | SQL 限制/偏移分页
PostgreSQL
MySQL
SQLite
This guide assumes familiarity with:

本指南演示了如何在 Drizzle 中实现 limit/offset 分页：

index.ts
schema.ts
import { asc } from 'drizzle-orm';
import { users } from './schema';

const db = drizzle(...);

await db
  .select()
  .from(users)
  .orderBy(asc(users.id)) // order by is mandatory
  .limit(4) // the number of rows to return
  .offset(4); // the number of rows to skip
select * from users order by id asc limit 4 offset 4;
// 5-8 rows returned
[
  {
    id: 5,
    firstName: 'Beth',
    lastName: 'Davis',
    createdAt: 2024-03-11T20:51:46.787Z
  },
  {
    id: 6,
    firstName: 'Charlie',
    lastName: 'Miller',
    createdAt: 2024-03-11T21:15:46.787Z
  },
  {
    id: 7,
    firstName: 'Clara',
    lastName: 'Wilson',
    createdAt: 2024-03-11T21:33:46.787Z
  },
  {
    id: 8,
    firstName: 'David',
    lastName: 'Moore',
    createdAt: 2024-03-11T21:45:46.787Z
  }
]

Limit 是返回 (page size) 的行数，offset 是跳过 ((page number - 1) * page size) 的行数。为了保持一致的分页，请确保按唯一列排序。否则，结果可能不一致。

如果你需要按非唯一列排序，则还应在排序后附加一个唯一列。

使用 2 列实现 limit/offset 分页的方法：

const getUsers = async (page = 1, pageSize = 3) => {
  await db
    .select()
    .from(users)
    .orderBy(asc(users.firstName), asc(users.id)) // order by first_name (non-unique), id (pk)
    .limit(pageSize) 
    .offset((page - 1) * pageSize);
}

await getUsers();

Drizzle 拥有实用的关系查询 API，可让你轻松实现 limit/offset 分页：

import * as schema from './db/schema';

const db = drizzle({ schema });

const getUsers = async (page = 1, pageSize = 3) => {
  await db.query.users.findMany({
    orderBy: (users, { asc }) => asc(users.id),
    limit: pageSize,
    offset: (page - 1) * pageSize,
  });
};

await getUsers();

Drizzle 拥有简单灵活的 API，可让你轻松创建自定义解决方案。使用 .$dynamic() 函数创建自定义分页函数的方法：

import { SQL, asc } from 'drizzle-orm';
import { PgColumn, PgSelect } from 'drizzle-orm/pg-core';

function withPagination<T extends PgSelect>(
  qb: T,
  orderByColumn: PgColumn | SQL | SQL.Aliased,
  page = 1,
  pageSize = 3,
) {
  return qb
    .orderBy(orderByColumn)
    .limit(pageSize)
    .offset((page - 1) * pageSize);
}

const query = db.select().from(users); // query that you want to execute with pagination

await withPagination(query.$dynamic(), asc(users.id));

你可以使用 deferred join 技术来提高 limit/offset 分页的性能。此方法对数据子集而不是整个表执行分页。

可以这样做：

const getUsers = async (page = 1, pageSize = 10) => {
   const sq = db
    .select({ id: users.id })
    .from(users)
    .orderBy(users.id)
    .limit(pageSize)
    .offset((page - 1) * pageSize)
    .as('subquery');

   await db.select().from(users).innerJoin(sq, eq(users.id, sq.id)).orderBy(users.id);
};

limit/offset 分页的优势：它易于实现，页面易于访问，这意味着你可以导航到任何页面，而无需保存先前页面的状态。

limit/offset 分页的缺点：随着偏移量的增加，查询性能会下降，因为数据库必须扫描偏移量之前的所有行才能跳过它们；数据偏移会导致不一致，这可能导致同一行返回到不同的页面或跳过某些行。

工作原理：

const getUsers = async (page = 1, pageSize = 3) => {
  await db
    .select()
    .from(users)
    .orderBy(asc(users.id))
    .limit(pageSize)
    .offset((page - 1) * pageSize);
};

// user is browsing the first page
await getUsers();
// results for the first page
[
  {
    id: 1,
    firstName: 'Alice',
    lastName: 'Johnson',
    createdAt: 2024-03-10T17:17:06.148Z
  },
  {
    id: 2,
    firstName: 'Alex',
    lastName: 'Smith',
    createdAt: 2024-03-10T17:19:06.147Z
  },
  {
    id: 3,
    firstName: 'Aaron',
    lastName: 'Williams',
    createdAt: 2024-03-10T17:22:06.147Z
  }
]
// while user is browsing the first page, a row with id 2 is deleted
await db.delete(users).where(eq(users.id, 2));

// user navigates to the second page
await getUsers(2);
// second page, row with id 3 was skipped
[
  {
    id: 5,
    firstName: 'Beth',
    lastName: 'Davis',
    createdAt: 2024-03-10T17:34:06.147Z
  },
  {
    id: 6,
    firstName: 'Charlie',
    lastName: 'Miller',
    createdAt: 2024-03-10T17:58:06.147Z
  },
  {
    id: 7,
    firstName: 'Clara',
    lastName: 'Wilson',
    createdAt: 2024-03-10T18:16:06.147Z
  }
]

因此，如果你的数据库实时执行频繁的插入和删除操作，或者你需要高性能来对大型表进行分页，则应考虑使用 cursor-based 分页。

要了解有关 deferred join 技术的更多信息，你应该遵循以下指南：Planetscale 分页指南 和 Aaron Francis 编写的高效分页指南。