OceanBase INSERT
描述
该语句用于添加一个或多个记录到表中。
格式
INSERT [IGNORE] [INTO]
single_table_insert
[ON DUPLICATE KEY UPDATE update_asgn_list]
single_table_insert:
{dml_table_name values_clause
| dml_table_name '(' ')' values_clause
| dml_table_name '(' column_list ')' values_clause
| dml_table_name SET update_asgn_list}
dml_table_name:
tbl_name [PARTITION (partition_name,...)]
values_clause:
{{VALUES | VALUE} ({expr | DEFAULT},...) [, ...]
| select_stmt}
column_list
column_name [, ...]
update_asgn_list:
column_name = expr [, ...]
参数解释
|
参数 |
描述 |
|---|---|
|
IGNORE |
在 INSERT 语句执行过程中发生的错误将会被忽略。 |
|
column_list |
用于指定插入数据的列，同时插入多列时，用“,”隔开。 |
|
tbl_name |
指定要插入的表名。 |
|
partition_name |
插入表指定的分区名。 |
|
update_asgn_list |
赋值语句，例如：“c1 = 2” |
|
ON DUPLICATE KEY UPDATE |
指定对重复主键或唯一键的处理。如果指定了 ON DUPLICATE KEY UPDATE，当要插入的主键或唯一键有重复时，会用配置值替换待插入的值；如果不指定 ON DUPLICATE KEY UPDATE，当要插入的主键或唯一键有重复时，插入报错。 |
示例
示例表及数据基于以下定义：
obclient>create table t1(c1 int primary key, c2 int) partition by key(c1) partitions 4;
Query OK, 0 rows affected (0.16 sec)
obclient>create table t2(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.16 sec)
obclient>select * from t2;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.06 sec)- 向表 t1 中插入一行数数据。
obclient>insert into t1 values(1,1);
Query OK, 1 row affected (0.01 sec)
obclient>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.04 sec)- 向表 t1 中插入多行数据。
obclient>insert t1 values(1,1),(2,default),(2+2,3*4);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | NULL |
| 4 | 12 |
+----+------+
3 rows in set (0.02 sec)- 向表 t1 指定的 p0 分区插入单行数据。
obclient>insert into t1 partition(p0) (c1) values(5);
Query OK, 1 row affected (0.02 sec)
obclient>select * from t1 partition(p0);
+----+------+
| c1 | c2 |
+----+------+
| 5 | NULL |
+----+------+
1 row in set (0.01 sec)- 将表 t2 的查询结果作为数据插入表 t1。
obclient>insert into t1 select * from t2;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
obclient>select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.01 sec)
- 向表 t1 中插入重复主键值时利用 ON DUPLICATE KEY UPDATE 功能进行值更新
obclient>insert into t1 values(1,1),(1,2) ON DUPLICATE KEY UPDATE c1=100;
Query OK, 3 rows affected (0.01 sec)
Records: 2 Duplicates: 1 Warnings: 0
obclient>select * from t1;
+-----+------+
| c1 | c2 |
+-----+------+
| 100 | 1 |
+-----+------+
1 row in set (0.02 sec)- 对可更新视图 v 的插入值
obclient>create view v as select * from t1;
Query OK, 0 rows affected (0.07 sec)
obclient>insert into v values(1,1);
Query OK, 1 row affected (0.01 sec)
obclient>select * from v;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.02 sec)注意事项
INSERT 语句不支持直接对子查询进行插入操作，比如：insert into (select * from t1) values(1, 1);