postgresql数据库使用——数据库不重复插入表的几种操作

程序员文章站 2022-06-02 14:06:03

...

1、我们在维护数据库的时候经常会遇到的需要插入数据的问题，通常可能会需要从一个表中插入一段数据到另一个表里面，使用纯sql的方法是比较合理的，

为了减少数据量，我们不希望重复数据插入到一张表中，之前使用的都是查询出相应的数据，然后与被插入的数据进行比较，判断是否插入数据库。

后面想到一种新方式，不用2次数据库交互----使用exisits

insert into xx
select #{x1},#{x2}
where not EXISTS (select 1 from xx t where t.x1 = #{x1} and t.x2 = #{x2})

这样就可以不重复插入数据了

在mysql中，应对这个问题，有三种解决的办法：

mysql在存在主键冲突或者唯一键冲突的情况下，根据插入策略不同，一般有以下三种避免方法。
1、insert ignore
2、replace into
3、insert on duplicate key update

注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用以上三个语句没有意义，与使用单纯的INSERT INTO相同。

解释一下，意思其实很直接，insert ignore的意思是，忽略数据库中已经存在的数据（根据主键或者索引进行）

此外，从官方来说，其实有一种更好的处理方法：

链接如下所示：postgresql – 忽略重复插入的最佳方法？

这里主要讲一下他的实现思路是什么样子的

方法一：

创建一个捕获唯一约束违规的事务,不执行任何操作：

BEGIN
    INSERT INTO db_table (tbl_column) VALUES (v_tbl_column);
  EXCEPTION WHEN unique_violation THEN
    -- Ignore duplicate inserts.
  END;

方法二：

创建规则以忽略给定表上的重复项：

CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
    ON INSERT TO db_table
   WHERE (EXISTS ( SELECT 1
           FROM db_table
          WHERE db_table.tbl_column = NEW.tbl_column)) DO INSTEAD NOTHING;

此外，根据Postgresql 插入或者更新操作upsert一文中提到的，在Postgresql9.5之后，提供了原子的upsert语法，不存在则插入，发生冲突可以update

Insert语法：官方文档

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
   [ OVERRIDING { SYSTEM | USER} VALUE ]
   { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
   [ ON CONFLICT [ conflict_target ] conflict_action ]
   [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

   ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
   ON CONSTRAINT constraint_name

and conflict_action is one of:

   DO NOTHING
   DO UPDATE SET { column_name = { expression | DEFAULT } |
                   ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                   ( column_name [, ...] ) = ( sub-SELECT )
                 } [, ...]
             [ WHERE condition ]

index_column_name

The name of a table_name column. Used to infer arbiter indexes. Follows CREATE INDEX format. SELECT privilege on index_column_name is required.

index_expression

Similar to index_column_name, but used to infer expressions on table_name columns appearing within index definitions (not simple columns). Follows CREATE INDEX format. SELECT privilege on any column appearing within index_expression is required.

## 使用示例

创建表

CREATE TABLE "test"."upsert_test" ( "id" int4 NOT NULL, "name" varchar(255) COLLATE "pg_catalog"."default" ) ;

当主键id冲突时，更新其他字段

INSERT INTO test.upsert_test(id, "name") VALUES(1, 'm'),(2, 'n'),(4, 'c') ON conflict(id) DO UPDATE SET "name" = excluded.name;

did 冲突的主键
EXCLUDED 代指要插入的记录

当主键或者unique key发生冲突时，什么都不做

INSERT INTO test.upsert_test(id, "name")
VALUES(1, 'm'),(2, 'n'),(4, 'c')
ON conflict(id)  DO NOTHING;

最后给定几个postgresql得到学习网站：

阮一峰 postgresql新手入门

官方中文网站

Postgresql插入更新操作