VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

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)