如何在 MySQL 中使用 `ON DUPLICATE KEY UPDATE` 進行 upsert 操作

簡介

在許多情況下，您可能希望確保表中的記錄存在且沒有衝突條目。實質上，您希望查詢並修改當前記錄（如果存在），或者在不存在時新增一個包含您所需值的新記錄。這通常被稱為“upsert”操作（“insert”和“update”的組合）。

MySQL 允許您使用 ON DUPLICATE KEY UPDATE 子句來修改 INSERT 命令，從而執行此操作。在本指南中，我們將介紹如何使用此結構來更新現有條目的值（如果存在），否則將其作為新行新增到表中。

如何使用 INSERT...ON DUPLICATE KEY UPDATE 結構

插入或更新操作的基本語法如下：

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

在 ON DUPLICATE KEY UPDATE 子句之後可以提供多個列，每個列都定義瞭如果與現有記錄發生衝突時新值應該是什麼。

為了演示此功能，我們假設有一個名為 director 的表，其列和填充資料如下：

CREATE TABLE director (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    latest_film VARCHAR(200)
);

INSERT INTO director (name)
VALUES
    ('frank'),
    ('bob'),
    ('sue');

表中的資料如下所示：

SELECT * FROM director;

+----+-------+-------------+
  id | name  | latest_film |
+----+-------+-------------+
   1 | frank | NULL        |
   2 | bob   | NULL        |
   3 | sue   | NULL        |
+----+-------+-------------+
3 rows in set (0.00 sec)

如果您嘗試插入另一行，其 id 列為“3”，MySQL 將通知您與現有行存在衝突：

INSERT INTO director (id, name) VALUES (3, 'susan');

ERROR 1062 (23000): Duplicate entry '3' for key 'director.PRIMARY'

如果我們預料到這種可能性並希望用新資訊更新現有行，我們可以避免這個錯誤。ON DUPLICATE KEY UPDATE 子句允許我們這樣做：

INSERT INTO director (id, name) VALUES (3, 'susan')
ON DUPLICATE KEY UPDATE name = 'susan';

Query OK, 2 rows affected (0.00 sec)

MySQL 將發生更新的 ON DUPLICATE KEY UPDATE 視為影響了兩行。如果沒有發生衝突並且添加了新記錄，則會顯示影響了一行。如果找到現有記錄但列已具有正確值，則不會報告任何行受影響。

您可以透過輸入以下內容確認該行已用新資訊更新：

SELECT * FROM director;

+----+-------+-------------+
  id | name  | latest_film |
+----+-------+-------------+
   1 | frank | NULL        |
   2 | bob   | NULL        |
   3 | susan | NULL        |
+----+-------+-------------+
3 rows in set (0.00 sec)

如何一次性插入或更新多條記錄

如果您嘗試同時插入或更新多條記錄，則要設定的每個列的值可能取決於哪些記錄發生了衝突。例如，如果您要插入四行新資料，但第三行的 id 列與現有記錄衝突，則您很可能希望根據您為第三行準備的資料來更新現有行。

MySQL 允許您使用 VALUES() 函式來引用這些提議的資料。該函式接受列名作為引數，並提供 INSERT 語句部分中給定的值。

基本語法如下：

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

這告訴 MySQL 使用與衝突行關聯的資料來更新值。

要了解這是如何工作的，我們回到之前的 director 表：

SELECT * FROM director;

+----+-------+-------------+
  id | name  | latest_film |
+----+-------+-------------+
   1 | frank | NULL        |
   2 | bob   | NULL        |
   3 | susan | NULL        |
+----+-------+-------------+
3 rows in set (0.00 sec)

假設我們想確保以下記錄存在於表中：

+----+--------+-------------+
  id | name   | latest_film |
+----+--------+-------------+
   4 | meg    | NULL        |
   2 | robert | NULL        |
   5 | tamara | NULL        |
+----+--------+-------------+

我們可以建立一個 INSERT...ON DUPLICATE KEY UPDATE 語句，如下所示：

INSERT INTO director (id, name)
VALUES
    (4, 'meg'),
    (2, 'robert'),
    (5, 'tamara')
ON DUPLICATE KEY UPDATE
    name = VALUES(name)

MySQL 將接受該語句，插入兩行新資料並更新與現有記錄衝突的一行（已有一條 id 為“2”的記錄）：

Query OK, 4 rows affected, 1 warning (0.01 sec)
Records: 3  Duplicates: 1  Warnings: 1

如果您查看錶資料，可以看到兩條新行按預期出現，並且衝突行的值已更新為相應的新資訊：

SELECT * FROM director;

+----+--------+-------------+
  id | name   | latest_film |
+----+--------+-------------+
   1 | frank  | NULL        |
   2 | robert | NULL        |
   3 | susan  | NULL        |
   4 | meg    | NULL        |
   5 | tamara | NULL        |
+----+--------+-------------+
5 rows in set (0.00 sec)

結論

MySQL 的 INSERT...ON DUPLICATE KEY UPDATE 結構允許您在插入資料時避免與現有記錄發生衝突。結合 VALUES() 函式，您可以使用它對已存在的記錄進行上下文更新，而無需發出多個語句。這一強大功能可以幫助您最大限度地減少在 SQL 語句之外必須使用的檢查和條件邏輯。