共有

はじめに

多くの場合、テーブル内に競合するエントリなしでレコードが存在することを確認したい場合があります。基本的に、既存のレコードを見つけて変更するか、存在しない場合は希望する値で新しいレコードを追加したいと考えています。これは一般に「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は、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ステートメントの外部で使用する必要があるチェックや条件ロジックの量を最小限に抑えるのに役立ちます。

著者について
Justin Ellingwood

Justin Ellingwood

ジャスティンは2013年からデータベース、Linux、インフラストラクチャ、開発者ツールについて執筆しています。現在は妻と2匹のウサギと共にベルリンに住んでいます。彼は通常、三人称で書く必要がないため、関係者全員にとって安心です。
© 2025 prisma.dokyumento.jp. All rights reserved.