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

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

批量插入数据


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

• 
  使用 insert into ... select 的方法。
  1. [backcolor=transparent]   postgres[backcolor=transparent] =#[backcolor=transparent] insert [backcolor=transparent] into[backcolor=transparent] tbl1 [backcolor=transparent] ([backcolor=transparent] id[backcolor=transparent] ,[backcolor=transparent] info [backcolor=transparent] ,[backcolor=transparent] crt_time[backcolor=transparent] )[backcolor=transparent] [backcolor=transparent] select[backcolor=transparent] generate_series[backcolor=transparent] ([backcolor=transparent] 1[backcolor=transparent] ,[backcolor=transparent] 10000[backcolor=transparent] ),[backcolor=transparent] 'test'[backcolor=transparent] ,[backcolor=transparent] now[backcolor=transparent] ();[backcolor=transparent]     
  2. [backcolor=transparent]  INSERT [backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]10000[backcolor=transparent]    
  3. [backcolor=transparent]  postgres[backcolor=transparent]=#[backcolor=transparent] [backcolor=transparent]select[backcolor=transparent] count[backcolor=transparent](*)[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] tbl1[backcolor=transparent];[backcolor=transparent]    
  4. [backcolor=transparent]   count    
  5. [backcolor=transparent]  [backcolor=transparent]-------[backcolor=transparent]    
  6. [backcolor=transparent]   [backcolor=transparent]10001[backcolor=transparent]    
  7. [backcolor=transparent]  [backcolor=transparent]([backcolor=transparent]1[backcolor=transparent] row[backcolor=transparent])
• 
  使用 values(),(),...(); 的方法。
  1. [backcolor=transparent] postgres[backcolor=transparent] =#[backcolor=transparent] insert [backcolor=transparent] into[backcolor=transparent] tbl1 [backcolor=transparent] ([backcolor=transparent] id[backcolor=transparent] ,[backcolor=transparent] info[backcolor=transparent] ,[backcolor=transparent] crt_time[backcolor=transparent] )[backcolor=transparent] values [backcolor=transparent] ([backcolor=transparent] 1[backcolor=transparent] ,[backcolor=transparent] 'test'[backcolor=transparent] ,[backcolor=transparent] now[backcolor=transparent] ()),[backcolor=transparent] [backcolor=transparent] ([backcolor=transparent] 2[backcolor=transparent] ,[backcolor=transparent] 'test2'[backcolor=transparent] ,[backcolor=transparent] now[backcolor=transparent] ()),[backcolor=transparent] [backcolor=transparent] ([backcolor=transparent] 3[backcolor=transparent] ,[backcolor=transparent] 'test3'[backcolor=transparent] ,[backcolor=transparent] now[backcolor=transparent] ());[backcolor=transparent]     
  2. [backcolor=transparent]INSERT [backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]3
• 
  使用 BEGIN; ...多条insert...; END; 的方法。严格来说，这不属于批量，但可以减少事务提交时的同步等待，同样可以提升性能。
  1. [backcolor=transparent] postgres[backcolor=transparent] =#[backcolor=transparent] [backcolor=transparent] begin[backcolor=transparent] ;[backcolor=transparent]     
  2. [backcolor=transparent]BEGIN[backcolor=transparent]    
  3. [backcolor=transparent]postgres[backcolor=transparent]=#[backcolor=transparent] insert [backcolor=transparent]into[backcolor=transparent] tbl1 [backcolor=transparent]([backcolor=transparent]id[backcolor=transparent],[backcolor=transparent]info[backcolor=transparent],[backcolor=transparent]crt_time[backcolor=transparent])[backcolor=transparent] values [backcolor=transparent]([backcolor=transparent]1[backcolor=transparent],[backcolor=transparent]'test'[backcolor=transparent],[backcolor=transparent]now[backcolor=transparent]());[backcolor=transparent]    
  4. [backcolor=transparent]INSERT [backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]1[backcolor=transparent]    
  5. [backcolor=transparent]postgres[backcolor=transparent]=#[backcolor=transparent] insert [backcolor=transparent]into[backcolor=transparent] tbl1 [backcolor=transparent]([backcolor=transparent]id[backcolor=transparent],[backcolor=transparent]info[backcolor=transparent],[backcolor=transparent]crt_time[backcolor=transparent])[backcolor=transparent] values [backcolor=transparent]([backcolor=transparent]2[backcolor=transparent],[backcolor=transparent]'test2'[backcolor=transparent],[backcolor=transparent]now[backcolor=transparent]());[backcolor=transparent]    
  6. [backcolor=transparent]INSERT [backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]1[backcolor=transparent]    
  7. [backcolor=transparent]postgres[backcolor=transparent]=#[backcolor=transparent] insert [backcolor=transparent]into[backcolor=transparent] tbl1 [backcolor=transparent]([backcolor=transparent]id[backcolor=transparent],[backcolor=transparent]info[backcolor=transparent],[backcolor=transparent]crt_time[backcolor=transparent])[backcolor=transparent] values [backcolor=transparent]([backcolor=transparent]3[backcolor=transparent],[backcolor=transparent]'test3'[backcolor=transparent],[backcolor=transparent]now[backcolor=transparent]());[backcolor=transparent]    
  8. [backcolor=transparent]INSERT [backcolor=transparent]0[backcolor=transparent] [backcolor=transparent]1[backcolor=transparent]    
  9. [backcolor=transparent]postgres[backcolor=transparent]=#[backcolor=transparent] [backcolor=transparent]end[backcolor=transparent];[backcolor=transparent]    
  10. [backcolor=transparent]COMMIT
