更新时间:2025-08-19 GMT+08:00
分享

批量更新、删除或插入数据

批量数据操作是一种优化手段,它能显著减少数据库与应用程序之间的频繁交互,从而降低系统开销并提升整体吞吐量。本文将结合具体代码示例,详细讲解如何高效实现数据的批量插入(Bulk Insert)、批量更新(Bulk Update)和批量删除(Bulk Delete),帮助开发者优化数据库性能。

创建示例表结构

CREATE TABLE product_inventory (
item_id INT PRIMARY KEY,
item_name VARCHAR(100),
stock_qty INT,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

批量数据插入方法

  • 方法一:使用多行VALUES语法
    一次性插入多条商品记录。
    INSERT INTO product_inventory (item_id, item_name, stock_qty) 
    VALUES 
        (101, '智能手表', 50),
        (102, '无线耳机', 120),
        (103, '蓝牙音箱', 75),
        (104, '平板电脑', 30);
  • 方法二:结合SELECT生成序列数据
    批量生成100条测试数据。
    INSERT INTO product_inventory (item_id, item_name, stock_qty)
    SELECT 
        200 + n, 
        '测试商品-' || n, 
        (random() * 100)::INT
    FROM generate_series(1, 100) AS n;
  • 方法三:事务包裹的多条INSERT
    BEGIN;
    INSERT INTO product_inventory VALUES (301, '游戏手柄', 40);
    INSERT INTO product_inventory VALUES (302, '机械键盘', 60);
    INSERT INTO product_inventory VALUES (303, '电竞鼠标', 55);
    COMMIT;
  • 方法四:使用COPY命令高效导入

    COPY是PostgreSQL提供的一种高性能数据导入/导出协议,与传统的INSERT语句不同,它采用更精简的通信格式和传输机制,能够显著提升大批量数据的插入效率。具体优势包括:

    • 协议优化:COPY使用二进制或文本流传输数据,避免了逐条解析SQL语句的开销。
    • 性能对比:在插入数万行以上数据时,COPY通常比INSERT快10倍以上。
    • 适用场景:适用于初始化数据、迁移或定期批量导入场景;支持从CSV、二进制文件或程序流中直接读取数据。

    通过COPY命令从CSV格式导入的示例:

    COPY product_inventory FROM STDIN WITH (FORMAT csv);
    401, "4K显示器", 25
    402, "曲面显示器", 18
    403, "便携显示器", 32
    \.

    不同的语言驱动对应的COPY接口不同,参考如下文档:

批量数据更新技术

  • 使用临时表关联更新

    创建临时更新数据表。

    WITH update_data(item_id, new_qty) AS (
        VALUES 
            (101, 45),
            (102, 110),
            (104, 28)
    )
    -- 执行批量更新
    UPDATE product_inventory p
    SET stock_qty = u.new_qty
    FROM update_data u
    WHERE p.item_id = u.item_id;
  • 基于CASE条件的批量更新

    根据不同条件批量更新库存。

    UPDATE product_inventory
    SET stock_qty = CASE
        WHEN item_id = 201 THEN 90
        WHEN item_id = 202 THEN 65
        WHEN item_id = 203 THEN 40
        ELSE stock_qty
    END
    WHERE item_id IN (201, 202, 203);

批量数据删除方案

  • 使用IN子句批量删除

    删除指定ID的商品记录。

    DELETE FROM product_inventory
    WHERE item_id IN (301, 302, 303, 304);
  • 通过子查询条件删除

    删除库存量低于20的商品。

    DELETE FROM product_inventory
    WHERE stock_qty < 20;
  • 使用TRUNCATE快速清空

    清空整个表数据(不可回滚)。

    TRUNCATE TABLE product_inventory;

性能优化建议

  • 事务批处理:将多个操作包裹在单个事务中。
    BEGIN;
    -- 批量操作1
    -- 批量操作2
    COMMIT;
  • 批量大小控制:建议每批处理1000~5000条记录。

相关文档