MySQL で `ON DUPLICATE KEY UPDATE` を使用してデータを upsert する方法

はじめに

多くの場合、競合するエントリなしにレコードがテーブルに存在するようにしたい場合があります。本質的に、既存のレコードが存在する場合はそれを検索して変更し、存在しない場合は必要な値で新しいレコードを追加したいと考えています。これは一般的に「upsert」操作（「insert」と「update」の組み合わせ）と呼ばれます。

MySQL では、ON DUPLICATE KEY UPDATE 句を使用して INSERT コマンドを変更することで、このアクションを実行できます。このガイドでは、この構文を使用して、エントリが存在する場合はその値を更新し、存在しない場合はテーブルに新しい行として追加する方法について説明します。

INSERT...ON DUPLICATE KEY UPDATE 構文の使用方法

insert または 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 を、2 行が影響を受けたと見なします。競合が発生せず、新しいレコードが追加された場合は、代わりに 1 行が影響を受けたと表示されます。既存のレコードが見つかったが、カラムにすでに正しい値が設定されている場合は、影響を受けた行は報告されません。

次のコマンドを入力して、行が新しい情報で更新されたことを確認できます。

SELECT * FROM director;

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

複数のレコードを一度に挿入または更新する方法

複数のレコードを同時に挿入または更新しようとしている場合、各カラムに設定する値は、どのレコードが競合したかによって異なる可能性があります。たとえば、4 つの新しい行を挿入しようとしていて、3 行目の id カラムが既存のレコードと競合している場合、既存の行を 3 行目に予定していたデータに基づいて更新するのが最も望ましいでしょう。

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 はステートメントを受け入れ、2 つの新しい行を挿入し、既存のレコードと競合する 1 つの行を更新します（id が「2」のレコードがすでに存在します）。

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

テーブルのデータを表示すると、2 つの新しい行が期待どおりに表示され、競合する行の値が適切な新しい情報で更新されていることがわかります。

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 ステートメントの外部で使用する必要があるチェックと条件付きロジックの量を最小限に抑えるのに役立ちます。