数据删除（DELETE）

在 PostgreSQL 中，删除数据是一个常见且重要的操作。与数据插入和更新一样，删除操作需要谨慎处理，特别是在生产环境中。本章将详细介绍如何安全有效地删除数据。

1. DELETE 语句基础概念

1.1 基本语法结构

DELETE 语句用于从表中删除符合条件的行。其基本语法如下：

sql

DELETE FROM table_name
WHERE condition;

重要特性

DELETE 操作是基于行的，您只能删除整行，而不能删除行中的特定列
如果不使用 WHERE 子句，将删除表中的所有行
DELETE 是一个 DML（数据操作语言）操作，可以回滚

1.2 操作流程图

2. 基础删除操作

2.1 条件删除

最常见的删除操作是根据特定条件删除数据。

基础示例条件删除

sql

-- 创建示例表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    category VARCHAR(50),
    stock_quantity INTEGER
);

-- 插入测试数据
INSERT INTO products (name, price, category, stock_quantity) VALUES
('笔记本电脑', 5999.99, '电子产品', 10),
('无线鼠标', 199.99, '电子产品', 50),
('办公椅', 899.99, '家具', 20),
('键盘', 299.99, '电子产品', 30),
('台灯', 159.99, '家具', 15);

sql

-- 删除价格等于特定值的产品
DELETE FROM products WHERE price = 199.99;

-- 删除价格大于1000的产品
DELETE FROM products WHERE price > 1000;

-- 删除特定类别的产品
DELETE FROM products WHERE category = '家具';

执行结果分析：

操作	影响行数	说明
`DELETE FROM products WHERE price = 199.99`	1	删除无线鼠标
`DELETE FROM products WHERE price > 1000`	1	删除笔记本电脑
`DELETE FROM products WHERE category = '家具'`	2	删除办公椅和台灯

2.2 复杂条件删除

sql

-- 使用多个条件
DELETE FROM products
WHERE price < 300 AND stock_quantity > 25;

-- 使用 IN 操作符
DELETE FROM products
WHERE category IN ('电子产品', '过时商品');

-- 使用模式匹配
DELETE FROM products
WHERE name LIKE '%鼠标%';

-- 使用日期条件（假设有创建时间字段）
DELETE FROM products
WHERE created_at < '2024-01-01';

2.3 全表删除

危险操作删除所有行是一个危险操作，请谨慎使用！

sql

-- 删除表中所有行
DELETE FROM products;

-- 等效操作，但更快（不能回滚）
TRUNCATE TABLE products;

DELETE vs TRUNCATE 比较：

特性	DELETE	TRUNCATE
速度	较慢	很快
可回滚	是	否（除非在事务中）
触发器	会触发	不会触发
WHERE 子句	支持	不支持
自增序列	不重置	重置

3. 高级删除操作

3.1 使用子查询删除

sql

-- 删除库存为0的产品
DELETE FROM products
WHERE id IN (
    SELECT id FROM products
    WHERE stock_quantity = 0
);

-- 删除价格低于平均价格的产品
DELETE FROM products
WHERE price < (
    SELECT AVG(price) FROM products
);

-- 删除同类别中价格最低的产品
DELETE FROM products p1
WHERE price = (
    SELECT MIN(price)
    FROM products p2
    WHERE p2.category = p1.category
);

3.2 关联表删除

当需要基于其他表的条件删除数据时：

sql

-- 创建相关表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    order_date DATE
);

-- 删除从未被订购的产品
DELETE FROM products
WHERE id NOT IN (
    SELECT DISTINCT product_id
    FROM orders
    WHERE product_id IS NOT NULL
);

-- 使用 EXISTS 子查询
DELETE FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.product_id = p.id
);

3.3 批量删除

对于大量数据的删除，建议分批进行：

sql

-- 分批删除（每次删除1000行）
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    LOOP
        DELETE FROM large_table
        WHERE some_condition
        LIMIT 1000;

        GET DIAGNOSTICS deleted_count = ROW_COUNT;

        -- 如果没有更多行可删除，退出循环
        IF deleted_count = 0 THEN
            EXIT;
        END IF;

        -- 提交中间结果并短暂休息
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

