阿里云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;

  • 返回结果

    %ignore_pre_15%