PostgreSQL DELETE 语句详解

数据删除是数据库管理的关键操作

与插入和更新不同，删除操作具有不可逆性，需特别谨慎处理。本章将用通俗易懂的方式讲解安全高效的删除策略。

1️⃣ 基础概念：什么是DELETE？

1.1 核心语法解析

DELETE FROM 表名
WHERE 条件; 

类比理解

DELETE操作就像图书馆清理旧书：

• WHERE 条件 = 选择标准（如出版年份<2000）
• 无 WHERE = 清空整个图书馆（危险！）

1.2 操作流程图解

2️⃣ 三大实际应用场景

场景1：电商库存清理 🛒

业务背景：电商平台需定期清理下架商品
问题：手动删除易出错，需精确清理特定类别的过期商品

-- 创建商品表（简化版）
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    last_stock_date DATE  -- 最后入库日期
);

-- 示例数据
INSERT INTO products (name, category, last_stock_date) VALUES
('iPhone 12', '手机', '2023-05-10'),
('机械键盘', '外设', '2024-01-15'),
('Type-C数据线', '配件', '2022-11-30'); 

清理操作：

-- 删除1年以上未补货的配件
DELETE FROM products
WHERE category = '配件'
AND last_stock_date < CURRENT_DATE - INTERVAL '1 year'
RETURNING *; -- 返回被删除的数据

处理结果：

 id |      name       | category | last_stock_date
----+-----------------+----------+-----------------
  3 | Type-C数据线    | 配件     | 2022-11-30
(1 row)

效果：✅ 自动清理过期商品 ✅ 避免误删热销商品 ✅ 返回删除记录供审计

---

场景2：用户数据合规清理 👥

业务背景：GDPR要求删除2年未活跃用户数据
问题：用户数据关联多个表（订单/评论等），需级联删除

错误示例

DELETE FROM users WHERE last_login < '2022-01-01';
-- 报错：违反外键约束

正确方案

-- 先删除关联数据
DELETE FROM orders
WHERE user_id IN (
    SELECT id FROM users
    WHERE last_login < '2022-01-01'
);

-- 再删除用户
DELETE FROM users
WHERE last_login < '2022-01-01';

级联删除风险

若使用 ON DELETE CASCADE，删除用户将自动删除所有关联数据，可能导致数据意外丢失！

---

场景3：海量日志批量清理 📊

业务背景：每日产生10万+日志记录，需保留最近30天
问题：单次删除百万数据会锁表，影响业务

-- 分批删除（每次1000条）
DO $$
DECLARE
    batch_size INT := 1000;
    deleted_rows INT;
BEGIN
    LOOP
        DELETE FROM access_log
        WHERE log_date < CURRENT_DATE - INTERVAL '30 days'
        LIMIT batch_size; 

        GET DIAGNOSTICS deleted_rows = ROW_COUNT;

        EXIT WHEN deleted_rows = 0; -- 退出条件

        COMMIT;  -- 提交批次
        RAISE NOTICE '已删除 % 行', deleted_rows;
        PERFORM pg_sleep(0.5); -- 暂停减少负载
    END LOOP;
END $$;

执行效果：

NOTICE:  已删除 1000 行
NOTICE:  已删除 1000 行
...
NOTICE:  已删除 420 行  -- 最后批次

3️⃣ 安全删除四原则

原则1：删除前验证

-- 先用SELECT预览
SELECT * FROM employees
WHERE departure_date < '2023-01-01';

-- 确认后再删除
DELETE FROM employees
WHERE departure_date < '2023-01-01';

原则2：事务保护盾

BEGIN;  -- 启动事务

DELETE FROM temp_data
WHERE created_at < '2023-06-01';

-- 检查影响
SELECT COUNT(*) FROM temp_data;

ROLLBACK; -- 测试用回滚
-- COMMIT; -- 生产环境提交

原则3：软删除代替物理删除

-- 添加删除标记
ALTER TABLE customers
ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

-- 软删除
UPDATE customers
SET is_deleted = TRUE
WHERE id = 1005;

-- 查询时排除
SELECT * FROM customers
WHERE is_deleted = FALSE;

原则4：备份为王

WARNING

重要删除前必须备份：

pg_dump -U user -d dbname -t target_table > backup.sql

4️⃣ 常见错误急救指南

错误1：外键约束冲突

/* 错误信息：
ERROR: update or delete on table "products" violates
foreign key constraint "orders_product_id_fkey"
*/

-- 解决方案：先删子表记录
DELETE FROM order_items
WHERE product_id = 123;

DELETE FROM products
WHERE id = 123;

错误2：误删全表

-- 悲剧发生：
DELETE FROM important_data;  -- 忘记加WHERE！

-- 急救措施：
1. 立即停止数据库写入
2. 使用WAL日志恢复（需提前开启归档）
3. 从备份恢复（若有）

5️⃣ 删除策略决策树

6️⃣ 终极安全清单 ✅

删除操作安全检查表

• [ ] 已在测试环境验证删除条件
• [ ] 生产环境执行了 SELECT 预览
• [ ] 重要数据已备份（pg_dump）
• [ ] 使用事务包裹操作（BEGIN/COMMIT）
• [ ] 检查外键依赖关系
• [ ] 设置了操作超时时间（statement_timeout）
• [ ] 通知相关人员维护窗口 :::

最后警告

DELETE没有撤销按钮！请始终铭记：
💾 备份是最后的防线 ⏱️ 事务是安全气囊 📋 预览是必经步骤