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