PostgreSQL 务实应用(二/5)插入冲突

在项目中,有时会动态地按周期(如按月)封存统计数据,通常需要做这样的处理:

以按月封存为例,当月数据到达时,先需要检查该月是否有过记录,有则以更新的方式累加统计数字,无则添加一条记录。

假设我们创建以下月封存表 month_stat,字段?month_name 表示月份,字段?total_count 表示统计数字。

CREATE TABLE month_stat (month_name varchar(6), total_count int, UNIQUE (month_name));

普通处理

假设数据到达,我们用 v_month_name 表示到达数据的月份,v_count 表示本次到达的数量,则通常我们使用以下方式完成月封存数据的记录:

DO $$ DECLARE v_month_name varchar := '201904'; -- 本次数据的月份 v_count int := 3; -- 本次数据相关的数量BEGIN -- 如果月份已经存在,则更新统计,将数量累加上去 IF EXISTS (SELECT 1 FROM month_stat WHERE month_name = v_month_name FOR UPDATE) THEN UPDATE month_stat set total_count = total_count + v_count WHERE month_name = v_month_name; ELSE -- 插入月份,数量为本次数量 INSERT INTO month_stat (month_name, total_count) VALUES (v_month_name, v_count); END IF;END $$;

判断逻辑在 BEGIN 与 END 之间,先判断月份是否存在,再按分支进行更新或插入处理。

使用 ON CONFLECT

好消息是,从 postgres-9.5 起增加了插入冲突的支持:

INSERT … ON CONFLICT DO NOTHING/UPDATE

于是有了以下写法:

INSERT INTO month_stat (month_name, total_count) VALUES ('201904', 3)ON CONFLICT(month_name)DO UPDATE set total_count = month_stat.total_count + EXCLUDED.total_count;

CONFLICT 后边括号中必须是建立了唯一索引(或主键)的字段或字段集。

DO 后边可以是 NOTHING 表示冲突时忽略,什么都不做。也可以是 UPDATE,表示冲突时需要更新,本次冲突相关的数据使用 EXCLUDED 来引用。

使用 ON CONFILECT 至少有两个好处:

  • 不需要自已费心去加事务锁,因为它就是一个语句
  • 代码简洁无分支结构

至于使用 NOTHING 还是 UPDATE,以及 UPDATE 更新的内容与条件则要根据业务规则(如值变化时才更新,或存在则不更新等)具体分析。

相关文章