INSERT

描述

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

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

语法

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:
    table_name [PARTITION (partition_name,...)]

values_clause:
     {{VALUES | VALUE} ({expr | DEFAULT},...) [, ...]
      | select_stmt}

column_list
    column_name [, ...]

update_asgn_list:
     column_name = expr [, ...]

参数解释

示例

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

obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected 

obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected 
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set 

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

  obclient>  INSERT INTO t1 VALUES(1,1);
  Query OK, 1 row affected 
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  +----+------+
  1 row in set 
  

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

  obclient> INSERT t1 VALUES(1,1),(2,DEFAULT),(2+2,3*4);
  Query OK, 3 rows affected 
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  |  2 | NULL |
  |  4 |   12 |
  +----+------+
  3 rows in set 
  

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

  obclient> INSERT INTO t1 PARTITION(p0) (c1) VALUES(5);
  Query OK, 1 row affected 
  obclient> SELECT * FROM t1 PARTITION(p0);
  +----+------+
  | c1 | c2   |
  +----+------+
  |  5 | NULL |
  +----+------+
  1 row in set 
  

• 向表 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 
  Records: 2  Duplicates: 1  Warnings: 0
  
  obclient> SELECT * FROM t1;
  +-----+------+
  | c1  | c2   |
  +-----+------+
  | 100 |    1 |
  +-----+------+
  1 row in set 
  

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

  obclient> CREATE VIEW v AS SELECT * FROM t1;
  Query OK, 0 rows affected 
  obclient> INSERT INTO v VALUES (1,1);
  Query OK, 1 row affected 
  
  obclient> SELECT * FROM v;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  +----+------+
  1 row in set 
  

• 向 t2 表中插入 t1 的查询结果。

  obclient> INSERT INTO t1 SELECT * FROM t2 WHERE c1>2;
  Query OK, 2 rows affected 
  Records: 2  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  3 |    3 |
  |  4 |    4 |
  +----+------+
  2 rows in set