INSERT

功能描述

INSERT语句用于向表中添加一行或多行数据。

本文档只介绍INSERT语句兼容MySQL的特性，原Vastbase的INSERT语法未做删除和修改，详见INSERT。

支持在INSERT语句中使用IGNORE选项。
支持使用SET进行赋值。
支持INSERT INTO语法使用value插入数据。
支持INSERT...ON DUPLICATE KEY UPDATE语句。
支持在表达式中引用字段名。

注意事项

该功能仅在数据库兼容模式为MySQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='B'）。

语法格式

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

本文档仅列出部分语法，更多INSERT语法请参考INSERT。

参数说明

IGNORE

使用该参数，如果发生了主键或唯一索引冲突，则会忽略错误并发出一个WARNING。

用户在执行INSERT语句的时候，如果发生主键或唯一索引冲突，SQL语句是会引发ERROR报错，如果使用IGNORE选项，则忽略在执行SQL语句时发生的可忽略的错误，将Error降级为Warning，且继续语句的执行，不会影响其他数据的操作。能使Error降级的场景有：

1、违反非空约束时：

若执行的SQL语句违反了表的非空约束，使用IGNORE选项可将Error降级为Warning，并根据GUC参数sql_ignore_strategy的值采用以下策略的一种继续执行：
- sql_ignore_strategy为ignore_null时，忽略违反非空约束的行的INSERT操作，并继续执行剩余数据操作。
- sql_ignore_strategy为overwrite_null时，将违反约束的null值覆写为目标类型的默认值，并继续执行剩余数据操作。
2、违反唯一约束时：

若执行的SQL语句违反了表的唯一约束，使用IGNORE选项可将Error降级为Warning，忽略违反约束的行的INSERT操作，并继续执行剩余数据操作。

3、分区表无法匹配到合法分区时：

在对分区表进行INSERT操作时，若某行数据无法匹配到表格的合法分区，使用IGNORE选项可将Error降级为Warning，忽略该行操作，并继续执行剩余数据操作。

4、插入值向目标列类型转换失败时：

执行INSERT语句时，若发现新值与目标列类型不匹配，使用IGNORE选项可将Error降级为Warning，并根据新值与目标列的具体类型采取以下策略的一种继续执行：
- 当新值类型与列类型同为数值类型时：若新值在列类型的范围内，则直接进行插入；若新值在列类型范围外，则以列类型的最大/最小值替代。
- 当新值类型与列类型同为字符串类型时：若新值长度在列类型限定范围内，则以直接进行插入；若新值长度在列类型的限定范围外，则保留列类型长度限制的前n个字符。
若遇到新值类型与列类型不可转换时：插入列类型的默认值。

例如，如果没有IGNORE，表中现有主键或唯一索引重复将会导致重复键错误，并且语句将中止。使用IGNORE选项，该行将被丢弃并且不会发生错误，忽略的错误会生成警告。

IGNORE关键字不支持列存，无法在列存表中生效。

详细用法请参考示例1。
set_clause_values

支持使用SET进行赋值，语法如下：
```
insert [into] table_name set column_name = value, column_name = value,...
```
set_clause_values是指set column_name = value，多个列插入值用逗号分隔，是insert into 的一种扩展语法。为防止insert into时字段顺序与值顺序混乱造成写入错误。
- 不支持向指定的分区插入数据。
- 不支持插入的行/列取别名。
- 不支持调度优先级。
- 支持set后面的表达式右值带字段名。
详细用法请参考示例2。
ON DUPLICATE KEY UPDATE

使用该选项，当所操作的表只有一个唯一索引（包括主键索引）时，insert的数据如果引起唯一索引的冲突，即唯一值重复了，则不会执行insert操作，而是执行后面的update操作。当主键或唯一性索引有多个字段时，满足多个row需要做update时，会update第一行记录，并且不会报错。
- INSERT ON DUPLICATE KEY UPDATE不支持WITH及WITH RECURSIVE子句。
- 不支持列存表、外表、内存表。
- 支持字段重复。
详细用法请参考示例3，示例4。
INTO

Vastbase在MySQL兼容模式下支持INSERT INTO语法使用value插入数据。

详细用法请参考示例5。

示例

示例1： IGNORE选项的使用。

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

CREATE TABLE table_name(id int NOT NULL,name varchar(50) DEFAULT NULL,age int DEFAULT NULL,PRIMARY KEY (id));
INSERT INTO table_name VALUES(1,'tom', 20);

2、再次插入一条数据，id为1，不使用IGNORE选项（主键冲突）。

INSERT INTO table_name VALUES(1,'Bill',21);

返回结果如下，报主键冲突的错误：

ERROR:  duplicate key value violates unique constraint "table_name_pkey"
DETAIL:  Key (id)=(1) already exists.

3、加上IGNORE选项之后插入数据。

INSERT IGNORE INTO table_name VALUES(1,'Bill',21);

返回结果如下，会有一个WARNING警告：

WARNING:  duplicate key value violates unique constraint in table "table_name"
INSERT 0 0

4、插入多行数据。

INSERT IGNORE INTO table_name VALUES(1,'Bill',21),(2,'cim',21);

返回结果为：

WARNING:  duplicate key value violates unique constraint in table "table_name"
INSERT 0 1

5、查询表，发现插入的数据(1,'Bill',21)被忽略了，(2,'cim',21)数据成功插入。

SELECT * FROM table_name;

返回结果为：

 id | name | age
----+------+-----
  1 | tom  |  20
  2 | cim  |  21
(2 rows)

示例2： 使用SET赋值。

1、创建测试表。

CREATE TABLE setTest(id INT PRIMARY KEY,name VARCHAR(128) NOT NULL,addr VARCHAR(128) DEFAULT 'ABC');

2、使用SET插入数据。

INSERT INTO setTest SET id=1,name='张三',addr='和平路1号';
INSERT INTO setTest SET id=2,name='李四',addr=default;

3、查询插入结果。

SELECT * FROM setTest;

返回结果为：

 id | name |   addr    
----+------+-----------
  1 | 张三 | 和平路1号
  2 | 李四 | ABC
(2 rows)

示例3： 多个row做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 * fr

结果显示如下，插入的数据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)

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

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)

示例5： 支持INSERT INTO语法使用value插入数据。

1、创建测试表。

CREATE TABLE tb_1117689(col1 int ,col2 int default 3);

2、插入数据。

INSERT INTO tb_1117689 VALUE(1,1),(2,2)

3、查询数据。

SELECT * FROM tb_1117689 ORDER BY 1,2;

返回结果为：

 col1 | col2
------+------
    1 |    1
    2 |    2
(2 rows)

示例6： 在表达式中引用字段名。

1、创建测试表。

CREATE TABLE test_order_t(n1 DATE DEFAULT '2000-01-01', n2 INT DEFAULT 1, s DATE);

2、INSERT语句中表达式引用字段。

INSERT INTO test_order_t(s,n1,n2) VALUES(n1-n2,n1+1,n2);

3、查询插入结果。

SELECT * FROM test_order_t;

返回结果为：

     n1     | n2 |     s
------------+----+------------
 2000-01-02 |  1 | 1999-12-31
(1 row)

VastbaseG100

INSERT

功能描述

注意事项

语法格式

参数说明

示例

弹框头部