INSERT

描述

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

使用限制及注意事项

不支持直接对子查询进行插入操作，例如 INSERT INTO (SELECT * FROM t1) VALUES(1, 1)。

权限要求

• 执行 INSERT 语句，需要拥有目标表的 INSERT 权限。
• 执行 INSERT ... SELECT ... 语句，不仅需要拥有目标表的 INSERT 权限，还需要拥有源表（即 SELECT 子句中涉及的表）的有 SELECT 权限。

有关 OceanBase 数据库权限的详细介绍，请参见 MySQL 模式下的权限分类。

语法

INSERT {into_table_insert | overwrite_table_insert};

into_table_insert:
    [hint_options] [IGNORE] [INTO]
    single_table_insert
    [ON DUPLICATE KEY UPDATE update_asgn_list]

hint_options:
    [/*+ [APPEND | DIRECT(bool, int, ['load_mode'])] enable_parallel_dml PARALLEL(N) | NO_DIRECT */]

load_mode:
    full
    | inc
    | inc_replace

single_table_insert:
    table_name [PARTITION (partition_name, ...)] [(column_list)] {VALUES | VALUE} (values_list)[, (values_list) ...]
    | table_name [PARTITION (partition_name, ...)] [(column_list)] select_stmt
    | table_name [PARTITION (partition_name, ...)] SET update_asgn_list

column_list:
    column_name [,column_name ...]

values_list:
    value [,value ...]

value:
    expr
    | DEFAULT

update_asgn_list:
    column_name = expr [,column_name = expr ...]

overwrite_table_insert:
     [/*+ PARALLEL(N)*/] OVERWRITE [INTO] table_name [(column_list)] [PARTITION (partition_name, ...)] select_stmt;

参数解释

hint_options

• [APPEND | DIRECT(bool, int, [load_mode])] enable_parallel_dml PARALLEL(N)：

  • APPEND | DIRECT(bool, int, [load_mode])：可选项，使用 Hint 启用旁路导入功能。

    注意

    在执行旁路导入任务的过程中，不建议同时进行 OceanBase 数据库的升级操作，因为这可能导致旁路导入任务失败。

    • APPEND：默认等同于使用的 DIRECT(true, 0)，同时可以实现在线收集统计信息（GATHER_OPTIMIZER_STATISTICS Hint）的功能。

    • DIRECT(bool, int, [load_mode])：

      • bool：表示写入的数据是否需要排序。取值如下：

        • true：表示需要排序。
        • false：表示不需要排序。

      • int：表示最大容忍的错误行数。

      • load_mode：可选参数，表示旁路导入的模式。取值须使用英文单引号包起来，取值如下：

        • full：默认值，表示全量导入。

        • inc：用于增量旁路导入场景。主键冲突时按插入或忽略冲突的方式处理，具体行为与语句中的 IGNORE 等选项及 inc 模式下的执行语义有关。增量旁路导入的能力限制参见 数据导入概述。

        • inc_replace：表示增量导入，但不检查主键是否重复，相当于 REPLACE 语义的增量导入。

          注意

          当 load_mode 取值为 inc_replace 时，INSERT 语句中不允许有 IGNORE 关键字。

        更多使用 INSERT INTO SELECT 旁路导入的信息，参见 数据导入概述。

  • enable_parallel_dml parallel(N)：可选项，加载数据的并行度。

    说明

    一般情况下，enable_parallel_dml Hint 和 parallel Hint 必须配合使用才能开启并行 DML。不过，当目标表的 Schema 上指定了表级别的并行度时，仅需指定 enable_parallel_dml Hint。

• NO_DIRECT：控制单条 SQL 强制不走旁路导入，只要输入的 SQL 带有该 Hint，则整个语句忽略其他旁路导入的 Hint，执行普通导入。

更多有关 Hint 的介绍信息，参见 Optimizer Hint。

single_table_insert

• [(column_list)] {VALUES | VALUE} (values_list)[, (values_list) ...]：直接将一组明确的值插入至目标表中。

  • column_list：可选项，指定插入数据对应的列名列表，多个列名间用逗号（,）分隔。

  • VALUES | VALUE: 用于引入待插入值列表的关键字。

  • values_list: 用于指定插入的数据值列表，用括号（()）包围。可以有多个值列表，值列表间以逗号（,）分隔，每个值列表代表一行数据。

    • values：插入的单个值，每个值对应一个列，值间以逗号（,）分隔。可以是表达式（expr）或 DEFAULT 关键字来使用列的默认值。

  示例如下：

  • 向表 tbl1 中插入一行数数据。

    INSERT INTO tbl1(col1, col2) VALUES(1, 'A1');
    

  • 向表 tbl1 中插入多行数据。

    INSERT INTO tbl1(col1, col2) VALUES(2, 'A2'),(3, 'A3');
    

