MySQL UPSERT

UPSERT 是 DBMS 软件用于管理数据库的基本功能之一。此操作允许 DML 用户插入新记录或更新表中的现有数据。UPSERT 由两个词组成：UPDATE 和 INSERT。前两个字母 UP 代表 UPDATE，而 SERT 代表 INSERT。UPSERT 是一个原子操作，这意味着它是一个单步完成的操作。例如，如果记录是新的，它将触发 INSERT 命令。但是，如果它已存在于表中，则此操作将执行 UPDATE 语句。

默认情况下，MySQL 为 INSERT 提供了 ON DUPLICATE KEY UPDATE 选项，以完成此任务。但是，它还包含其他一些语句来实现此目标，例如 INSERT IGNORE 或 REPLACE。我们将详细学习和查看所有这些解决方案。

MySQL UPSERT 示例

我们主要可以通过三种方式执行 MySQL UPSERT 操作，如下所示：

1. 使用 INSERT IGNORE 的 UPSERT
2. 使用 REPLACE 的 UPSERT
3. 使用 INSERT ON DUPLICATE KEY UPDATE 的 UPSERT

使用 INSERT IGNORE 的 UPSERT

INSERT IGNORE 语句 用于在我们执行无效行插入操作时忽略执行错误。例如，主键列不允许存储重复值。如果我们尝试插入具有与表中已存在的主键相同的新记录，我们将收到错误。但是，如果我们使用 INSERT IGNORE 命令执行此操作，它将生成警告而不是错误。

语法

以下是在 MySQL 中使用 INSERT IGNORE 语句的语法：

MySQL INSERT IGNORE 示例

让我们了解 MySQL 中 INSERT IGNORE 语句的工作原理。首先，我们需要使用以下语句创建一个名为 “Student” 的表：

UNIQUE 约束 确保我们不能将重复值保存在 email 列中。接下来，需要将记录插入表中。以下语句用于向表中添加数据：

现在，我们可以使用 SELECT 语句验证 插入 操作：

我们将得到以下输出，表中包含 三 行：

现在，我们将执行以下语句向表中添加两条记录：

执行上述语句后，我们将看到错误：ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'student.Email'，因为电子邮件违反了 UNIQUE 约束。

但是，当我们使用 INSERT IGNORE 命令执行相同的语句时，我们没有收到任何错误。相反，我们只收到一个警告。

MySQL 将生成一条消息：添加了一行，另一行被忽略。

我们可以使用 SHOW WARNINGS 命令查看详细警告：

因此，如果存在重复项并且我们使用 INSERT IGNORE 语句，MySQL 会发出警告而不是错误，并将剩余的记录添加到表中。

使用 REPLACE 的 UPSERT

在某些情况下，我们希望更新表中的现有记录以使其保持最新。如果为此目的使用标准插入查询，它将给出主键重复条目错误。在这种情况下，我们将使用 REPLACE 语句来执行我们的任务。当我们使用 REPLACE 命令时，会发生 两种可能 的事件：

• 如果未找到与现有数据行匹配的值，则执行标准 INSERT 语句。
• 如果旧记录与新记录匹配，则 replace 命令将删除现有行，然后执行正常的 INSERT 语句，将新记录添加到表中。

在 REPLACE 语句中，更新分两步执行。首先，它将删除现有记录，然后添加新更新的记录，类似于标准 INSERT 命令。因此，我们可以说 REPLACE 语句执行两个标准功能：DELETE 和 INSERT。

MySQL 中 REPLACE 语句的语法如下：

示例

让我们通过一个真实的例子来理解它。这里，我们将使用我们之前创建的 Student 表，其中包含以下数据：

现在，我们将使用 REPLACE 语句将 ID 为 2 的学生的城市 更新为新城市 洛杉矶。为此，执行以下语句：

成功执行后，我们将得到如下输出：

在上面的图片中，我们可以看到消息显示 “2 行受影响”，而我们只更新了一行的值。这是因为 REPLACE 命令首先删除了记录，然后将新记录添加到表中。因此，消息显示“2 行受影响”。

使用 INSERT ON DUPLICATE KEY UPDATE 的 UPSERT

到目前为止，我们已经看到了两个 UPSERT 命令，但它们有一些限制。INSERT IGNORE 语句只忽略重复错误，而不对表进行任何修改。而 REPLACE 方法检测到 INSERT 错误，但它会在添加新记录之前删除行。因此，我们仍在寻找更完善的解决方案。

所以，我们使用一个更完善的解决方案，即 INSERT ON DUPLICATE KEY UPDATE 语句。它是一种非破坏性方法，这意味着它不会删除重复行。相反，当我们在 SQL 语句中指定 ON DUPLICATE KEY UPDATE 子句，并且一行会导致 UNIQUE 或 PRIMARY KEY 索引列中出现重复错误值时，则会更新现有行。

MySQL 中 Insert on Duplicate Key Update 语句的语法如下：

示例

让我们通过一个真实的例子来理解它。这里，我们将使用我们之前创建的 Student 表。现在，使用以下查询向表中添加一行：

此查询将成功向表中添加一条记录，因为它没有任何重复值。

接下来，执行以下 MySQL UPSERT 命令，该命令更新 Stud_ID 列中的重复记录：

成功执行后，MySQL 会给出以下消息：

在输出中，我们可以看到 行 ID=4 已经存在。因此，查询只将 城市纽约 更新为 加利福尼亚。