迹忆客 专注技术分享

数据库管理员可能偶尔需要向 MySQL 表中添加一条记录，或者更新一条经常出现的记录。 如果用户尝试插入一条已经存在或具有重复 PRIMARY KEY 值的记录，MySQL 将发送警报。

本文概述了可用于纠正此错误并获得更好结果的方法。

如果存在则更新记录，否则将其插入 MySQL 表中

当需要在确定行实际上是新的还是已经存在后插入行时，MySQL 提供了几个有用的语句。

如果记录存在，则使用 REPLACE INTO 更新记录，否则将其插入 MySQL 表

如果 INSERT 命令由于重复的 UNIQUE 或 PRIMARY KEY 值而失败，如前所述，如果您想真正替换行，则一种方法是使用 REPLACE 语句。

使用 REPLACE INTO 语句时，每个发出的命令可能会产生两种结果：

• 执行典型的 INSERT 语句是因为找不到具有所需值的数据行。
• 当发现匹配的数据行时，使用通常的 DELETE 语句删除现有行，然后执行标准 INSERT。

例如，假设我们有一个 employees 表，其中包含 id、department、employee_name 和 joining_year 列。 我们已决定将我们的部门和 joining_year of id = 101 记录更改回原始操作记录。

-- Before update
SELECT * from employees where id = 101;
REPLACE INTO employees
    (id, department, employee_name, joining_year)
VALUES
    (101, 'Operations', 'John Doe', 2013);
-- After update
SELECT * from employees where id = 101;

在任何与 MySQL 兼容的浏览器中运行上面的代码行。 它将显示以下结果：

-- Before update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Technology | John Doe      |       2017     |
+----+------------+---------------+----------------+

Query OK, 2 rows affected (0.01 sec)

-- After update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Operations | John Doe      |       2013     |
+----+------------+---------------+----------------+

在此示例中，即使我们只更改了一个项目，结果也显示有两行受到影响，因为我们删除了前一条记录，然后插入了新行以取代它。

使用 INSERT ... ON DUPLICATE KEY UPDATE 更新记录（如果存在）否则将其插入 MySQL 表

我们可以使用 INSERT ... ON DUPLICATE KEY UPDATE 语句和子句作为插入到可能具有重复 UNIQUE 或 PRIMARY KEY 值的行的替代方法。

通过仅发出 INSERT 或 UPDATE 指令而不发出 DELETE，利用 INSERT ... ON DUPLICATE KEY UPDATE 是非破坏性的，与 REPLACE 相比，后者由于在必要时执行 DELETE 命令而本质上是有害的。

例如，假设我们有一个 employees 表，其中包含 id、department、employee_name 和 joining_year 列。 我们已决定将我们的部门和 joining_year of id = 101 记录更改回原始操作记录。

现在可以将新的 ON DUPLICATE KEY UPDATE 子句添加到我们原来的 INSERT 语句中：

-- Before update
SELECT * from employees where id = 101;
SET @id = 101,
    @department = 'Operations',
    @employee_name = 'John Doe',
    @joining_year = 2013;
INSERT INTO employees
    (id, department, employee_name, joining_year)
VALUES
    (@id, @department, @employee_name, @joining_year)
ON DUPLICATE KEY UPDATE
    department = @department,
    employee_name = @employee_name,
    joining_year = @joining_year;
-- After update
SELECT * from employees where id = 101;

在上面的示例中，我们选择使用用户变量来避免多次声明我们希望 INSERT 或 UPDATE 的实际值，即使 ON DUPLICATE KEY UPDATE 方法没有必要按预期工作。

在与 MySQL 兼容的任何浏览器中运行上面的代码行。 它将显示以下结果：

-- Before update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Technology | John Doe      |       2017     |
+----+------------+---------------+----------------+

Query OK, 1 rows affected (0.00 sec)

-- After update
+----+------------+---------------+----------------+
| id | department | employee_name | year_published |
+----+------------+---------------+----------------+
|101 | Operations | John Doe      |       2013     |
+----+------------+---------------+----------------+