PG中文社区 /
mdi-home
首页 社区新闻 中文文档 加入ACE {{ item.text }} 登录
mdi-home 首页 mdi-chat-processing 社区新闻 mdi-book-open-variant 中文文档 mdi-account-multiple-check 加入ACE mdi-file-multiple-outline 相关资料 mdi-blank {{item.text}} mdi-exit-to-app 退出账号
在PostgreSQL中实现update | delete limit

原作者:digoal/德哥  创作时间:2016-10-15 22:50:38+08  
doudou586 发布于2016-10-15 22:50:38           评论: 3   浏览: 11636   顶: 2212  踩: 2263 

在PostgreSQL中实现update | delete limit

2016 Postgres大象会官方报名通道: 点此报名



作者: digoal

日期: 2016-08-27

背景

使用MySQL的用户可能会比较熟悉这样的用法,更新或删除时可以指定限制更新或删除多少条记录。 达到限定的记录数后不再继续进行下去,而是返回。

delete from tbl where xxx limit 100;
..
update tbl set xxx=xxx where xxx limit 100;

目前PostgreSQL没有类似的语法,但是可以通过其他手段来达到同样的效果。

with语法实现

创建测试表

postgres=# create table t(id int primary key, info text);
CREATE TABLE
postgres=# insert into t select generate_series(1,1000000);
INSERT 0 1000000

update | delete limit 用法如下

postgres=# with t1 as (select id from t where id between 1 and 1000 limit 10) 
                  delete from t where id in (select * from t1);
DELETE 10

postgres=# with t1 as (select id from t where id between 1 and 1000 limit 10) 
                   update t set info='new' where id in (select * from t1);
UPDATE 10

postgres=# explain with t1 as (select id from t where id between 1 and 1000 limit 10) 
postgres-#                    update t set info='new' where id in (select * from t1);
                                      QUERY PLAN     
------------------------------------------------------------------------------------
 Update on t  (cost=4.89..12.93 rows=1 width=38)
   CTE t1
     ->  Limit  (cost=0.42..4.44 rows=1 width=4)
           ->  Index Only Scan using t_pkey on t t_1  (cost=0.42..4.44 rows=1 width=4)
                 Index Cond: ((id >= 1) AND (id <= 1000))
   ->  Nested Loop  (cost=0.45..8.48 rows=1 width=38)
         ->  HashAggregate  (cost=0.02..0.03 rows=1 width=32)
               Group Key: t1.id
               ->  CTE Scan on t1  (cost=0.00..0.02 rows=1 width=32)
         ->  Index Scan using t_pkey on t  (cost=0.42..8.44 rows=1 width=10)
               Index Cond: (id = t1.id)
(11 rows)
使用with模拟必须有PK或者非空UK,否则需要用行号,但是行号来扫就慢了,没走tid scan方法。
postgres=#  with t1 as (select ctid from t where id between 1 and 100000 limit 10) 
              delete from t where ctid in (select ctid from t1);
DELETE 10

postgres=#  with t1 as (select ctid from t where id between 1 and 100000 limit 10) 
            update t set info='new' where ctid in (select ctid from t1);
UPDATE 10

postgres=# explain with t1 as (select ctid from t where id between 1 and 100000 limit 10) 
               update t set info='new' where ctid in (select ctid from t1);
                                       QUERY PLAN                                  
------------------------------------------------------------------------------------
 Update on t  (cost=129834.64..134784.79 rows=10 width=40)
   CTE t1
     ->  Limit  (cost=0.42..0.78 rows=10 width=6)
           ->  Index Scan using t_pkey on t t_1  (cost=0.42..3178.93 rows=89225 width=6)
                 Index Cond: ((id >= 1) AND (id <= 100000))
   ->  Merge Semi Join  (cost=129833.86..134784.01 rows=10 width=40)
         Merge Cond: (t.ctid = t1.ctid)
         ->  Sort  (cost=129833.49..132308.49 rows=990000 width=10)
               Sort Key: t.ctid
               ->  Seq Scan on t  (cost=0.00..14325.00 rows=990000 width=10)  --这个NODE非常慢
         ->  Sort  (cost=0.37..0.39 rows=10 width=36)
               Sort Key: t1.ctid
               ->  CTE Scan on t1  (cost=0.00..0.20 rows=10 width=36)
(13 rows)

这应该也是可以优化的地方,已反馈给社区。

函数+游标实现

do language plpgsql $$
declare 
  rowvar record;
 cur cursor for select * from t where id between 1 and 1000000 limit 10;
begin
  open cur; 
  loop 
    fetch cur into rowvar;
    if found then 
      update t set info='new' where current of cur returning * into rowvar; 
      raise notice '%', rowvar;
    else
      return;
    end if;
  end loop; 
  close cur;
end;
$$;

