OceanBase INSERT
描述
该语句用于添加一个或多个记录到表中。
格式
INSERT [hint_options] { single_table_insert | multi_table_insert }
single_table_insert:
{ INTO insert_table_clause opt_nologging '(' column_list ')' values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
| INTO insert_table_clause opt_nologging '(' ')' values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
| INTO insert_table_clause opt_nologging values_clause [{ RETURNING | RETURN } returning_exprs [into_clause]]
}
opt_nologging: { NOLOGGING | /*EMPTY*/ }
returning_exprs:
projection [, ...]
insert_into_clause:
{ INTO into_var_list | BULK COLLECT INTO into_var_list}
into_var_list:
{ USER_VARIABLE | ref_name } [, ...]
values_clause:
VALUES ({ expr | DEFAULT } [, { expr | DEFAULT } ]... )
multi_table_insert:
{ ALL { insert_into_clause [ values_clause ] [error_logging_clause] }
| conditional_insert_clause
} subquery
conditional_insert_clause:
[ ALL | FIRST ]
WHEN condition
THEN insert_into_clause
[ values_clause ]
[ error_logging_clause ]
[ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
[ WHEN condition
THEN insert_into_clause
[ values_clause ]
[ error_logging_clause ]
[ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
]...
[ ELSE insert_into_clause
[ values_clause ]
[ error_logging_clause ]
[ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
]
error_logging_clause:
LOG ERRORS [ INTO [schema.] table ] [ (simple_expression) ] [ REJECT LIMIT { integer | UNLIMITED } ]参数解释
|
参数 |
描述 |
|---|---|
|
hint_options |
指定 Hint 选项。 |
|
single_table_insert |
单表插入。 |
|
insert_table_clause |
指定的插入的表，可以是基表、可更新视图、特殊子查询。 |
|
opt_nologging |
尽量减少插入时的日志信息。 |
|
column_list |
指定要插入的列名。 |
|
returning_exprs |
返回插入数据之后的投影列。 |
|
insert_into_clause |
将插入数据之后的列值插入到指定列表中。 |
|
multi_table_insert |
多表插入。 |
|
conditional_insert_clause |
带条件的多表插入。
|
注意
特殊子查询指的类似于可更新视图对应的子查询，这类子查询不应该包含复杂的算子（比如 group by/distinct/window function 等）
示例
示例表及数据基于以下定义：
obclient>CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT,c3 INT);
Query OK, 0 rows affected (0.10 sec)
obclient> SELECT * FROM t;
Empty set (0.02 sec)-
单表插入：向表 t 中插入一行数据。
obclient> INSERT INTO t VALUES(1,2,3);
Query OK, 1 row affected (0.00 sec)
obclient> SELECT * FROM t;
+----+------+------+
| C1 | C2 | C3 |
+----+------+------+
| 1 | 2 | 3 |
+----+------+------+
1 row in set (0.00 sec)-
单表插入：直接向子查询中插入数据
obclient>INSERT INTO (SELECT * FROM t) VALUES(1,2,3);
Query OK, 1 row affected (0.00 sec)
obclient> SELECT * FROM t;
+----+------+------+
| C1 | C2 | C3 |
+----+------+------+
| 1 | 2 | 3 |
+----+------+------+
1 row in set (0.01 sec)-
单表插入：包含
RETURNING子句。
obclient>INSERT INTO t VALUES(1,2,3) RETURNING c1;
+----+
| C1 |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
obclient>SELECT * FROM t;
+----+------+------+
| C1 | C2 | C3 |
+----+------+------+
| 1 | 2 | 3 |
+----+------+------+
1 row in set (0.00 sec)-
普通的多表插入：当表 t 中有至少一行数据时，向表 t1 插入一行数据 (1,1,1)，向表 t2 插入一行数据 (2,2,2)。
obclient>INSERT ALL INTO t1 VALUES(1,1,1) INTO t2 VALUES(2,2,2) SELECT * FROM t
WHERE ROWNUM< 2;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
obclient>SELECT * FROM t1;
+----+------+------+
| C1 | C2 | C3 |
+----+------+------+
| 1 | 1 | 1 |
+----+------+------+
1 row in set (0.02 sec)
obclient>SELECT * FROM t2;
+----+------+------+
| C1 | C2 | C3 |
+----+------+------+
| 2 | 2 | 2 |
+----+------+------+
1 row in set (0.01 sec)-
带条件的多表插入：使用
INSERT ALL，当表 t 中 c2 的值大于 1 时，向表 t1 中插入数据 (1,1,1)；当表 t 中 c3 的值大于 1 时，向表 t2 中插入数据 (2,2,2)；如果都不满足，则向表 t1 中插入数据 (3,3,3)。
obclient>DELETE FROM (SELECT * FROM t);
Query OK, 4 rows affected (0.04 sec)
obclient>INSERT INTO t VALUES(1,2,3);
Query OK, 1 row affected (0.00 sec)
obclient>INSERT ALL
WHEN c2 > 1 THEN INTO t1 VALUES(1,1,1)
WHEN c3 > 1 THEN INTO t2 VALUES(2,2,2)
ELSE INTO t1 VALUES(3,3,3) SELECT c2,c3 FROM t;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
obclient>SELECT * FROM t1;
+----+------+------+
| C1 | C2 | C3 |
+----+------+------+
| 1 | 1 | 1 |
+----+------+------+
1 row in set (0.00 sec)
obclient>SELECT * FROM t2;
+----+------+------+
| C1 | C2 | C3 |
+----+------+------+
| 2 | 2 | 2 |
+----+------+------+
1 row in set (0.00 sec)-
带条件的多表插入：使用
INSERT FIRST，当表 t 中 c2 的值大于 1 时，向表 t1 中插入数据 (1,1,1)，向表 t2 中插入数据 (4,4,4)；当表 t 中 c3 的值大于 1 时，向表 t2 中插入数据 (2,2,2)；如果都不满足，则向表 t1 中插入数据 (3,3,3)。
obclient>DELETE FROM (SELECT * FROM t);
Query OK, 4 rows affected (0.04 sec)
obclient>INSERT INTO t VALUES(1,2,3);
Query OK, 1 row affected (0.00 sec)
obclient>INSERT ALL
WHEN c2 > 1 THEN INTO t1 VALUES(1,1,1) INTO t2 VALUES(4,4,4)
when c3 > 1 THEN INTO t2 VALUES(2,2,2)
ELSE INTO t1 VALUES(3,3,3) SELECT c2,c3 FROM t;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
obclient>SELECT * FROM t1;
+----+------+------+
| C1 | C2 | C3 |
+----+------+------+
| 1 | 1 | 1 |
+----+------+------+
1 row in set (0.00 sec)
obclient>SELECT * FROM t2;
+------+------+------+
| C1 | C2 | C3 |
+------+------+------+
| 4 | 4 | 4 |
| 2 | 2 | 2 |
+------+------+------+
2 rows in set (0.00 sec)