数据库原理与选型 / 系统课时
批量插入与从查询结果插入
批量插入与从查询结果插入
本课解决“怎样高效写入多条数据,以及怎样把查询结果直接迁移到目标表”这两个问题。你将使用多行 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按自己的节奏,确认真正理解。