全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
云数据库 RDS 版

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

更新时间:2017-06-07 13:26:11

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

批量插入数据

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

  • 使用 insert into ... select 的方法。

    1. postgres=# insert into tbl1 (id, info ,crt_time) select generate_series(1,10000),'test',now();
    2. INSERT 0 10000
    3. postgres=# select count(*) from tbl1;
    4. count
    5. -------
    6. 10001
    7. (1 row)
  • 使用 values(),(),...(); 的方法。

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

    1. postgres=# begin;
    2. BEGIN
    3. postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now());
    4. INSERT 0 1
    5. postgres=# insert into tbl1 (id,info,crt_time) values (2,'test2',now());
    6. INSERT 0 1
    7. postgres=# insert into tbl1 (id,info,crt_time) values (3,'test3',now());
    8. INSERT 0 1
    9. postgres=# end;
    10. COMMIT
  • 使用 copy 协议。copy 协议与 insert 协议不一样,更加精简,插入效率高。

    1. test03=# \d test
    2. Table "public.test"
    3. Column | Type | Modifiers
    4. ----------+-----------------------------+-----------
    5. id | integer | not null
    6. info | text |
    7. crt_time | timestamp without time zone |
    8. Indexes:
    9. "test_pkey" PRIMARY KEY, btree (id)
    10. test03=# copy test from stdin;
    11. Enter data to be copied followed by a newline.
    12. End with a backslash and a period on a line by itself.
    13. >> 8 'test' '2017-01-01'
    14. >> 9 'test9' '2017-02-02'
    15. >> \.
    16. COPY 2

    说明:不同的语言驱动,对应的 COPY 接口不同,请参见如下文档。

批量更新数据

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

批量删除数据

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

如果要清除全表,建议您使用 truncate。

  1. test03=# set lock_timeout = '1s';
  2. SET
  3. test03=# truncate test;
  4. TRUNCATE TABLE
  5. test03=# select * from test;
  6. id | info | crt_time
  7. ----+------+----------
  8. (0 rows)
本文导读目录