人大金仓数据库KingbaseES INSERT…SET…语法介绍

金仓数据库KingbaseES INSERT… SET … 语法介绍

关键字：

KingbaseES、INSERT、MYSQL、人大金仓

INSERT … SET …

INSERT命令可以往表中插入新的一行或多行数据。通常大家熟悉的插入语法如下：

INSERT [INTO] table_name(col_name,col_name,…) VALUES(value, value,…);

有所不同的是在MYSQL数据库中支持了INSERT INTO … SET …语法，可以对待插入的单行数据进行逐项赋值。MYSQL官方文档语法如下：

INSERT [INTO] table_nameSET assignment_list[AS row_alias[(col_alias[, col_alias]…)]][ON DUPLICATE KEY UPDATE assignment_list]

在kingbaseES数据中也支持相应的操作，有所不同的是MYSQL只支持SET语句的一列一列区赋值，在KingbaseES中支持多列同时SET赋值。在KingbaseES的手册中相应的语法如下：

| INSERT INTO table_nameSET { column_name = { expression | DEFAULT } |( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |( column_name [, ...] ) = ( sub-SELECT )} [, ...][AS row_alias[col_name_alias]][ON DUPLICATE KEY UPDATE expression[RETURNING col_name_list]]; | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

可以看到在KingbaseES中支持在INSERT...SET…中多列同时采用表达式或子查询的方式赋值。下面使用测试用例对比说一下区别。

与MYSQL对比

首先使用下面的命令在MYSQL和KingbaseES数据库中建立相应的表：

Create table test_table(c1 int primary key, c2 text, c3 text);

1. INSERT…SET…逐列插入

MYSQL数据库

Insert into test_table set c1=1, c2='this is c2', c3='this is c3';Select * from test_table;

KingbaseES数据库

Insert into test_table set c1=1, c2='this is c2', c3='this is c3';Select * from test_table;

可以看出在执行单列逐项插入时，MYSQL和KingbaseES数据没有差异。

1. 多列采用表达式的方式赋值

MYSQL数据库

Insert into test_table set c1=2, (c2,c3)=('mulit_insert_c2', 'mulit_insert_c3');Select * from test_table;

KingbaseES数据库

Insert into test_table set c1=2, (c2,c3)=('mulit_insert_c2', 'mulit_insert_c3');Select * from test_table;

这里可以看出明显差异，在MYSQL数据库中并不支持这种同时对多列进行set的方式，而在KingBaseES数据库中支持这种赋值方式。这也仅仅是语法差异而已，只是MYSQL使用这种SET的方式赋值比较繁琐。

（3）子查询方式赋值

在进行进行子查询赋值之前建立一个待查询的表，并往表中插入相应数据。

Create table tt(c1 int, col2 text, col3 text);Insert into tt values(3, 'sub_select2', 'sub-select3');

MYSQL

Inser into test_table set c1=(select c1 from tt);Insert into test_table set (c1, c2, c3)=(select * from tt);Select * from test_table;

KingbaseES

Insert into test_table set (c1, c2, c3)=(select * from tt);Insert into test_table set (c1, c2, c3)=(select * from tt);

这可以看出mysql并不支持子查询的方式去set赋值，而在KingbaseES数据库中支持了子查询的方式赋值。Insert…set…子查询的赋值方式更加灵活，你可以在查询语句中使用任何select语句的，你只需要确保查询结果只有一行，数据类型和数据数量与等号前面的列相符。

子查询的INSERT…SET…

现在，来测试一下支持什么样的子查询吧。首先使用下述命令建立多个表，并插入相应数据。

create table emp1(id int primary key, grade varchar(100) default('a_sbkdg'));create table emp2(id int primary key, name_str varchar(100) default('TOM'));create table emp3(name_str varchar(100), grade varchar(100) default('a_sdad'));insert into emp2 values(1,'sdsd'), (2, default), (3, 'john');insert into emp3 values('sdsd', 'msdajnh'), ('TOM', default), ('john', '15646487'), ('TOM', '1841.684');

上述命令，建立了3个表，并往其中插入了相应的数据。首先测试最简单的查询语句

insert into emp1 set (id,grade)=(select 0, 'set subselect');

然后是where条件语句和join连接语句

insert into emp1 set (id,grade)=(select id,grade from emp2 left join emp3 on emp2.name_str=emp3.name_str where emp2.name_str = 'sdsd');

排序和limit语句

insert into emp1 set (id,grade)=(select id,grade from emp2 left join emp3 on emp2.name_str=emp3.name_str order by emp2.id limit 1 offset 1);

Group和聚集函数

insert into emp1 set (id,grade)=(select count()+1, cast(count() as varchar) FROM emp2 FULL OUTER JOIN ONLY emp3 ON emp2.name_str=emp3.name_str group by emp2.name_str having emp2.name_str='TOM' limit 1);

窗函数

insert into emp1 set (id,grade)=(select emp2.id+2,cast(count (*) OVER win as varchar) as _count FROM emp2 FULL OUTER JOIN ONLY emp3 ON emp2.name_str=emp3.name_str where emp2.name_str='TOM' WINDOW win AS(PARTITION BY emp3.name_str) limit 1);

Insert冲突语句

insert into emp1 set (id,grade)=(select id,grade from emp2 left join emp3 on emp2.name_str=emp3.name_str where emp2.name_str = 'sdsd') as prev on duplicate key update (id,grade)=(prev.id, prev.grade) returning *;

可以看出KingbaseES数据库的insert…set…支持了大部分的select的子句和insert冲突处理。下面展示相关错误。

insert into emp1 set (id,grade)=(SELECT emp2.id, emp2.name_str from emp2);--查询返回了多行数据insert into emp1 set (id,grade)=(select emp2.id from emp2);--等号前后列数不相同insert into emp1 set (id,name_str)=(SELECT emp2.id, emp2.name_str from emp2 where emp2.id=1), (sno)=(1, 15);--插入一个不存在的列
更多信息，参见https://help.kingbase.com.cn/v8/index.html