数据删除 #
一、DELETE基础 #
1.1 基本语法 #
sql
-- 基本删除语法
DELETE FROM table_name
WHERE condition;
-- 示例表
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL,
email VARCHAR(100),
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (name, email, status) VALUES
('Alice', 'alice@example.com', 'active'),
('Bob', 'bob@example.com', 'inactive'),
('Carol', 'carol@example.com', 'deleted'),
('David', 'david@example.com', 'active');
-- 删除单行
DELETE FROM users
WHERE name = 'Alice';
-- 查看结果
SELECT * FROM users;
1.2 删除所有行 #
sql
-- 删除所有行 (慎用!)
DELETE FROM users;
-- 通常应该加 WHERE 条件
DELETE FROM users
WHERE status = 'deleted';
1.3 条件删除 #
sql
-- 等值条件
DELETE FROM users
WHERE email = 'alice@example.com';
-- 范围条件
DELETE FROM users
WHERE created_at < NOW() - INTERVAL '1 year';
-- 多条件组合
DELETE FROM users
WHERE status = 'deleted'
AND created_at < NOW() - INTERVAL '30 days';
-- IN 条件
DELETE FROM users
WHERE id IN ('uuid-1', 'uuid-2', 'uuid-3');
-- NOT IN 条件
DELETE FROM users
WHERE status NOT IN ('active', 'pending');
二、TRUNCATE语句 #
2.1 TRUNCATE语法 #
sql
-- TRUNCATE 快速清空表
TRUNCATE TABLE users;
-- TRUNCATE vs DELETE
-- TRUNCATE: 更快，不触发触发器，重置序列
-- DELETE: 逐行删除，触发触发器，记录日志
-- TRUNCATE 多个表
TRUNCATE TABLE users, orders, logs;
-- TRUNCATE 与 CASCADE
TRUNCATE TABLE users CASCADE; -- 同时删除依赖对象
2.2 TRUNCATE vs DELETE #
text
TRUNCATE vs DELETE 对比
┌─────────────────────────────────────────────────────────────┐
│ │
│ TRUNCATE: │
│ ├── 速度快 │
│ ├── 不触发触发器 │
│ ├── 重置自增序列 │
│ ├── 不能回滚 (在某些数据库) │
│ └── 不记录每行删除日志 │
│ │
│ DELETE: │
│ ├── 速度慢 │
│ ├── 触发触发器 │
│ ├── 不重置序列 │
│ ├── 可以回滚 │
│ └── 记录每行删除日志 │
│ │
│ CockroachDB: │
│ ├── TRUNCATE 和 DELETE 都可以回滚 │
│ └── TRUNCATE 性能更好 │
│ │
└─────────────────────────────────────────────────────────────┘
三、批量删除 #
3.1 分批删除 #
sql
-- 分批删除大量数据
-- 每次删除 1000 行
-- 方法1: 使用 LIMIT
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '90 days'
LIMIT 1000;
-- 方法2: 使用事务
BEGIN;
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '90 days'
LIMIT 1000;
COMMIT;
-- 方法3: 循环删除
DO $$
DECLARE
deleted_count INT;
BEGIN
LOOP
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '90 days'
LIMIT 1000;
GET DIAGNOSTICS deleted_count = ROW_COUNT;
EXIT WHEN deleted_count = 0;
COMMIT;
END LOOP;
END;
$$;
3.2 使用CTE删除 #
sql
-- 使用 CTE 删除
WITH to_delete AS (
SELECT id
FROM users
WHERE status = 'deleted'
LIMIT 1000
)
DELETE FROM users
WHERE id IN (SELECT id FROM to_delete);
-- 使用 CTE 删除并返回结果
WITH deleted AS (
DELETE FROM users
WHERE status = 'inactive'
RETURNING *
)
SELECT COUNT(*) as deleted_count FROM deleted;
四、级联删除 #
4.1 外键级联 #
sql
-- 创建带外键的表
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
amount DECIMAL(10, 2),
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE order_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
product_name VARCHAR(100),
quantity INT
);
-- 删除用户时自动删除相关订单和订单项
DELETE FROM users WHERE id = 'user-uuid';
-- 自动删除 orders 和 order_items 中的相关数据
4.2 手动级联删除 #
sql
-- 手动级联删除
BEGIN;
-- 先删除子表数据
DELETE FROM order_items
WHERE order_id IN (
SELECT id FROM orders WHERE user_id = 'user-uuid'
);
-- 再删除父表数据
DELETE FROM orders WHERE user_id = 'user-uuid';
-- 最后删除主表数据
DELETE FROM users WHERE id = 'user-uuid';
COMMIT;
五、软删除 #
5.1 软删除实现 #
sql
-- 添加软删除字段
ALTER TABLE users
ADD COLUMN deleted_at TIMESTAMP DEFAULT NULL;
-- 软删除 (不真正删除，只标记)
UPDATE users
SET deleted_at = NOW()
WHERE id = 'user-uuid';
-- 查询时过滤已删除数据
SELECT * FROM users
WHERE deleted_at IS NULL;
-- 创建视图简化查询
CREATE VIEW active_users AS
SELECT * FROM users
WHERE deleted_at IS NULL;
-- 使用视图查询
SELECT * FROM active_users;
5.2 软删除最佳实践 #
text
软删除最佳实践
┌─────────────────────────────────────────────────────────────┐
│ │
│ 优点: │
│ ├── 数据可恢复 │
│ ├── 保留历史记录 │
│ └── 支持审计追踪 │
│ │
│ 缺点: │
│ ├── 查询需要额外条件 │
│ ├── 索引需要包含删除标记 │
│ └── 数据量持续增长 │
│ │
│ 建议: │
│ ├── 使用 deleted_at 而不是 is_deleted │
│ ├── 创建过滤视图 │
│ ├── 定期归档已删除数据 │
│ └── 添加唯一索引时排除已删除数据 │
│ │
└─────────────────────────────────────────────────────────────┘
5.3 软删除索引 #
sql
-- 创建部分索引 (只索引未删除数据)
CREATE INDEX idx_users_email ON users(email)
WHERE deleted_at IS NULL;
-- 创建唯一索引 (排除已删除数据)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email)
WHERE deleted_at IS NULL;
-- 查询时自动使用索引
SELECT * FROM users
WHERE email = 'test@example.com'
AND deleted_at IS NULL;
六、RETURNING子句 #
6.1 返回删除结果 #
sql
-- 返回删除的行
DELETE FROM users
WHERE status = 'deleted'
RETURNING *;
-- 返回特定列
DELETE FROM users
WHERE id = 'user-uuid'
RETURNING id, name, email;
-- 返回计算结果
DELETE FROM users
WHERE created_at < NOW() - INTERVAL '1 year'
RETURNING
id,
name,
created_at,
NOW() - created_at AS age;
6.2 使用返回结果 #
sql
-- 删除并归档
WITH deleted AS (
DELETE FROM users
WHERE status = 'deleted'
RETURNING *
)
INSERT INTO users_archive
SELECT *, NOW() AS archived_at FROM deleted;
-- 删除并统计
WITH deleted AS (
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '90 days'
RETURNING *
)
SELECT
COUNT(*) as deleted_count,
MIN(created_at) as oldest_deleted,
MAX(created_at) as newest_deleted
FROM deleted;
七、使用子查询删除 #
7.1 使用子查询条件 #
sql
-- 使用子查询删除
DELETE FROM users
WHERE id IN (
SELECT user_id
FROM orders
WHERE created_at < NOW() - INTERVAL '1 year'
GROUP BY user_id
HAVING COUNT(*) = 0
);
-- 使用相关子查询
DELETE FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);
-- 使用 JOIN 删除
DELETE FROM users
USING orders
WHERE users.id = orders.user_id
AND orders.status = 'cancelled';
7.2 删除重复数据 #
sql
-- 删除重复数据，保留最新的一条
DELETE FROM users u1
WHERE EXISTS (
SELECT 1
FROM users u2
WHERE u2.email = u1.email
AND u2.created_at > u1.created_at
);
-- 使用 CTE 删除重复数据
WITH duplicates AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) as rn
FROM users
)
DELETE FROM users
WHERE id IN (
SELECT id FROM duplicates WHERE rn > 1
);
八、删除性能 #
8.1 性能建议 #
text
删除性能优化建议
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 使用索引 │
│ ├── WHERE 条件使用索引列 │
│ └── 加快定位速度 │
│ │
│ 2. 分批删除 │
│ ├── 减少锁持有时间 │
│ └── 降低冲突概率 │
│ │
│ 3. 使用 TRUNCATE │
│ ├── 清空整表时使用 │
│ └── 性能更好 │
│ │
│ 4. 考虑软删除 │
│ ├── 避免实际删除 │
│ └── 便于恢复 │
│ │
│ 5. 删除后维护 │
│ ├── 清理索引 │
│ └── 更新统计信息 │
│ │
└─────────────────────────────────────────────────────────────┘
8.2 查看执行计划 #
sql
-- 查看删除执行计划
EXPLAIN DELETE FROM users
WHERE status = 'deleted';
-- 查看详细执行计划
EXPLAIN ANALYZE DELETE FROM users
WHERE status = 'deleted';
九、常见问题 #
9.1 外键约束错误 #
sql
-- 问题: 删除有外键引用的行
DELETE FROM users WHERE id = 'user-uuid';
-- Error: foreign key violation
-- 解决方案1: 先删除引用数据
DELETE FROM orders WHERE user_id = 'user-uuid';
DELETE FROM users WHERE id = 'user-uuid';
-- 解决方案2: 使用 CASCADE
DELETE FROM users WHERE id = 'user-uuid' CASCADE;
-- 解决方案3: 设置外键为 ON DELETE CASCADE
ALTER TABLE orders
DROP CONSTRAINT orders_user_id_fkey,
ADD CONSTRAINT orders_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;
9.2 删除大量数据 #
sql
-- 问题: 删除大量数据导致性能问题
-- 解决: 分批删除
DO $$
DECLARE
batch_size INT := 10000;
total_deleted INT := 0;
batch_deleted INT;
BEGIN
LOOP
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '90 days'
LIMIT batch_size;
GET DIAGNOSTICS batch_deleted = ROW_COUNT;
total_deleted := total_deleted + batch_deleted;
EXIT WHEN batch_deleted = 0;
COMMIT;
RAISE NOTICE 'Deleted % rows, total: %', batch_deleted, total_deleted;
END LOOP;
RAISE NOTICE 'Total deleted: %', total_deleted;
END;
$$;
9.3 误删数据恢复 #
sql
-- 使用 AS OF SYSTEM TIME 恢复数据
-- 假设误删发生在 10 分钟前
-- 查看删除前的数据
SELECT * FROM users
AS OF SYSTEM TIME '-10m'
WHERE id = 'deleted-uuid';
-- 恢复数据
INSERT INTO users
SELECT * FROM users
AS OF SYSTEM TIME '-10m'
WHERE id = 'deleted-uuid';
十、总结 #
DELETE 语句要点：
| 类型 | 说明 |
|---|---|
| 基本删除 | 条件删除、批量删除 |
| TRUNCATE | 快速清空表 |
| 级联删除 | 外键级联、手动级联 |
| 软删除 | 标记删除、保留数据 |
| RETURNING | 返回删除结果 |
下一步，让我们学习基础查询操作！
最后更新：2026-03-27