複合UNIQUEキーでも「INSERT ... ON DUPLICATE KEY UPDATE」構文は使える

MySQLには「INSERT ... ON DUPLICATE KEY UPDATE」という便利な構文がある。INSERTの内容がUNIQUE制約に引っかかる場合に指定カラムの値をUPDATEしてくれるものだ。

この構文について「複合UNIQUEキーの場合には使えない」とする記事を見た。

注意点としては、複合UNIQUEキーを指定しているテーブルでは（column2とcolumn1の組でUNIQUEなど）、 UPDATE文が複数レコードにマッチする可能性がありますので、UNIQUEキー制約が単一カラムにしかないテーブルでのみ使用します。内部的に実行されるUPDATE文のWHERE節がUNIQUEキーのORで判定するためです。（WHERE column1=’’ OR column2=’’）複合UNIQUEキー制約があるテーブルに対しては、次のREPLACE構文が使えます。

http://www.flatz.jp/archives/76

しかし、これは誤りだと思う。MySQL 5.1のマニュアルにはこうある。

例えば、もしカラム 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;

もしその行が新しいレコードとして挿入されると、行に影響される値は1となり、もし既存レコードが更新されると2になります。


もしカラム 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 条項を利用するのは避けるべきです。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文

つまり、カラム a とカラム b に対してそれぞれ独立のUNIQUEインデックスが張られている場合には利用を避けよということだ。

たとえば下記の場合、最後のINSERT時に更新される行は不定となる。

CREATE TABLE table_a (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  UNIQUE (a),
  UNIQUE (b)
);
INSERT INTO table_a (a, b, c) VALUES (1, 1, 0);
INSERT INTO table_a (a, b, c) VALUES (2, 2, 0);
INSERT INTO table_a (a, b, c) VALUES (1, 2, 0) ON DUPLICATE KEY UPDATE c = c + 1;

ひるがえって、カラム a とカラム b に対して複合UNIQUEインデックスが張られており、それ以外にUNIQUEインデックスが存在しないならば、上記のような問題は起こらない。

CREATE TABLE table_a (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  UNIQUE (a, b)
);
INSERT INTO table_a (a, b, c) VALUES (1, 1, 0);
INSERT INTO table_a (a, b, c) VALUES (1, 2, 0);
INSERT INTO table_a (a, b, c) VALUES (2, 1, 0);
INSERT INTO table_a (a, b, c) VALUES (2, 2, 0);
INSERT INTO table_a (a, b, c) VALUES (1, 2, 0) ON DUPLICATE KEY UPDATE c = c + 1;

mysql> select * from table_a;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 2 | 1 | 0 |
| 2 | 2 | 0 |
+---+---+---+
4 rows in set (0.00 sec)

複合UNIQUEキーがあっても「複数の固有インデックス」さえなければ「INSERT ... ON DUPLICATE KEY UPDATE」構文は使えるということだ。