NOTICE:  00000: (10011,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10012,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10013,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10014,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10015,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10016,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10017,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10018,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10019,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
NOTICE:  00000: (10020,new)
LOCATION:  exec_stmt_raise, pl_exec.c:3216
DO

期待阿里云PostgreSQL扩展支持这个MySQL语法。


2016 Postgres大象会官方报名通道:http://www.huodongxing.com/event/8352217821400

扫描报名


/images/news/2016/pgconf2016_qrcode.jpg


/images/news/2016/pgconf2016_plus_logo_cn.png


评论:3   浏览: 11636                   顶: 2212  踩: 2263 

请在登录后发表评论,否则无法保存。

1# __ xcvxcvsdf 回答于 2024-10-24 14:53:33+08
http://jinqiang.ahtcbmw.cn/qgqzw/ http://js.sytcxxw.cn/bazp/ http://js.sytcxxw.cn/jbq/ http://tuiguang.hntcxxw.cn/jslyg/ https://fenlei.tiancebbs.cn/csjz/ https://jiangyuan.tiancebbs.cn/ https://qiemo.tiancebbs.cn/ http://cf.lstcxxw.cn/haerbin/ https://honglan.tiancebbs.cn/yu-lin/ http://jinqiang.ahtcbmw.cn/yingtan/ http://fuyang.tjtcbmw.cn/qhhd/ http://yuanbang.tjtcbmw.cn/nanning/ http://yz.cqtcxxw.cn/nxzw/ http://jinqiang.ahtcbmw.cn/bsfw/ http://taiying.njtcbmw.cn/yangquan/ http://tuiguang.hntcxxw.cn/shuangyashan/ https://gongfu.tiancebbs.cn/

2# __ xcvxcvsdf 回答于 2024-10-16 08:35:03+08
https://aihuishou.tiancebbs.cn/sh/3554.html https://taicang.tiancebbs.cn/hjzl/465136.html https://www.tiancebbs.cn/ershoufang/466956.html https://hz.tiancebbs.cn/qths/470502.html https://zulin.tiancebbs.cn/sh/153.html https://changshushi.tiancebbs.cn/hjzl/462862.html https://aihuishou.tiancebbs.cn/sh/3781.html https://zulin.tiancebbs.cn/sh/4669.html https://su.tiancebbs.cn/hjzl/462201.html https://zulin.tiancebbs.cn/sh/65.html https://zhanjiang.tiancebbs.cn/qths/450422.html https://zulin.tiancebbs.cn/sh/2430.html https://aihuishou.tiancebbs.cn/sh/4813.html https://changshushi.tiancebbs.cn/hjzl/464091.html https://th.tiancebbs.cn/qths/452850.html https://su.tiancebbs.cn/hjzl/459427.html https://zulin.tiancebbs.cn/sh/4845.html

3# __ xiaowu 回答于 2024-04-22 09:24:23+08
相聚一起快乐时光句子:https://www.nanss.com/wenan/1959.html 白天不知黑夜的黑说说:https://www.nanss.com/wenan/1536.html 夸孩子们晨读的句子:https://www.nanss.com/yulu/1576.html 表达自己懂而不说的说说:https://www.nanss.com/wenan/1659.html qq群好听的专属头衔:https://www.nanss.com/mingcheng/1534.html 哄好生气的闺蜜:https://www.nanss.com/yulu/1860.html 余额不足的心情说说:https://www.nanss.com/wenan/1650.html 儿的生日娘的苦日感恩句子:https://www.nanss.com/yulu/1635.html 恳请别人帮忙的话语:https://www.nanss.com/yulu/1754.html 圣经中祝福的金句:https://www.nanss.com/shenghuo/1677.html 不好惹的网名:https://www.nanss.com/mingcheng/1917.html 已婚孤单无聊的说说心情句子:https://www.nanss.com/wenan/1886.html 诚信朋友圈诚信说说:https://www.nanss.com/wenan/1527.html dnf名字大全:https://www.nanss.com/mingcheng/1814.html 没钱的说说伤感句子:https://www.nanss.com/wenan/1938.html 一句话感谢借钱的人:https://www.nanss.com/yulu/1509.html 临走前告别城市的句子:https://www.nanss.com/yulu/1842.html 感恩爸妈暖心短句:https://www.nanss.com/yulu/1978.html 提醒他人别太狂的句子:https://www.nanss.com/yulu/1949.html 聚在一起开心的群名字:https://www.nanss.com/mingcheng/1565.html 骑行的快乐说说:https://www.nanss.com/wenan/1728.html 感谢工作人员的话:https://www.nanss.com/gongzuo/1653.html 吸引人的装修朋友圈文案:https://www.nanss.com/wenan/1948.html 孙子对爷爷的悼念词:https://www.nanss.com/shenghuo/1673.html 特别宠溺的备注:https://www.nanss.com/mingcheng/1994.html 好听的战队名字:https://www.nanss.com/mingcheng/1838.html 拽姐语录:https://www.nanss.com/shenghuo/1547.html 地震暖心话语:https://www.nanss.com/yulu/1737.html 三个字的游戏名:https://www.nanss.com/mingcheng/1826.html 感叹小姑娘长成大姑娘的句子:https://www.nanss.com/yulu/1778.html



发表评论:
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
© PostgreSQL中文社区 ... (自2010年起)