数据库 insert 数据的几种方式

INSERT INTO 语句用于向表中插入新记录。

首先创建一个 fruit 表，表结构如下所示：

+----+------------+-------------+
| id | name       | floor_price |
+----+------------+-------------+
|  0 | Apple      |         100 |
+----+------------+-------------+

插入数据

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可，语法如下所示：

INSERT INTO table_name
VALUES (value1,value2,value3,...);

实例如下：

INSERT INTO fruit
VALUES (2, 'Banana', 36.9);

第二种形式需要指定列名及被插入的值，语法如下所示：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

实例如下：

INSERT INTO fruit (id, name, floor_price)
VALUES (2, 'Banana', 36.9);

批量插入数据

批量插入的基本语法如下所示：

INSERT INTO table_name (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6),
    (value7, value8);

实例如下：

INSERT INTO fruit (id, name, floor_price)
VALUES (1, 'Apple', 100.0),
       (2, 'Banana', 36.9),
       (3, 'Pear', 0.6),
       (4, 'Peach', 1.1);

数据库中表数据如下：

+----+------------+-------------+
| id | name       | floor_price |
+----+------------+-------------+
|  1 | Apple      |         100 |
|  2 | Banana     |        36.9 |
|  3 | Pear       |         0.6 |
|  4 | Peach      |         1.1 |
+----+------------+-------------+

插入和更新数据

在许多情况下，我们可能希望确保表中存在一条记录而没有冲突的条目。本质上，我们希望查找并修改当前记录（如果存在），或者添加具有所需值的新记录（如果尚未存在），这通常称为“upsert”操作（“insert”和“update”的组合）。

“upsert” 操作的实际实现在不同数据库中有不同的实现方式，下面主要介绍 MySQL 和 PostgreSQL 的实现方式。

在 MySQL 中插入或更新数据使用 ON DUPLICATE KEY UPDATE

MySQL 允许我们使用 ON DUPLICATE KEY UPDATE 子句来修改 INSERT 命令来执行此操作。接下来将介绍如何使用此构造来更新条目的值（如果存在），或者将其添加为表中的新行。

插入或更新操作的基本语法如下所示：

INSERT INTO table_name (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6),
    (value7, value8)
ON DUPLICATE KEY UPDATE
    <column1> = <value1>,
    <column2> = <value2>;

在 ON DUPLICATE KEY UPDATE 子句之后可以提供多个列，每个列定义如果与现有记录发生冲突，新值应该是什么。

实例如下：

INSERT INTO fruit (id, name, floor_price)
Values (1, 'Apple', 100.0),
       (2, 'Banana', 44.9),
       (3, 'Pear', 50.6),
       (4, 'Peach', 71.1),
       (5, 'Strawberry', 29.5)
ON DUPLICATE KEY UPDATE id          = Values(id),
                        name        = Values(name),
                        floor_price = Values(floor_price);

数据库中表数据如下：

+----+------------+-------------+
| id | name       | floor_price |
+----+------------+-------------+
|  1 | Apple      |         100 |
|  2 | Banana     |        44.9 |
|  3 | Pear       |        50.6 |
|  4 | Peach      |        71.1 |
|  5 | Strawberry |        29.5 |
+----+------------+-------------+

MySQL 将接受该语句，插入id 为 “5” 的新行，并更新与现有记录冲突的行（已经有id 为“1”、“2”、“3”、“4”的记录），但是它们的floor_price 不一样。

在 PostgreSQL 中插入或更新数据使用 INSERT ON CONFLICT

PostgreSQL 中的实际实现使用带有特殊 ON CONFLICT 子句的 INSERT 命令来指定如果表中已经存在记录时要做什么。如果记录已在表中找到，您可以指定是否要更新该记录或静默跳过该记录。

插入或更新操作的基本语法如下所示：

INSERT INTO table_name (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6),
    (value7, value8)
ON CONFLICT <target> <action>;

<target> 指定发生冲突时我们要为其定义的策略。可以是以下任何一种：

• 特定列或列的名称：(column1)
• 唯一约束的名称： ON CONSTRAINT <constraint_name>

<action> 定义 PostgreSQL 在发生冲突时应该做什么。指定的 <action> 可以是以下之一：

• DO NOTHING：告诉 PostgreSQL 保留冲突记录原样。本质上，此操作不会进行任何更改，但会抑制在我们尝试插入违反条件的行时通常会发生的错误。
• DO UPDATE：这告诉 PostgreSQL 我们想更新表中已经存在的行。更新的语法反映了普通 UPDATE 命令的语法。

当指定 DO UPDATE 时，一个名为 EXCLUDED 的特殊虚拟表可在 UPDATE 子句中使用。该表包含原始 INSERT 命令中建议的值（与现有表值冲突）。

DO UPDATE 操作的实例如下：

INSERT INTO fruit
VALUES (1, 'Apple', 100.0),
       (2, 'Banana', 44.9),
       (3, 'Pear', 60.6),
       (4, 'Peach', 71.1),
       (5, 'Strawberry', 29.5),
       (6, 'Tomato', 85.5)
ON CONFLICT (id) DO UPDATE SET id          = EXCLUDED.id,
                               name        = EXCLUDED.name,
                               floor_price = EXCLUDED.floor_price;

数据库中表数据如下：

 id |    name    | floor_price
----+------------+-------------
  0 | apple      |        74.5
  1 | Apple      |         100
  2 | Banana     |        44.9
  3 | Pear       |        60.6
  4 | Peach      |        71.1
  5 | Strawberry |        29.5
  6 | Tomato     |        85.5