描述

该语句用于添加一个或多个记录到表中。

格式

  1. INSERT [IGNORE] [INTO]
  2. single_table_insert
  3. [ON DUPLICATE KEY UPDATE update_asgn_list]
  4. single_table_insert:
  5. {dml_table_name values_clause
  6. | dml_table_name '(' ')' values_clause
  7. | dml_table_name '(' column_list ')' values_clause
  8. | dml_table_name SET update_asgn_list}
  9. dml_table_name:
  10. tbl_name [PARTITION (partition_name,...)]
  11. values_clause:
  12. {{VALUES | VALUE} ({expr | DEFAULT},...) [, ...]
  13. | select_stmt}
  14. column_list
  15. column_name [, ...]
  16. update_asgn_list:
  17. column_name = expr [, ...]

参数解释

参数

描述

IGNOREINSERT语句执行过程中发生的错误将会被忽略。
column_list用于指定插入数据的列,同时插入多列时,用“,”隔开。
tbl_name指定要插入的表名。
partition_name插入表指定的分区名。
update_asgn_list

赋值语句,例如:“c1 = 2”

ON DUPLICATE KEY UPDATE

指定对重复主键或唯一键的处理。如果指定了ON DUPLICATE KEY UPDATE,当要插入的主键或唯一键有重复时,会用配置值替换待插入的值;如果不指定ON DUPLICATE KEY UPDATE,当要插入的主键或唯一键有重复时,插入报错。

示例

示例表及数据基于以下定义:

  1. OceanBase(admin@test)>create table t1(c1 int primary key, c2 int) partition by key(c1) partitions 4;
  2. Query OK, 0 rows affected (0.16 sec)
  3. OceanBase(admin@test)>create table t2(c1 int primary key, c2 int);
  4. Query OK, 0 rows affected (0.16 sec)
  5. OceanBase(admin@test)>select * from t2;
  6. +----+------+
  7. | c1 | c2 |
  8. +----+------+
  9. | 1 | 1 |
  10. | 2 | 2 |
  11. | 3 | 3 |
  12. | 4 | 4 |
  13. +----+------+
  14. 4 rows in set (0.06 sec)
  • 向表 t1 中插入一行数数据。
  1. OceanBase(admin@test)>insert into t1 values(1,1);
  2. Query OK, 1 row affected (0.01 sec)
  3. OceanBase(admin@test)>select * from t1;
  4. +----+------+
  5. | c1 | c2 |
  6. +----+------+
  7. | 1 | 1 |
  8. +----+------+
  9. 1 row in set (0.04 sec)
  • 向表 t1 中插入多行数据。
  1. OceanBase(admin@test)>insert t1 values(1,1),(2,default),(2+2,3*4);
  2. Query OK, 3 rows affected (0.02 sec)
  3. Records: 3 Duplicates: 0 Warnings: 0
  4. OceanBase(admin@test)>select * from t1;
  5. +----+------+
  6. | c1 | c2 |
  7. +----+------+
  8. | 1 | 1 |
  9. | 2 | NULL |
  10. | 4 | 12 |
  11. +----+------+
  12. 3 rows in set (0.02 sec)
  • 向表 t1 指定的 p0 分区插入单行数据。
  1. OceanBase(admin@test)>insert into t1 partition(p0) (c1) values(5);
  2. Query OK, 1 row affected (0.02 sec)
  3. OceanBase(admin@test)>select * from t1 partition(p0);
  4. +----+------+
  5. | c1 | c2 |
  6. +----+------+
  7. | 5 | NULL |
  8. +----+------+
  9. 1 row in set (0.01 sec)
  • 将表 t2 的查询结果作为数据插入表 t1。
  1. OceanBase(admin@test)>insert into t1 select * from t2;
  2. Query OK, 4 rows affected (0.02 sec)
  3. Records: 4 Duplicates: 0 Warnings: 0
  4. OceanBase(admin@test)>select * from t1;
  5. +----+------+
  6. | c1 | c2 |
  7. +----+------+
  8. | 1 | 1 |
  9. | 2 | 2 |
  10. | 3 | 3 |
  11. | 4 | 4 |
  12. +----+------+
  13. 4 rows in set (0.01 sec)
  • 向表 t1 中插入重复主键值时利用 ON DUPLICATE KEY UPDATE 功能进行值更新
  1. OceanBase(admin@test)>insert into t1 values(1,1),(1,2) ON DUPLICATE KEY UPDATE c1=100;
  2. Query OK, 3 rows affected (0.01 sec)
  3. Records: 2 Duplicates: 1 Warnings: 0
  4. OceanBase(admin@test)>select * from t1;
  5. +-----+------+
  6. | c1 | c2 |
  7. +-----+------+
  8. | 100 | 1 |
  9. +-----+------+
  10. 1 row in set (0.02 sec)
  • 对可更新视图v的插入值
  1. OceanBase(admin@test)>create view v as select * from t1;
  2. Query OK, 0 rows affected (0.07 sec)
  3. OceanBase(admin@test)>insert into v values(1,1);
  4. Query OK, 1 row affected (0.01 sec)
  5. OceanBase(admin@test)>select * from v;
  6. +----+------+
  7. | c1 | c2 |
  8. +----+------+
  9. | 1 | 1 |
  10. +----+------+
  11. 1 row in set (0.02 sec)

注意事项

INSERT语句不支持直接对子查询进行插入操作,比如:insert into (select * from t1) values(1, 1);