数据库原理与选型 / 系统课时

批量插入与从查询结果插入

批量插入与从查询结果插入

本课解决“怎样高效写入多条数据,以及怎样把查询结果直接迁移到目标表”这两个问题。你将使用多行 VALUES 完成批量新增,并用 INSERT INTO SELECT 避免逐行读取再写回。

学习目标

  • 使用多行 VALUES 一次插入多条记录
  • 使用 INSERT INTO SELECT 复制查询结果
  • 校验列数量、类型和事务边界

多行 VALUES 批量插入

多行插入只写一次 INSERT 关键字,每组括号代表一条记录。它比循环执行多条单行 INSERT 更简洁,也能减少解析和事务开销。

sql
DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0)
);

INSERT INTO products (name, category, price)
VALUES
    ('机械键盘', '外设', 399.00),
    ('无线鼠标', '外设', 129.00),
    ('显示器支架', '办公', 259.00);

SELECT id, name, category, price
FROM products
ORDER BY id;

预期结果:

text
id | name       | category | price
1  | 机械键盘   | 外设     | 399.0
2  | 无线鼠标   | 外设     | 129.0
3  | 显示器支架 | 办公     | 259.0

每一行提供的值数量必须与列清单一致。任意一行违反约束时,整条语句通常失败,不会只跳过错误行。

使用事务提交一批数据

数据量较大时,应显式使用事务,避免每条语句单独提交。下面示例把两条批量语句作为一个整体执行。

sql
BEGIN;

INSERT INTO products (name, category, price)
VALUES
    ('USB 集线器', '外设', 89.00),
    ('桌面收纳盒', '办公', 59.00);

INSERT INTO products (name, category, price)
VALUES
    ('笔记本支架', '办公', 169.00);

COMMIT;

SELECT COUNT(*) AS total_rows
FROM products;

预期结果:

text
total_rows
6

如果中途发现数据不正确,应执行 ROLLBACK;,而不是 COMMIT;。

从查询结果插入

INSERT INTO ... SELECT 会把 SELECT 返回的每一行写入目标表,适合归档、迁移和按条件生成数据。目标列与查询列必须数量一致,并且顺序对应。

sql
DROP TABLE IF EXISTS product_archive;

CREATE TABLE product_archive (
    archive_id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    archived_price REAL NOT NULL
);

INSERT INTO product_archive (product_id, name, archived_price)
SELECT id, name, price
FROM products
WHERE category = '办公';

SELECT product_id, name, archived_price
FROM product_archive
ORDER BY product_id;

预期结果:

text
product_id | name       | archived_price
3          | 显示器支架 | 259.0
5          | 桌面收纳盒 | 59.0
6          | 笔记本支架 | 169.0

查询中的表达式也可以直接参与写入。例如 price * 0.9 可以把折后价存入目标列,但应确认目标列精度满足业务要求。

避免重复迁移

重复执行迁移语句可能生成重复记录。常见做法是给业务键添加 UNIQUE 约束,再配合冲突处理;也可以在 SELECT 中使用 NOT EXISTS 过滤已经迁移的数据。

sql
INSERT INTO product_archive (product_id, name, archived_price)
SELECT p.id, p.name, p.price
FROM products AS p
WHERE p.category = '办公'
  AND NOT EXISTS (
      SELECT 1
      FROM product_archive AS a
      WHERE a.product_id = p.id
  );

SELECT changes() AS inserted_rows;

预期结果:

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

目录与阅读设置

阅读设置

17

记录一个想法

批量插入与从查询结果插入

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

登录哪都通