4. 安全删除策略

4.1 删除前验证

最佳实践删除数据前，建议先使用 SELECT 语句验证将要删除的数据

sql

-- 第一步：查看将要删除的数据
SELECT * FROM products WHERE price < 100;

-- 第二步：确认无误后执行删除
DELETE FROM products WHERE price < 100;

-- 第三步：验证删除结果
SELECT COUNT(*) as remaining_count FROM products;

4.2 使用事务保护

sql

-- 开始事务
BEGIN;

-- 执行删除操作
DELETE FROM products WHERE category = '测试商品';

-- 检查结果
SELECT COUNT(*) FROM products WHERE category = '测试商品';

-- 如果满意，提交事务
COMMIT;
-- 如果不满意，回滚事务
-- ROLLBACK;

4.3 软删除策略

在某些业务场景中，推荐使用软删除而不是物理删除：

sql

-- 添加删除标记字段
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE products ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

-- 软删除（标记为已删除）
UPDATE products
SET is_deleted = TRUE, deleted_at = NOW()
WHERE id = 1;

-- 查询未删除的数据
SELECT * FROM products WHERE is_deleted = FALSE;

-- 恢复已删除的数据
UPDATE products
SET is_deleted = FALSE, deleted_at = NULL
WHERE id = 1;

5. 删除操作的返回值

5.1 RETURNING 子句

sql

-- 删除时返回被删除的行数据
DELETE FROM products
WHERE stock_quantity = 0
RETURNING id, name, price;

-- 删除并统计总价值
DELETE FROM products
WHERE category = '清仓商品'
RETURNING price,
          SUM(price * stock_quantity) OVER() as total_value;

5.2 获取影响行数

sql

-- 在存储过程或函数中
DO $$
DECLARE
    deleted_rows INTEGER;
BEGIN
    DELETE FROM products WHERE price < 50;
    GET DIAGNOSTICS deleted_rows = ROW_COUNT;
    RAISE NOTICE '删除了 % 行数据', deleted_rows;
END $$;

6. 性能优化

6.1 索引对删除性能的影响

sql

-- 为常用的删除条件创建索引
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_stock ON products(stock_quantity);

-- 复合索引适用于多条件删除
CREATE INDEX idx_products_category_price ON products(category, price);

6.2 大批量删除优化

7. 常见错误和解决方案

7.1 外键约束错误

sql

-- 错误示例：尝试删除被引用的记录
DELETE FROM products WHERE id = 1;
-- ERROR: update or delete on table "products" violates foreign key constraint

-- 解决方案1：先删除子表记录
DELETE FROM orders WHERE product_id = 1;
DELETE FROM products WHERE id = 1;

-- 解决方案2：使用级联删除
ALTER TABLE orders
DROP CONSTRAINT orders_product_id_fkey,
ADD CONSTRAINT orders_product_id_fkey
    FOREIGN KEY (product_id)
    REFERENCES products(id)
    ON DELETE CASCADE;

7.2 权限不足错误

sql

-- 检查当前用户权限
SELECT has_table_privilege('products', 'DELETE');

-- 授予删除权限
GRANT DELETE ON products TO username;

8. 实际应用场景

8.1 数据清理任务

sql

-- 清理过期数据的存储过程
CREATE OR REPLACE FUNCTION cleanup_old_data()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER := 0;
    total_deleted INTEGER := 0;
BEGIN
    -- 删除30天前的日志
    DELETE FROM application_logs
    WHERE created_at < NOW() - INTERVAL '30 days';
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    total_deleted := total_deleted + deleted_count;

    -- 删除已完成的临时订单
    DELETE FROM temp_orders
    WHERE status = 'completed'
    AND created_at < NOW() - INTERVAL '7 days';
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    total_deleted := total_deleted + deleted_count;

    RETURN total_deleted;
END;
$$ LANGUAGE plpgsql;

-- 执行清理
SELECT cleanup_old_data();

8.2 数据归档

sql

