SQL 删除

您可以删除表中的所有行：

await db.delete(users);

您还可以使用过滤器和条件删除：

await db.delete(users).where(eq(users.name, 'Dan'));

限制

PostgreSQL

MySQL

SQLite

SingleStore

使用 .limit() 向查询添加 limit 子句 - 例如：

await db.delete(users).where(eq(users.name, 'Dan')).limit(2);

delete from "users" where "users"."name" = $1 limit $2;

按顺序排列

使用 .orderBy() 将 order by 子句添加到查询中，按指定字段对结果进行排序：

import { asc, desc } from 'drizzle-orm';

await db.delete(users).where(eq(users.name, 'Dan')).orderBy(users.name);
await db.delete(users).where(eq(users.name, 'Dan')).orderBy(desc(users.name));

// order by multiple fields
await db.delete(users).where(eq(users.name, 'Dan')).orderBy(users.name, users.name2);
await db.delete(users).where(eq(users.name, 'Dan')).orderBy(asc(users.name), desc(users.name2));

delete from "users" where "users"."name" = $1 order by "name";
delete from "users" where "users"."name" = $1 order by "name" desc;

delete from "users" where "users"."name" = $1 order by "name", "name2";
delete from "users" where "users"."name" = $1 order by "name" asc, "name2" desc;

带返回值的删除

PostgreSQL

SQLite

MySQL

SingleStore

您可以在 PostgreSQL 和 SQLite 中删除一行并获取返回值：

const deletedUser = await db.delete(users)
  .where(eq(users.name, 'Dan'))
  .returning();

// 部分返回
const deletedUserIds: { deletedId: number }[] = await db.delete(users)
  .where(eq(users.name, 'Dan'))
  .returning({ deletedId: users.id });

使用 WITH DELETE 子句

查看如何使用 WITH 语句与选择、插入、更新

使用 with 子句可以通过将复杂查询分割成称为公共表表达式（CTE）的小子查询来简化复杂查询：

const averageAmount = db.$with('average_amount').as(
  db.select({ value: sql`avg(${orders.amount})`.as('value') }).from(orders)
);

const result = await db
	.with(averageAmount)
	.delete(orders)
	.where(gt(orders.amount, sql`(select * from ${averageAmount})`))
	.returning({
		id: orders.id
	});

with "average_amount" as (select avg("amount") as "value" from "orders") 
delete from "orders" 
where "orders"."amount" > (select * from "average_amount") 
returning "id"