文档

批量更新、删除或插入数据

更新时间:

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

批量插入数据

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

以下示例中使用的测试表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协议不一样,更加精简,插入效率高。

批量更新数据

示例语句

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