如何使用 ON DUPLICATE KEY UPDATE 在 MySQL 中執行更新插入 (upsert) 操作

簡介

在許多情況下，您可能需要確保表格中存在一筆記錄，且沒有衝突的條目。 基本上，您想要查找並修改現有記錄（如果存在），或者新增一筆具有您想要的值的新記錄（如果尚不存在）。 這通常被稱為「更新插入 (upsert)」操作（「插入」和「更新」的組合）。

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 語句之外必須使用的檢查和條件邏輯量。