金仓数据库KingbaseES 数据插入更新操作

数据库使用过程中，经常会遇到一种场景：业务系统对数据进行dml操作，当数据库中数据不存在时，将数据做为新记录插入到表中，当数据库中数据存在时，对现有数据进行更新操作。

下面介绍KingbaseES中对上述的业务场景的应对方式。

-- 测试数据：
CREATE TABLE test(id int PRIMARY KEY ,name varchar(20));

INSERT INTO test values(1 , 'a');

1.KingbaseES 原生ON CONFLICT

用法：

INSERT INTO table_name [ [ AS ] alias ] [ ( column_name [, ...] ) ]
[ ON CONFLICT [ conflict_target ] conflict_action ]

ON CONFLICT子句为出现唯一性违背或排除约束违背错误时提供另一种可供选择的动作。对于每一个要插入的行，不管是插入进行下去还是由conflict_target 指定的一个仲裁者约束或者索引被违背，都会采取可供选择的conflict_action。 ON CONFLICT DO NOTHING简单地把避免插入行。 ON CONFLICT DO UPDATE则会 更新与要插入的行冲突的已有行。

注意，特殊的excluded表被用来引用原来要插入的值：

test=# select * from test ;
 id | name 
----+------
  1 | a
(1 行记录)

-- 存在相同的id值时，不进行操作
test=# insert into test values(1 , 'b') on conflict(id) do nothing ;
INSERT 0 0

-- 存在相同的id值时，更新数据
test=# insert into test values(1 , 'b') on conflict(id) do update set name = 'b' ;
INSERT 0 1
test=# select * from test ;
 id | name 
----+------
  1 | b
(1 行记录)

-- 存在相同的id值时，通过引用excluded方式更新数据
test=# insert into test values(1 , 'c') on conflict(id) do update set name = excluded.name ;       
INSERT 0 1
test=# select * from test ;                                                                 
 id | name 
----+------
  1 | c
(1 行记录)

2.兼容 mysql 的ON DUPLICATE KEY UPDATE方式

前置条件：KingbaseES 数据库初始化为MySQL模式。

用法：

INSERT INTO table_name[(col_name_list)]
VALUES(value_list) ON
DUPLICATE KEY
UPDATE
    EXPRESSION[RETURNING col_name_list];

INSERT ON DUPLICATE KEY UPDATE 功能和 KingbaseES 的 ON CONFLICT 相似。如果在 INSERT 语句后面指定了ON DUPLICATE KEY UPDATE,并且插入当前的元组后会导致 UNIQUE KEY 或 PRIMARY KEY 中出现了重复的值，则在出现 UNIQUE KEY 重复的已存在元组执行 UPDATE 操作；如果插入的元组与已存在元组不存在 UNIQUE KEY 重复问题，则插入新的元组。

test=# select * from test ;
 id | name 
----+------
  1 | a
(1 行记录)

-- 存在相同的id值时，更新数据
test=# insert into test values(1 , 'b') as new ON DUPLICATE KEY UPDATE name = new.name ;
INSERT 0 1
test=#  select * from test ;
 id | name 
----+------
  1 | b
(1 行记录)

3.兼容 oracle 的MERGE方式

根据连接条件对目标表执行插入或修改操作

用法：

MERGE INTO [ schema. ] { target_table } [ [ AS ] target_table_alias ]
    USING { [ schema. ] { source_table } [ [ AS ] source_table_alias ]
    ON ( condition_expression )
[ merge_update_clause ]
[ merge_insert_clause ];

merge_update_clause:
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }[, column = { expr | DEFAULT } ]...
[ where_clause ]
[ delete_clause ]

delete_clause:
[DELETE where_clause]

merge_insert_clause:
WHEN NOT MATCHED THEN
INSERT [ ( column [, column ]...) ]
VALUES ({ expr | DEFAULT }[, { expr | DEFAULT } ]...)
[ where_clause ]

使用 MERGE 语法可合并 UPDATE 和 INSERT 语句。通过 MERGE语句，根据一张表（或视图）的连接条件对另外一张表（或视图）进行查询，连接条件匹配上的执行 UPDATE（可能含有 DELETE），无法匹配的执行 INSERT。

create table test1(a int, b int);
insert into test1 values(1, 1);

create table test2(x int, y int);
insert into test2 values(1, 1);

test=# select * from test2 ;
 x | y 
---+---
 1 | 1
(1 行记录)

test=# MERGE INTO test2
test-#   USING test1
test-# ON
test-# (
test(#   test1.a = test2.x
test(# )
test-# WHEN MATCHED THEN
test-# UPDATE
test-# SET
test-#   y = y * -1
test-# WHEN NOT MATCHED THEN INSERT
test-#   VALUES(
test(#     test1.a,
test(#     test1.b
test(#   );
MERGE 1
test=#  select * from test2 ;
 x | y  
---+----
 1 | -1
(1 行记录)

test=# select * from test ;
 id | name 
----+------
  1 | a
(1 行记录)

test=# MERGE INTO test t1
test-#   USING (
test(#   SELECT
test(#     1 id,
test(#     'b' name
test(#   FROM
test(#     dual
test(# ) tmp ON
test-# (
test(#   t1.id = tmp.id
test(# ) when MATCHED THEN
test-# UPDATE
test-# SET
test-#   t1.name = tmp.name
test-# WHERE
test-#   t1.id = tmp.id
test-# WHEN NOT MATCHED THEN INSERT
test-#   VALUES(
test(#     tmp.id ,
test(#     tmp.name
test(#   ) ;
MERGE 1
test=# select * from test ;
 id | name 
----+------
  1 | b
(1 行记录)

更多信息，参见https://help.kingbase.com.cn/v8/index.html