Description
You can use this statement to add one or more records to a table.
Syntax
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 [, ...]
Parameters
| Parameter | Description |
|---|---|
| IGNORE | Ignores errors that occur during the execution of an INSERT statement. |
| column_list | The column to which data is to be inserted. Separate multiple columns with a comma (,). |
| tbl_name | The name of the table to which the data is inserted. |
| partition_name | The name of the partition to which the data is inserted. |
| update_asgn_list | The statement that assigns values. For example, c1 = 2. |
| ON DUPLICATE KEY UPDATE | Specifies the action performed on duplicate primary keys or unique keys. If this parameter is specified, the configured value takes the place of the inserted value when the inserted primary key or unique key is duplicate. If this parameter is not specified, an error is returned when the inserted primary key or unique key is duplicate. |
Examples
Execute the following statement to create Table t1 and Table 2 and insert data into them:
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)
- Insert a row of data into Table 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)
- Insert two or more rows of data into Table 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)
- Insert a single row of data into Partition p0 of Table t1.
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)
- Insert the query results of Table t2 into Table 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)
- When you insert a duplicate primary key value into Table t1, use the ON DUPLICATE KEY UPDATE function to update the value.
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)
- Insert values into updatable View 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)
Note
The INSERT statement does not support direct INSERT operations on subqueries. Example: insert into (select * from t1) values(1, 1);