The INSERT statement is used to insert a new row into a table.
The syntax for the simplest INSERT statement:
INSERT INTO table_name (list_of_columns) VALUES (list_of_values);
Where, list_of_columns specifies the table_name column of the table. list_of_values specifies a list of values corresponding to each column that you specified in list_of_columns. Therefore, before you insert a row into a table, you need to know the information of all columns in the table, including the column types, valid values, and whether the columns can contain null values. In the command-line (CLI) environment of OceanBase Client (OBClient), you can directly use the DESC command to view the column attributes. Example:
obclient> desc ordl;
+----------------+-------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+----------------+-------------+------+-----+---------+-------+
| OL_W_ID | NUMBER(38) | NO | PRI | NULL | NULL |
| OL_D_ID | NUMBER(38) | NO | PRI | NULL | NULL |
| OL_O_ID | NUMBER(38) | NO | PRI | NULL | NULL |
| OL_NUMBER | NUMBER(38) | NO | PRI | NULL | NULL |
| OL_DELIVERY_D | DATE | YES | NULL | NULL | NULL |
| OL_AMOUNT | NUMBER(6,2) | YES | NULL | NULL | NULL |
| OL_I_ID | NUMBER(38) | YES | NULL | NULL | NULL |
| OL_SUPPLY_W_ID | NUMBER(38) | YES | NULL | NULL | NULL |
| OL_QUANTITY | NUMBER(38) | YES | NULL | NULL | NULL |
| OL_DIST_INFO | CHAR(24) | YES | NULL | NULL | NULL |
+----------------+-------------+------+-----+---------+-------+
10 rows in set (0.01 sec)
In an INSERT statement, you do not need to know the values in every column of a table, but you need to know the values in the columns
that have a NOT NULL constraint. For a NOT NULL column that has a default value, you do not need to specify the values.
For a NULL column, you do not need to specify values either because OceanBase Database will insert a NULL value into the column.
To insert a multi-condition record into a table, you can use multiple INSERT statements or a single INSERT statement that has multiple VALUES clauses.
Example: Using an INSERT statement when you know all column information
The following example creates a table where a column has default values and uses an SQL statement to insert two records. All fields have values.
obclient> CREATE TABLE t_insert(
id number NOT NULL PRIMARY KEY
, name varchar(10) NOT NULL, value number
, gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
Query OK, 0 rows affected (0.07 sec)
obclient> INSERT INTO t_insert(id, name, value, gmt_create)
values(1,'CN',10001, current_timestamp);
Query OK, 1 row affected (0.01 sec)
Example: Using an INSERT statement when you do not know all column information
An SQL statement is used in the following example to insert two records, but the gmt_create field is not provided. The two records are inserted by using an INSERT statement that has multiple VALUES clauses.
obclient> INSERT INTO t_insert(id, name, value)
VALUES (2,'US', 10002) ,(3,'EN', 10003);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
Example: Using an INSERT statement to insert values that violate the UNIQUE constraint
When you insert identical records into a table that has a UNIQUE constraint, the database returns an error.
obclient> INSERT INTO t_insert(id, name, value)
VALUES (3,'UK', 10003)
,(4, 'JP', 10004);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
You can use the INSERT IGNORE INTO, MERGE INTO, or INSERT INTO ON DUPLICATE KEY UPDATE statement to avoid this error.
About the INSERT IGNORE INTO statement
In the following example, an INSERT IGNORE INTO statement is used in a MySQL tenant to avoid constraint conflicts. You can use the IGNORE keyword to ignore the impact of an insert failure caused by a constraint conflict.
obclient> INSERT IGNORE INTO t_insert(id, name, value)
VALUES (3, 'UK', 10003), (4, 'JP', 10004);
Query OK, 1 row affected (0.00 sec)
obclient> select * from t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2020-04-03 16:05:45 |
| 2 | US | 10002 | 2020-04-03 16:05:54 |
| 3 | EN | 10003 | 2020-04-03 16:05:54 |
| 4 | JP | 10004 | 2020-04-03 16:06:08 |
+----+------+-------+---------------------+
4 rows in set (0.00 sec)
Example: Using a SELECT...FROM statement as an INSERT statement that has multiple VALUES clauses
To back up all or part of records in a table, you can use the INSERT INTO ... SELECT ... FROM statement.
obclient> create table ware_bak(
w_id int
, w_ytd decimal(12,2)
, w_tax decimal(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, primary key(w_id)
);
Query OK, 0 rows affected (0.17 sec)
obclient> insert into ware_bak select * from ware;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0