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

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

  • 使用 INSERT INTO ... SELECT 的方法。

      postgres=# INSERT INTO tbl1 (id, info ,crt_time) SELECT GENERATE_SERIES(1,10000),'test',NOW();    
      INSERT 0 10000    
      postgres=# SELECT COUNT(*) FROM tbl1;    
       count     
      -------    
       10001    
      (1 row)
  • 使用 VALUES(),(),...(); 的方法。

    postgres=# INSERT INTO tbl1 (id,info,crt_time) VALUES (1,'test',NOW()), (2,'test2',NOW()), (3,'test3',NOW());    
    INSERT 0 3
  • 使用 BEGIN; ...多条INSERT...; END; 的方法。严格来说,这不属于批量,但可以减少事务提交时的同步等待,同样可以提升性能。

    postgres=# BEGIN;    
    BEGIN    
    postgres=# INSERT INTO tbl1 (id,info,crt_time) VALUES (1,'test',NOW());    
    INSERT 0 1    
    postgres=# INSERT INTO tbl1 (id,info,crt_time) VALUES (2,'test2',NOW());    
    INSERT 0 1    
    postgres=# INSERT INTO tbl1 (id,info,crt_time) VALUES (3,'test3',NOW());    
    INSERT 0 1    
    postgres=# END;    
    COMMIT
  • 使用COPY协议。COPY协议与INSERT协议不一样,更加精简,插入效率高。

      test03=# \d test  
                      Table "public.test"  
        Column  |            Type             | Modifiers   
      ----------+-----------------------------+-----------  
       id       | integer                     | not null  
       info     | text                        |   
       crt_time | timestamp without time zone |   
      Indexes:  
          "test_pkey" PRIMARY KEY, btree (id)  
    
      test03=# COPY test FROM stdin;  
      Enter data to be copied followed by a newline.  
      End with a backslash and a period on a line by itself.  
      >> 8    'test'  '2017-01-01'  
      >> 9    'test9' '2017-02-02'  
      >> \.  
      COPY 2
    说明

批量更新数据

test03=# UPDATE test SET info=tmp.info from (VALUES (1,'new1'),(2,'new2'),(6,'new6')) AS tmp (id,info) WHERE test.id=tmp.id;  
UPDATE 3  
test03=# SELECT * FROM test;  
 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)

批量删除数据

test03=# DELETE FROM test USING (VALUES (3),(4),(5)) AS tmp(id) WHERE test.id=tmp.id;  
DELETE 3  
test03=# SELECT * FROM test;  
 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。

test03=# SET lock_timeout = '1s';
SET
test03=# TRUNCATE test;  
TRUNCATE TABLE  
test03=# SELECT * FROM test;  
 id | info | crt_time   
----+------+----------  
(0 rows)