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

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

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

効果はまったく同じではありません: a が自動インクリメントカラムである InnoDB テーブルの場合、INSERT ステートメントは自動インクリメント値を増やしますが、UPDATE は増やしません。

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

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

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

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

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

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

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

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

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

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

MySQL 8.0.19 以降では、VALUES または SET 句の後に AS キーワードを付けて、挿入する行のエイリアス (オプションでそのカラムの 1 つ以上) を使用できます。 行エイリアス new を使用すると、以前に VALUES() を使用して新しいカラム値にアクセスしていたステートメントを、次に示す形式で記述できます:

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

また、カラムエイリアス m、n および p を使用する場合は、代入句で行エイリアスを省略して、次のような同じステートメントを記述できます:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

この方法でカラムエイリアスを使用する場合、代入句で直接使用しない場合でも、VALUES 句の後に行エイリアスを使用する必要があります。

前述のように、SET 句で行およびカラムのエイリアスを使用することもできます。 前述の 2 つの INSERT ... ON DUPLICATE KEY UPDATE ステートメントで、VALUES のかわりに SET を使用すると、次のように実行できます:

INSERT INTO t1 SET a=1,b=2,c=3 AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

行のエイリアスは、テーブルの名前と同じにできません。 カラムエイリアスが使用されていない場合、またはカラム名と同じ場合は、ON DUPLICATE KEY UPDATE 句の行エイリアスを使用して区別する必要があります。 カラムのエイリアスは、適用される行のエイリアスに関して一意である必要があります (つまり、同じ行のカラムを参照するカラムのエイリアスは同じにできません)。

INSERT ... SELECT ステートメントの場合、ON DUPLICATE KEY UPDATE 句で参照できる SELECT クエリー式の許容形式に関して、次のルールが適用されます:

UNION からのカラムの参照はサポートされていません。 この制限を回避するには、UNION を導出テーブルとして書き換え、その行を単一テーブルの結果セットとして処理できるようにします。 たとえば、次のステートメントはエラーを生成します:

INSERT INTO t1 (a, b)
  SELECT c, d FROM t2
  UNION
  SELECT e, f FROM t3
ON DUPLICATE KEY UPDATE b = b + c;

かわりに、UNION を導出テーブルとしてリライトする同等のステートメントを使用します:

INSERT INTO t1 (a, b)
SELECT * FROM
  (SELECT c, d FROM t2
   UNION
   SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;

クエリーを導出テーブルとしてリライトする方法では、GROUP BY クエリーからカラムを参照することもできます。

INSERT ... SELECT ステートメントの結果は SELECT からの行の順序に依存するため、この順序は常に保証されるわけではないため、ソースとスレーブの INSERT ... SELECT ON DUPLICATE KEY UPDATE ステートメントの相違を記録するときに可能です。 したがって、ステートメントベースのレプリケーションでは、INSERT ... SELECT ON DUPLICATE KEY UPDATE ステートメントに安全でないというフラグが付けられます。 このようなステートメントは、ステートメントベースのモードの使用時にエラーログに警告を生成し、MIXED モードの使用時に行ベースの形式を使用してバイナリログに書き込まれます。 複数の一意キーまたは主キーを持つテーブルに対する INSERT ... ON DUPLICATE KEY UPDATE ステートメントも安全でないとマークされます。 (Bug #11765650、Bug #58637)

セクション17.2.1.1「ステートメントベースおよび行ベースレプリケーションのメリットとデメリット」も参照してください。