• 
  使用 copy 协议。copy 协议与 insert 协议不一样，更加精简，插入效率高。
  1. [backcolor=transparent]   test03[backcolor=transparent] =#[backcolor=transparent] \d test  
  2. [backcolor=transparent]                  [backcolor=transparent]Table[backcolor=transparent] [backcolor=transparent]"public.test"[backcolor=transparent]  
  3. [backcolor=transparent]    [backcolor=transparent]Column[backcolor=transparent]  [backcolor=transparent]|[backcolor=transparent]            [backcolor=transparent]Type[backcolor=transparent]             [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]Modifiers[backcolor=transparent]  
  4. [backcolor=transparent]  [backcolor=transparent]----------+-----------------------------+-----------[backcolor=transparent]  
  5. [backcolor=transparent]   id       [backcolor=transparent]|[backcolor=transparent] integer                     [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]not[backcolor=transparent] [backcolor=transparent]null[backcolor=transparent]  
  6. [backcolor=transparent]   info     [backcolor=transparent]|[backcolor=transparent] text                        [backcolor=transparent]|[backcolor=transparent]  
  7. [backcolor=transparent]   crt_time [backcolor=transparent]|[backcolor=transparent] timestamp without time zone [backcolor=transparent]|[backcolor=transparent]  
  8. [backcolor=transparent]  [backcolor=transparent]Indexes[backcolor=transparent]:[backcolor=transparent]  
  9. [backcolor=transparent]      [backcolor=transparent]"test_pkey"[backcolor=transparent] PRIMARY KEY[backcolor=transparent],[backcolor=transparent] btree [backcolor=transparent]([backcolor=transparent]id[backcolor=transparent])[backcolor=transparent]  
  11. [backcolor=transparent]  test03[backcolor=transparent]=#[backcolor=transparent] copy test [backcolor=transparent]from[backcolor=transparent] stdin[backcolor=transparent];[backcolor=transparent]  
  12. [backcolor=transparent]  [backcolor=transparent]Enter[backcolor=transparent] data to be copied followed [backcolor=transparent]by[backcolor=transparent] a newline[backcolor=transparent].[backcolor=transparent]  
  13. [backcolor=transparent]  [backcolor=transparent]End[backcolor=transparent] [backcolor=transparent]with[backcolor=transparent] a backslash [backcolor=transparent]and[backcolor=transparent] a period on a line [backcolor=transparent]by[backcolor=transparent] itself[backcolor=transparent].[backcolor=transparent]  
  14. [backcolor=transparent]  [backcolor=transparent]>>[backcolor=transparent] [backcolor=transparent]8[backcolor=transparent]    [backcolor=transparent]'test'[backcolor=transparent]  [backcolor=transparent]'2017-01-01'[backcolor=transparent]  
  15. [backcolor=transparent]  [backcolor=transparent]>>[backcolor=transparent] [backcolor=transparent]9[backcolor=transparent]    [backcolor=transparent]'test9'[backcolor=transparent] [backcolor=transparent]'2017-02-02'[backcolor=transparent]  
  16. [backcolor=transparent]  [backcolor=transparent]>>[backcolor=transparent] \.  
  17. [backcolor=transparent]  COPY [backcolor=transparent]2

  
  [backcolor=transparent]说明：不同的语言驱动，对应的 COPY 接口不同，请参见如下文档。

