关于 INSERT 语句

INSERT 语句用来向表中插入行记录，本文介绍了相关语句的使用方法和示例。

INSERT 语句

INSERT 语句语法格式如下：

INSERT INTO table_name (list_of_columns) VALUES (list_of_values);

在向一个表插入记录之前，需要了解这个表所有的列信息，以及列类型和有效值、是否允许为空等。在 obclient 命令行环境下，可以直接用 DESC 命令查看列属性，如下所示：

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

在 INSERT 语句中，需要知道所有列属性的信息：

• 列属性为 NOT NULL

  • 若列属性有默认值时，您可以不指定该列的值，OceanBase 会在该列上插入默认值。

  • 若列属性无默认值，则必须指定该列的值。

• 如果列为 NULL，您也可以不指定该列的值，OceanBase 会在该列上插入一个 NULL 值。

当插入多条件记录时，可以分多条 INSERT 语句，也可以用一个 INSERT 多个 VALUES 语句。

示例

如下示例创建有默认值列的表（gmt_create 字段有默认值）：

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 

使用 INSERT 语句插入数据

• 知道所有列信息

  通过下列 SQL 插入一笔所有字段信息都有值的记录。

  obclient> INSERT INTO t_insert(id, name, value, gmt_create)
     values(1,'CN',10001, current_timestamp);
  Query OK, 1 row affected 
  

• 未知道所有列信息

  下面 SQL 插入两笔记录，使用一个 INSERT 多个 VALUES 子句。

  说明

  其中 gmt_create 字段没有提供，但是该字段有默认值，所以插入数据可以执行成功。

  obclient> INSERT INTO t_insert(id, name, value) 
  VALUES (2,'US', 10002) ,(3,'EN', 10003);
  Query OK, 2 rows affected
  

使用 INSERT 语句违反唯一约束冲突

当表上有唯一性约束的时候，插入相同的记录，数据库会报错。

obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

这个报错可以通过 INSERT IGNORE INTO、INSERT INTO ON DUPLICATE KEY UPDATE 避免。

• 通过 INSERT IGNORE INTO 避免约束冲突，IGNORE 关键字可以忽略由于约束冲突导致的 INSERT 失败的影响。

  obclient> INSERT IGNORE INTO t_insert(id, name, value) 
      VALUES (3,'UK', 10003),(4, 'JP', 10004);
  Query OK, 1 row affected 
  
  obclient> SELECT * FROM t_insert;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   | 10001 | 1970-01-01 16:05:45 |
  |  2 | US   | 10002 | 1970-01-01 16:05:54 |
  |  3 | EN   | 10003 | 1970-01-01 16:05:54 |
  |  4 | JP   | 10004 | 1970-01-01 16:06:08 |
  +----+------+-------+---------------------+
  4 rows in set 
  

• 通过 INSERT INTO ON DUPLICATE KEY UPDATE 避免约束冲突，可以指定对重复主键或唯一键的后续处理，以下示例对冲突行内容进行了更新。

  说明

  • 指定 ON DUPLICATE KEY UPDATE：当要插入的主键或唯一键有重复时，会用配置值替换待插入的值。

  • 不指定 ON DUPLICATE KEY UPDATE：当要插入的主键或唯一键有重复时，插入报错。

  obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004) ON DUPLICATE KEY UPDATE name=VALUES(name);
  Query OK, 1 row affected 
  
  obclient> SELECT * FROM t_insert;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   | 10001 | 1970-01-01 16:05:45 |
  |  2 | US   | 10002 | 1970-01-01 16:05:54 |
  |  3 | UK   | 10003 | 1970-01-01 16:05:54 |
  |  4 | JP   | 10004 | 1970-01-01 16:06:08 |
  +----+------+-------+---------------------+
  4 rows in set
  

使用查询语句充当 INSERT 的 values 子句

当需要备份一个表的备份或者全部记录时，可以使用 INSERT INTO ... SELECT ... FROM 语句。

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 

obclient> INSERT INTO ware_bak SELECT * FROM ware;
Query OK, 2 rows affected 
Records: 2  Duplicates: 0  Warnings: 0