INSERT ... ON DUPLICATE KEY UPDATE で重複時に更新する

INSERT しようとしたときに、主キーやユニークキーが重複していたら UPDATE したい、ということがあります。そんなときは INSERT ... ON DUPLICATE KEY UPDATE を使います。

基本構文

INSERT INTO テーブル名 (カラム1, カラム2) VALUES (値1, 値2)
ON DUPLICATE KEY UPDATE カラム2 = 値2;

主キーまたはユニークキーが重複したときだけ、UPDATE 部分が実行されます。

具体例

users テーブルで email がユニークキーだとします。

INSERT INTO users (email, name, login_count) VALUES ('tanaka@example.com', '田中', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1;

email が存在しなければ、新規行として INSERT される
email が既に存在すれば、login_count が 1 増える

VALUES() を使う

INSERT で指定した値を UPDATE 部分で参照するには、VALUES() 関数を使います。

INSERT INTO users (email, name, age) VALUES ('tanaka@example.com', '田中太郎', 30)
ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

これで、重複時には name と age が新しい値で更新されます。

VALUES(カラム名)

INSERT で指定した値を参照できる

カラム名のみ

既存の値を参照する

MySQL 8.0.20 以降の書き方

MySQL 8.0.20 以降では VALUES() は非推奨になり、代わりにエイリアスを使う書き方が推奨されています。

INSERT INTO users (email, name, age) VALUES ('tanaka@example.com', '田中太郎', 30) AS new
ON DUPLICATE KEY UPDATE name = new.name, age = new.age;

AS new で INSERT する値に別名をつけ、UPDATE 部分で new.カラム名 として参照します。

複数行の INSERT

複数行を一度に処理することもできます。

INSERT INTO users (email, name, login_count) VALUES 
  ('tanaka@example.com', '田中', 1),
  ('suzuki@example.com', '鈴木', 1),
  ('sato@example.com', '佐藤', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1;

各行について、重複があれば UPDATE、なければ INSERT が実行されます。

使いどころ

ログイン回数のカウント

初回ログインなら INSERT、2 回目以降なら login_count を増やす

データの同期

外部システムからデータを取り込むとき、あれば更新・なければ追加

この構文は MySQL 独自のものです。他のデータベースでは UPSERT や MERGE など、別の構文を使います。