• 
  PostgreSQL JDBC Driver - JDBC 4.2 9.4.1209 API
• 
  PostgreSQL 9.6.2 Documentation — Functions Associated with the COPY Command

1. [backcolor=transparent]test03[backcolor=transparent]=#[backcolor=transparent] update test [backcolor=transparent]set[backcolor=transparent] info[backcolor=transparent]=[backcolor=transparent]tmp[backcolor=transparent].[backcolor=transparent]info [backcolor=transparent]from[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]values [backcolor=transparent]([backcolor=transparent]1[backcolor=transparent],[backcolor=transparent]'new1'[backcolor=transparent]),([backcolor=transparent]2[backcolor=transparent],[backcolor=transparent]'new2'[backcolor=transparent]),([backcolor=transparent]6[backcolor=transparent],[backcolor=transparent]'new6'[backcolor=transparent]))[backcolor=transparent] [backcolor=transparent]as[backcolor=transparent] tmp [backcolor=transparent]([backcolor=transparent]id[backcolor=transparent],[backcolor=transparent]info[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]where[backcolor=transparent] test[backcolor=transparent].[backcolor=transparent]id[backcolor=transparent]=[backcolor=transparent]tmp[backcolor=transparent].[backcolor=transparent]id[backcolor=transparent];[backcolor=transparent]  
2. [backcolor=transparent]UPDATE [backcolor=transparent]3[backcolor=transparent]  
3. [backcolor=transparent]test03[backcolor=transparent]=#[backcolor=transparent] [backcolor=transparent]select[backcolor=transparent] [backcolor=transparent]*[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] test[backcolor=transparent];[backcolor=transparent]  
4. [backcolor=transparent] id [backcolor=transparent]|[backcolor=transparent]     info     [backcolor=transparent]|[backcolor=transparent]          crt_time            
5. [backcolor=transparent]----+--------------+----------------------------[backcolor=transparent]  
6. [backcolor=transparent]  [backcolor=transparent]3[backcolor=transparent] [backcolor=transparent]|[backcolor=transparent] hello        [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]2017[backcolor=transparent]-[backcolor=transparent]04[backcolor=transparent]-[backcolor=transparent]24[backcolor=transparent] [backcolor=transparent]15[backcolor=transparent]:[backcolor=transparent]31[backcolor=transparent]:[backcolor=transparent]49.14291[backcolor=transparent]  
7. [backcolor=transparent]  [backcolor=transparent]4[backcolor=transparent] [backcolor=transparent]|[backcolor=transparent] digoal0123   [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]2017[backcolor=transparent]-[backcolor=transparent]04[backcolor=transparent]-[backcolor=transparent]24[backcolor=transparent] [backcolor=transparent]15[backcolor=transparent]:[backcolor=transparent]42[backcolor=transparent]:[backcolor=transparent]50.912887[backcolor=transparent]  
8. [backcolor=transparent]  [backcolor=transparent]5[backcolor=transparent] [backcolor=transparent]|[backcolor=transparent] hello digoal [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]2017[backcolor=transparent]-[backcolor=transparent]04[backcolor=transparent]-[backcolor=transparent]24[backcolor=transparent] [backcolor=transparent]15[backcolor=transparent]:[backcolor=transparent]57[backcolor=transparent]:[backcolor=transparent]29.622045[backcolor=transparent]  
9. [backcolor=transparent]  [backcolor=transparent]1[backcolor=transparent] [backcolor=transparent]|[backcolor=transparent] new1         [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]2017[backcolor=transparent]-[backcolor=transparent]04[backcolor=transparent]-[backcolor=transparent]24[backcolor=transparent] [backcolor=transparent]15[backcolor=transparent]:[backcolor=transparent]58[backcolor=transparent]:[backcolor=transparent]55.610072[backcolor=transparent]  
10. [backcolor=transparent]  [backcolor=transparent]2[backcolor=transparent] [backcolor=transparent]|[backcolor=transparent] new2         [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]2017[backcolor=transparent]-[backcolor=transparent]04[backcolor=transparent]-[backcolor=transparent]24[backcolor=transparent] [backcolor=transparent]15[backcolor=transparent]:[backcolor=transparent]28[backcolor=transparent]:[backcolor=transparent]20.37392[backcolor=transparent]  
11. [backcolor=transparent]  [backcolor=transparent]6[backcolor=transparent] [backcolor=transparent]|[backcolor=transparent] new6         [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]2017[backcolor=transparent]-[backcolor=transparent]04[backcolor=transparent]-[backcolor=transparent]24[backcolor=transparent] [backcolor=transparent]15[backcolor=transparent]:[backcolor=transparent]59[backcolor=transparent]:[backcolor=transparent]12.265915[backcolor=transparent]  
12. [backcolor=transparent]([backcolor=transparent]6[backcolor=transparent] rows[backcolor=transparent])

