INSERT…ON DUPLICATE KEY UPDATE

功能描述

用户在执行INSERT...ON DUPLICATE KEY UPDATE语句时，当所操作的表只有一个唯一索引（包括主键索引）时，insert的数据如果引起唯一索引的冲突，即唯一值重复了，则不会执行insert操作，而是执行后面的update操作。但是当主键或唯一性索引有多个字段时，语句执行失败。

Vastbase G100从V2.2 Build 10(Patch No.7)版本开始，在MySQL兼容模式下，当主键或唯一性索引有多个字段时，满足多个row需要做update时，会update第一行记录，并且不会报错。

Vastbase G100从V2.2 Build 10(Patch No.10)版本开始，在MySQL兼容模式下，支持字段重复。可参见示例2。

注意事项

该特性仅在数据库兼容模式为MySQL时能够使用（即创建DB时DBCOMPATIBILITY='B'），在其他数据库兼容模式下不能使用该特性。
支持版本：
- 仅Vastbase G100 V2.2 Build 10(Patch No.7)及以后补丁版本支持当主键或唯一性索引有多个字段时，若满足多个row需要做update时会update第一行记录的特性。
- 仅Vastbase G100从V2.2 Build 10(Patch No.10)及以后补丁版本支持字段重复特性。
INSERT ON DUPLICATE KEY UPDATE不支持WITH及WITH RECURSIVE子句。
不支持列存表、外表、内存表。

语法格式

INSERT [/*+ plan_hint */] INTO table_name [partition_clause] [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES
    | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...] 
    | query }
    [ ON DUPLICATE KEY UPDATE { NOTHING | { column_name = { expression | DEFAULT } } [, ...] [ WHERE condition ] }]
    [ON CONFLICT [ conflict_target ] conflict_action]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

其中conflict_action可以是下列之一:

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

参数说明

NOTHING

用户执行INSERT...ON DUPLICATE KEY UPDATE语句，对于带有唯一约束（UNIQUE INDEX或PRIMARY KEY）的表，如果插入数据违反唯一约束，则对冲突行执行UPDATE子句完成更新，对于不带唯一约束的表，则仅执行插入。UPDATE时，若指定NOTHING则忽略此条插入。

其他参数请参考INSERT参数说明。

示例

前置步骤： 创建兼容模式为MySQL的库db_mysql，并进入。

create  database  db_mysql  dbcompatibility  'B';
\c  db_mysql

示例1 多个row需要做update时，会update第一行记录

1、创建测试表。

CREATE TABLE devices (id int,name VARCHAR(100),unique (id,name));

2、给表增加一个唯一约束。

alter table devices add constraint uk_devices unique(id);

3、插入数据。

INSERT INTO devices(id,name) VALUES(1,'Router F1'),(2,'Switch 1'),(3,'Switch 2'),(4,'Printer');

4、执行insert语句插入数据（插入数据无冲突），并查询结果。

INSERT INTO devices(id,name) VALUES(5,'Switch 3') ON DUPLICATE KEY UPDATE name = 'Switch 4';
select * from devices;

结果显示如下，插入的数据5,'Switch 3'与目标表中数据无冲突，则直接插入：

 id |   name
----+----------
 1  | Router F1
 2  | Switch 1
 3  | Switch 2
 4  | Printer
 5  | Switch 3
(5 rows)

5、执行insert语句插入数据（插入数据有冲突），并查询结果。

INSERT INTO devices(id,name) VALUES(4,'Printer') ON DUPLICATE KEY UPDATE name = 'another';
select * from devices;

结果显示如下，插入的数据4,'Printer'与目标表中数据重复，则更新原数据：

 id |   name
----+----------
 1  | Router F1
 2  | Switch 1
 3  | Switch 2
 5  | Switch 3
 4  | another
(5 rows)

示例2 插入数据时支持字段重复。

1、创建测试表并插入数据。

CREATE TABLE devices_1152303 (
id int,
name VARCHAR(100),
primary key (id,name)
);
INSERT INTO devices_1152303(id,name) VALUES(1,'Router F1'),(2,'Switch 1'),(3,'Switch 2'),(4,'Printer'),(4,'Printer2');

2、插入数据时字段重复，且与表内原有数据无冲突。

INSERT INTO devices_1152303(id,name) VALUES(5,'Switch 3') ON DUPLICATE KEY UPDATE name = 'Switch 4', name='Switch 5', name='Switch 6';
INSERT INTO devices_1152303(id,name) VALUES(4,'Switch 4') ON DUPLICATE KEY UPDATE name = 'Switch 4', name='Switch 5', name='Switch 6';
select * from devices_1152303 order by id;

数据正常插入，所插入数据的查询结果为如下：

 id |   name
----+-----------
  1 | Router F1
  2 | Switch 1
  3 | Switch 2
  4 | Printer
  4 | Printer2
  4 | Switch 4
  5 | Switch 3
(7 rows)

3、插入数据时字段重复，且与表内原有数据有冲突。

INSERT INTO devices_1152303(id,name) VALUES(4,'Printer') ON DUPLICATE KEY UPDATE name = 'Switch 4', name='Switch 5', name='Switch 6';
select * from devices_1152303 order by id;

冲突数据更新，插入的值为字符重复的最后一个取值，查询结果为如下（即第5行数据更新为4 Switch 6）：

 id |   name
----+-----------
  1 | Router F1
  2 | Switch 1
  3 | Switch 2
  4 | Printer2
  4 | Switch 6
  4 | Switch 4
  5 | Switch 3
(7 rows)

VastbaseG100

INSERT…ON DUPLICATE KEY UPDATE

功能描述

注意事项

语法格式

参数说明

示例

弹框头部