• [(column_list)] select_stmt：从一个或多个源表中选取数据，并将查询到的结果插入到目标表中。

  • column_list：可选项，指定插入数据对应的列名列表，多个列名间用逗号（,）分隔。
  • select_stmt: 指定要执行的查询语句，其结果将被插入到目标表中。select_stmt 必须返回一个结果集，查询语句的具体结构和选项信息，参见 SELECT 语句。

  示例如下：

  INSERT INTO tbl1(col1, col2) SELECT col1, col2 FROM tbl2 WHERE col2 = 'a3';
  

• SET update_asgn_list：通过 SET 子句指定各列的值，从而将数据插入到目标表中。 update_asgn_list 表示赋值语句，详细介绍可参见下文 update_asgn_list。

  示例如下：

  INSERT INTO tbl1 SET col1= 4, col2='a4';
  

update_asgn_list

column_name = expr [, ...]：列名等于表达式的列表，用于更新已存在的行，多个值对间用逗号（,）分隔。

示例如下：

• 指定当发生键冲突时，将目标行的 col2 列的值更新为原本尝试插入的值（即 VALUES(col2)，在这个例子中是 a2）。

  INSERT INTO tbl1(col1, col2) VALUES (2, 'a2') ON DUPLICATE KEY UPDATE col2 = VALUES(col2);
  

示例

示例表 test_tbl1 和 test_tbl2 定义如下：

CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 INT)
    PARTITION BY KEY(col1)
         (PARTITION p0,
          PARTITION p1,
          PARTITION p2,
          PARTITION p3
         );

CREATE TABLE test_tbl2(col1 INT PRIMARY KEY, col2 INT);

• 向表 test_tbl1 指定的 p0 分区插入单行数据。

  INSERT INTO test_tbl1 PARTITION(p0) (col1) VALUES(5);
  

  查看表 test_tbl1 数据：

  SELECT * FROM test_tbl1 PARTITION(p0);
  

  返回结果如下：

  +------+------+
  | col1 | col2 |
  +------+------+
  |    5 | NULL |
  +------+------+
  1 row in set
  

• 向可更新视图 v 的插入值。

  CREATE VIEW v AS SELECT * FROM test_tbl2;
  

  INSERT INTO v VALUES (1, 1);
  

  查看视图 v 数据：

  SELECT * FROM v;
  

  返回结果如下：

  +------+------+
  | col1 | col2 |
  +------+------+
  |    1 |    1 |
  +------+------+
  1 row in set
  

• INSERT OVERWRITE SELECT 示例：

  1. 向表 test_tbl1 中插入数据。

     INSERT INTO test_tbl1 VALUES (1, 30),(2, 25),(3, 22);
     

  2. 使用 INSERT OVERWRITE SELECT 语句，基于 col2 大于 25 从 test_tbl1 中筛选数据，并将这些数据插入到 test_tbl2 中，替换其原有内容。

     INSERT OVERWRITE test_tbl2 SELECT * FROM test_tbl1 WHERE col2 > 25;
     

  3. 查看表 test_tbl2 替换数据后的数据。

     SELECT * FROM test_tbl2;
     

     返回结果如下：

     +------+------+
     | col1 | col2 |
     +------+------+
     |    1 |   30 |
     +------+------+
     1 row in set
     

旁路导入相关限制（INSERT 语法）

使用 INSERT INTO ... SELECT ... 配合 APPEND、DIRECT(...)、enable_parallel_dml、parallel(N) 等 Hint 执行旁路导入时，除 数据导入概述 中说明的旁路导入能力适用范围与限制外，还须满足以下 INSERT INTO SELECT 语句级限制：

全量旁路导入

• 只支持 PDML（Parallel Data Manipulation Language，并行数据操纵语言），非 PDML 不能用旁路导入。有关并行 DML 的更多信息，参见 并行 DML。
• 旁路导入属于 DDL 语句，无法在多行事务（包含多个操作的事务）中执行。
• 不能在 BEGIN 中执行。
• Autocommit 必须设置为 1。

增量旁路导入

• 只支持 PDML，非 PDML 不能用旁路导入。有关并行 DML 的更多信息，参见 并行 DML。
• 有索引（不包括主键）的表不支持增量旁路导入。

相关文档

• 插入数据
• 数据导入概述
• SELECT 语句