RDS如何批量插入更新和删除数据_云数据库 RDS(RDS)-阿里云帮助中心

批量操作可以减少数据库与应用程序的交互次数，提高数据处理的吞吐量。本文将通过示例介绍如何批量插入、更新和删除数据。

批量插入数据

您可以通过如下四种方法进行批量插入数据。

以下示例中使用的测试表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)
```
说明
不同的语言驱动，对应的COPY接口不同，请参见如下文档：
- PostgreSQL JDBC Driver - JDBC 4.2 9.4.1209 API
- PostgreSQL 9.6.2 Documentation — Functions Associated with the COPY Command

批量更新数据

示例语句

-- 批量更新数据
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)