1. [backcolor=transparent]test03[backcolor=transparent]=#[backcolor=transparent] [backcolor=transparent]delete[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] test [backcolor=transparent]using[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]values [backcolor=transparent]([backcolor=transparent]3[backcolor=transparent]),([backcolor=transparent]4[backcolor=transparent]),([backcolor=transparent]5[backcolor=transparent]))[backcolor=transparent] [backcolor=transparent]as[backcolor=transparent] tmp[backcolor=transparent]([backcolor=transparent]id[backcolor=transparent])[backcolor=transparent] [backcolor=transparent]where[backcolor=transparent] test[backcolor=transparent].[backcolor=transparent]id[backcolor=transparent]=[backcolor=transparent]tmp[backcolor=transparent].[backcolor=transparent]id[backcolor=transparent];[backcolor=transparent]  
2. [backcolor=transparent]DELETE [backcolor=transparent]3[backcolor=transparent]  
3. [backcolor=transparent]test03[backcolor=transparent]=#[backcolor=transparent] [backcolor=transparent]select[backcolor=transparent] [backcolor=transparent]*[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] test[backcolor=transparent];[backcolor=transparent]  
4. [backcolor=transparent] id [backcolor=transparent]|[backcolor=transparent]  info   [backcolor=transparent]|[backcolor=transparent]          crt_time            
5. [backcolor=transparent]----+---------+----------------------------[backcolor=transparent]  
6. [backcolor=transparent]  [backcolor=transparent]1[backcolor=transparent] [backcolor=transparent]|[backcolor=transparent] new1    [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]2017[backcolor=transparent]-[backcolor=transparent]04[backcolor=transparent]-[backcolor=transparent]24[backcolor=transparent] [backcolor=transparent]15[backcolor=transparent]:[backcolor=transparent]58[backcolor=transparent]:[backcolor=transparent]55.610072[backcolor=transparent]  
7. [backcolor=transparent]  [backcolor=transparent]2[backcolor=transparent] [backcolor=transparent]|[backcolor=transparent] new2    [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]2017[backcolor=transparent]-[backcolor=transparent]04[backcolor=transparent]-[backcolor=transparent]24[backcolor=transparent] [backcolor=transparent]15[backcolor=transparent]:[backcolor=transparent]28[backcolor=transparent]:[backcolor=transparent]20.37392[backcolor=transparent]  
8. [backcolor=transparent]  [backcolor=transparent]6[backcolor=transparent] [backcolor=transparent]|[backcolor=transparent] new6    [backcolor=transparent]|[backcolor=transparent] [backcolor=transparent]2017[backcolor=transparent]-[backcolor=transparent]04[backcolor=transparent]-[backcolor=transparent]24[backcolor=transparent] [backcolor=transparent]15[backcolor=transparent]:[backcolor=transparent]59[backcolor=transparent]:[backcolor=transparent]12.265915

1. [backcolor=transparent]test03[backcolor=transparent]=#[backcolor=transparent] [backcolor=transparent]set[backcolor=transparent] lock_timeout [backcolor=transparent]=[backcolor=transparent] [backcolor=transparent]'1s'[backcolor=transparent];
2. [backcolor=transparent]SET
3. [backcolor=transparent]test03[backcolor=transparent]=#[backcolor=transparent] truncate test[backcolor=transparent];[backcolor=transparent]  
4. [backcolor=transparent]TRUNCATE TABLE  
5. [backcolor=transparent]test03[backcolor=transparent]=#[backcolor=transparent] [backcolor=transparent]select[backcolor=transparent] [backcolor=transparent]*[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] test[backcolor=transparent];[backcolor=transparent]  
6. [backcolor=transparent] id [backcolor=transparent]|[backcolor=transparent] info [backcolor=transparent]|[backcolor=transparent] crt_time  
7. [backcolor=transparent]----+------+----------[backcolor=transparent]  
8. [backcolor=transparent]([backcolor=transparent]0[backcolor=transparent] rows[backcolor=transparent])