-- 归档旧数据的完整流程
CREATE TABLE products_archive (LIKE products INCLUDING ALL);

-- 移动旧数据到归档表
WITH moved_rows AS (
    DELETE FROM products
    WHERE created_at < '2023-01-01'
    RETURNING *
)
INSERT INTO products_archive SELECT * FROM moved_rows;

9. 监控和日志

9.1 删除操作监控

sql

-- 创建删除操作日志表
CREATE TABLE delete_operations_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    deleted_rows INTEGER,
    operation_time TIMESTAMP DEFAULT NOW(),
    user_name VARCHAR(100) DEFAULT current_user,
    conditions TEXT
);

-- 记录删除操作的触发器函数
CREATE OR REPLACE FUNCTION log_delete_operation()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO delete_operations_log (table_name, deleted_rows, conditions)
    VALUES (TG_TABLE_NAME, TG_OP, 'Trigger logged operation');
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

10. 总结

10.1 删除操作核心要点

删除操作最佳实践清单 ✅ 执行前必做：

使用 SELECT 预览要删除的数据
在测试环境中验证删除逻辑
确保有完整的数据备份

✅ 执行中注意：

使用事务保护重要操作
对大量数据采用分批删除
监控删除操作的性能影响

✅ 执行后检查：

验证删除结果的正确性
检查相关约束是否受影响
更新相关统计信息

10.2 删除策略选择

场景	推荐方案	原因
少量数据删除	直接 DELETE	简单高效
大量数据删除	分批删除	避免长时间锁表
全表清空	TRUNCATE	性能最佳
需要恢复的删除	软删除	数据安全
历史数据处理	归档+删除	平衡性能和存储

重要提醒删除操作是不可逆的（除非在事务中回滚），因此在执行任何删除操作前，都应该：

确保有完整的数据备份
在测试环境中验证删除逻辑
使用事务保护重要的删除操作
考虑使用软删除策略代替物理删除

通过掌握这些删除操作的技巧和最佳实践，您可以在 PostgreSQL 中安全、高效地管理数据的删除需求。

数据删除（DELETE） ​

1. DELETE 语句基础概念 ​

1.1 基本语法结构 ​

1.2 操作流程图 ​

2. 基础删除操作 ​

2.1 条件删除 ​

2.2 复杂条件删除 ​

2.3 全表删除 ​

3. 高级删除操作 ​

3.1 使用子查询删除 ​

3.2 关联表删除 ​

3.3 批量删除 ​

4. 安全删除策略 ​

4.1 删除前验证 ​

4.2 使用事务保护 ​

4.3 软删除策略 ​

5. 删除操作的返回值 ​

5.1 RETURNING 子句 ​

5.2 获取影响行数 ​

6. 性能优化 ​

6.1 索引对删除性能的影响 ​

6.2 大批量删除优化 ​

7. 常见错误和解决方案 ​

7.1 外键约束错误 ​

7.2 权限不足错误 ​

8. 实际应用场景 ​

8.1 数据清理任务 ​

8.2 数据归档 ​

9. 监控和日志 ​

9.1 删除操作监控 ​

10. 总结 ​

10.1 删除操作核心要点 ​

10.2 删除策略选择 ​

数据删除（DELETE）

1. DELETE 语句基础概念

1.1 基本语法结构

1.2 操作流程图

2. 基础删除操作

2.1 条件删除

2.2 复杂条件删除

2.3 全表删除

3. 高级删除操作

3.1 使用子查询删除

3.2 关联表删除

3.3 批量删除

4. 安全删除策略

4.1 删除前验证

4.2 使用事务保护

4.3 软删除策略

5. 删除操作的返回值

5.1 RETURNING 子句

5.2 获取影响行数

6. 性能优化

6.1 索引对删除性能的影响

6.2 大批量删除优化

7. 常见错误和解决方案

7.1 外键约束错误

7.2 权限不足错误

8. 实际应用场景

8.1 数据清理任务

8.2 数据归档

9. 监控和日志

9.1 删除操作监控

10. 总结

10.1 删除操作核心要点

10.2 删除策略选择