PostgreSQL / 系统课时

批量插入数据

批量插入数据

本课解决一次写入多条记录的问题。完成后,你能使用一条 INSERT 插入多组值,减少重复语句和网络往返,并通过实际测试验证批量写入失败时的整体行为。

学习目标

  • 掌握多行 VALUES 的写法
  • 保证每组数据与列清单一致
  • 理解并验证单条批量语句的原子性

一条语句插入多行

在 VALUES 后连续写多组括号,以逗号分隔,每组代表一行。所有行必须提供相同数量的值,并与列清单按顺序对应,同时满足对应列的数据类型和约束。

下面创建一张商品表,一次插入三条商品记录。id 是自动生成的标识列,因此不需要出现在插入列清单中。

sql
DROP TABLE IF EXISTS lesson_product;

CREATE TABLE lesson_product (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name varchar(100) NOT NULL,
    price numeric(10, 2) NOT NULL,
    stock integer NOT NULL DEFAULT 0
);

-- 三组值由同一条 INSERT 一次写入
INSERT INTO lesson_product (name, price, stock)
VALUES
    ('Keyboard', 299.00, 20),
    ('Mouse', 99.00, 50),
    ('Monitor', 1299.00, 8);

-- 按主键排序,检查三条记录是否写入成功
SELECT id, name, price, stock
FROM lesson_product
ORDER BY id;

预期结果

text
 id |   name   |  price  | stock
----+----------+---------+------
  1 | Keyboard |  299.00 |   20
  2 | Mouse    |   99.00 |   50
  3 | Monitor  | 1299.00 |    8

这里的主键从 1 开始,因为示例先删除并重新创建了表。实际项目中,自动生成的主键可能存在间隔,不能假设每次插入后都连续递增。

如果某列有默认值,例如 stock DEFAULT 0,可以从整个插入列清单中省略该列,让每一行使用默认值。但不能只在部分行中随意减少值的数量;需要使用默认值的位置可以明确填写 DEFAULT。

为什么比重复 INSERT 更合适

批量语句减少了 SQL 解析和网络往返次数,特别适合一次提交少量到中等数量的固定数据。例如写入 100 条商品记录,逐条执行通常需要发送 100 条 INSERT,而多行 VALUES 可以用一条语句完成。

不过,批量插入不代表任何数据规模下都应该拼接成一条超长 SQL。参数化插入时,所有行的参数数量会累加,还要考虑语句大小、客户端内存及事务持续时间。

数据量很大时,应进一步考虑 PostgreSQL 的 COPY,它更适合文件或数据流形式的大规模导入。需要获取新生成的主键时,也可以在多行 INSERT 末尾使用 RETURNING id, name 返回实际插入的记录。

失败时如何处理

同一条多行 INSERT 具有语句级原子性。任意一行违反非空、唯一或类型约束时,整条语句失败,不会只写入其中一部分。这与应用连续执行多条独立的 INSERT 不同,后者是否整体回滚取决于事务边界。

沿用上面的商品表,先单独执行下面这条故意包含错误数据的语句:

sql
-- 第二行的 name 为 NULL,违反 NOT NULL 约束
INSERT INTO lesson_product (name, price, stock)
VALUES
    ('Webcam', 199.00, 10),
    (NULL, 9.90, 1);

预期结果:PostgreSQL 报出 null value in column "name" of relation "lesson_product" violates not-null constraint 错误。虽然第一行 Webcam 本身合法,但整条语句失败,两行都不会写入。

在默认自动提交模式下,单独运行以下查询,检查之前成功插入的数据是否保持不变:

sql
-- 同时验证总行数和失败批次中的有效记录是否残留
SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE name = 'Webcam') AS webcam_rows
FROM lesson_product;

预期结果

text
 total | webcam_rows
-------+-------------
     3 |           0

结果表明,原有三条记录保持不变,失败批次中的 Webcam 也没有被部分写入,验证了单条批量插入的原子性。

生产代码仍应控制单批大小,避免一条 SQL 过长、参数过多或事务占用时间过久。批次大小应根据记录宽度、实际性能测试和业务允许的失败范围确定。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

批量插入数据

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通