13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文

ON DUPLICATE KEY UPDATE を指定したとき、UNIQUE インデックスまたは PRIMARY KEY に重複した値を発生させる行が挿入された場合は、MySQL によって古い行の UPDATE が実行されます。たとえば、カラム a が UNIQUE として宣言され、値 1 を含んでいる場合、次の 2 つのステートメントには同様の効果があります。

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

(これらの効果は、a が自動インクリメントカラムである InnoDB テーブルに対して同じではありません。自動インクリメントカラムを使用した場合、INSERT ステートメントは自動インクリメント値を増やしますが、UPDATE は増やしません。)

ON DUPLICATE KEY UPDATE 句には、カンマで区切られた、複数のカラム割り当てを含めることができます。

ON DUPLICATE KEY UPDATE を使用した場合、行ごとの影響を受けた行の値は、その行が新しい行として挿入された場合は 1、既存の行が更新された場合は 2、既存の行がその現在の値に設定された場合は 0 です。mysqld への接続時に CLIENT_FOUND_ROWS フラグを mysql_real_connect() に指定すると、既存の行がその現在の値に設定された場合の影響を受けた行の値は (0 ではなく) 1 になります。

カラム b も一意である場合、INSERT は、代わりに次の UPDATE ステートメントと同等です。

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

a=1 OR b=2 が複数の行に一致する場合は、1 つの行だけが更新されます。一般に、一意のインデックスが複数含まれているテーブルに対して ON DUPLICATE KEY UPDATE 句を使用することは避けるようにしてください。

UPDATE 句で VALUES(col_name) 関数を使用して、INSERT ... ON DUPLICATE KEY UPDATE ステートメントの INSERT 部分からカラム値を参照できます。つまり、ON DUPLICATE KEY UPDATE 句にある VALUES(col_name) は、重複キーの競合が発生していない場合に挿入される col_name の値を参照します。この関数は、複数の行を挿入する際に特に役立ちます。VALUES() 関数は、INSERT ... UPDATE ステートメントの中でだけ意味を持ち、そうでなければ NULL を返します。例:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

そのステートメントは、次の 2 つのステートメントと同一です。

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

テーブルに AUTO_INCREMENT カラムが含まれているときに、INSERT ... ON DUPLICATE KEY UPDATE で行を挿入または更新した場合、LAST_INSERT_ID() 関数は AUTO_INCREMENT 値を返します。

ON DUPLICATE KEY UPDATE を使用している場合、DELAYED オプションは無視されます。

INSERT ... SELECT ステートメントの結果は SELECT からの行の順序に依存し、またこの順序を常に保証することはできないため、ロギング時に、INSERT ... SELECT ON DUPLICATE KEY UPDATE ステートメントがマスターとスレーブで異なる可能性があります。そのため、MySQL 5.6.4 以降では、INSERT ... SELECT ON DUPLICATE KEY UPDATE ステートメントには、ステートメントベースのレプリケーションには安全でないというフラグが付けられます。この変更により、このようなステートメントは、ステートメントベースモードを使用しているときはログ内に警告を生成し、MIXED モードを使用しているときは行ベース形式を使用してログに記録されます。さらに、MySQL 5.6.6 からは、一意のキーまたは主キーが複数含まれているテーブルに対する INSERT ... ON DUPLICATE KEY UPDATE ステートメントも安全ではないとしてマークされます。(Bug #11765650、Bug #58637) セクション17.1.2.1「ステートメントベースおよび行ベースレプリケーションのメリットとデメリット」も参照してください。

MySQL 5.6.6 より前は、テーブルレベルのロックを採用した MyISAM などのストレージエンジンを使用しているパーティション化されたテーブルに対する INSERT ... ON DUPLICATE KEY UPDATE によって、そのテーブルのすべてのパーティションがロックされました。(これは、行レベルロックを採用した InnoDB などのストレージエンジンを使用しているテーブルでは発生しておらず、現在も発生しません。)MySQL 5.6.6 以降では、このようなステートメントでは、パーティション化キーカラムが更新されたパーティションのみがロックされます。詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。