批量操作可以减少数据库与应用程序的交互次数,提高数据处理的吞吐量。本文将通过示例介绍如何批量插入、更新和删除数据。
批量插入数据
您可以通过如下四种方法进行批量插入数据。
以下示例中使用的测试表tbl1的建表语句如下:
CREATE TABLE tbl1 (
id SERIAL PRIMARY KEY,
info TEXT,
crt_time TIMESTAMP
);
方式一:使用INSERT INTO ... SELECT
示例语句
-- 向tbl1表中批量插入数据 INSERT INTO tbl1 (id, info ,crt_time) SELECT GENERATE_SERIES(1,10000),'test',NOW(); -- 查询数据量 SELECT COUNT(*) FROM tbl1;
返回结果
count ------- 10000 (1 row)
方式二:使用VALUES(),(),...();
示例语句
-- 向tbl1表中批量插入数据 INSERT INTO tbl1 (id,info,crt_time) VALUES (1,'test',NOW()), (2,'test2',NOW()), (3,'test3',NOW()); -- 查询数据量 SELECT COUNT(*) FROM tbl1;
返回结果
count ------- 3 (1 row)
方式三:使用BEGIN; ...多条INSERT...; END;
说明
严格来说,此方式不属于批量,但可以减少事务提交时的同步等待,同样可以提升性能。
示例语句
-- 向tbl1表中批量插入数据 BEGIN; INSERT INTO tbl1 (id,info,crt_time) VALUES (1,'test',NOW()); INSERT INTO tbl1 (id,info,crt_time) VALUES (2,'test2',NOW()); INSERT INTO tbl1 (id,info,crt_time) VALUES (3,'test3',NOW()); END; -- 查询数据量 SELECT COUNT(*) FROM tbl1;
返回结果
count ------- 3 (1 row)
方式四:使用COPY协议
说明
COPY协议与INSERT协议不一样,更加精简,插入效率高。
示例语句
-- 从标准输入流中读取数据,并将数据复制到tbl1表中。 COPY tbl1 FROM stdin; -- 输入第一条数据 1 'test' '2023-01-01' -- 输入第二条数据 2 'test1' '2023-02-02' -- 结束 \. -- 查询数据量 SELECT COUNT(*) FROM tbl1;
返回结果
count ------- 2 (1 row)
说明
批量更新数据
示例语句
-- 批量更新数据
UPDATE tbl1 SET info=tmp.info from (VALUES (1,'new1'),(2,'new2'),(6,'new6')) AS tmp (id,info) WHERE tbl1.id=tmp.id;
-- 查询表数据
SELECT * FROM tbl1;
返回结果
id | info | crt_time
----+--------------+----------------------------
3 | hello | 2017-04-24 15:31:49.14291
4 | digoal0123 | 2017-04-24 15:42:50.912887
5 | hello digoal | 2017-04-24 15:57:29.622045
1 | new1 | 2017-04-24 15:58:55.610072
2 | new2 | 2017-04-24 15:28:20.37392
6 | new6 | 2017-04-24 15:59:12.265915
(6 rows)
批量删除数据
批量清楚部分表数据
示例语句
-- 批量删除数据 DELETE FROM tbl1 USING (VALUES (3),(4),(5)) AS tmp(id) WHERE tbl1.id=tmp.id; -- 查询表数据 SELECT * FROM tbl1;
返回结果
id | info | crt_time ----+---------+---------------------------- 1 | new1 | 2017-04-24 15:58:55.610072 2 | new2 | 2017-04-24 15:28:20.37392 6 | new6 | 2017-04-24 15:59:12.265915
清除全表数据,建议您使用TRUNCATE。
示例语句
-- 设置锁超时时间 SET lock_timeout = '1s'; -- 清空名为tbl1的表中的所有数据 TRUNCATE tbl1; -- 查询表数据 SELECT * FROM tbl1;
返回结果
id | info | crt_time ----+------+---------- (0 rows)
文档内容是否对您有帮助?