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「パーティショニングとロック」を参照してください。