【MySQL】レコードが無い場合→INSERT、ある場合→UPDATEする「INSERT ... ON DUPLICATE KEY UPDATE 構文」を試してみる。

DB&サーバ

fukasawa

2017.06.30

こんにちは、fukasawaです。梅雨ですね。

今回はINSERT ... ON DUPLICATE KEY UPDATE 構文についてです。
SQLでINSERTを実行する際、「ON DUPLICATE KEY UPDATE」をつけると

重複した行がない場合はINSERT
重複した行がある場合はUPDATE

を実行することができます。
※重複：プライマリーキーが重複してる or UNIQUEインデックスが重複してる

他のサイトを拝見した感じだと「集計バッチ等で使用すると便利」と書かれていたりするので、サンプルの方向性を間違えたかもと思い始めているのですが、このまま進めていきます。

13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文

※MySQL 5.6.34 で検証しています。

基本的なサンプル

サンプルを見てみます。このような感じでテストテーブルを作成しました。
アルパカ太郎さんは髭と背中の脱毛コースに申し込んでいます。

★ service_plans テーブルのカラム customer_id, category に対してユニークインデックスを付けています。

サンプル.sql

一度申し込みをしたものの、指毛と膝下の毛も気になってきたのでプランを変更したくなりました。
指毛と膝下の脱毛コースに申し込むと、既存のコースも無料プランになってさらにお得です。

コースとプランを変更するSQLを実行してみます。

    INSERT INTO service_plans (customer_id, category, plan) VALUES
      (101, '髭脱毛コース', '無料プラン'),
      (101, '背中脱毛コース', '無料プラン'),
      (101, '指脱毛コース', 'Aプラン'),
      (101, '膝下脱毛コース', 'Bプラン')
    ON DUPLICATE KEY UPDATE plan = VALUES (plan);

既に登録されていた「髭脱毛コース」と「背中脱毛コース」は無料プランに更新されました。（UPDATE）
登録されていなかった「指脱毛コース」と「膝下脱毛コース」は新しくレコードが追加されています。（INSERT）

VALUES(col_name) 関数を使うことで、INSERT 構文中の値をUPDATEの値として使うことができます。

UPDATEの条件を指定する

ON DUPLICATE KEY UPDATEの中でif()関数やCASE演算子を使用することができます。
「既に無料プランが設定されている場合は上書きしない」という条件で実行してみます。

if()

    INSERT INTO service_plans (customer_id, category, plan) VALUES
      (101, '髭脱毛コース', 'Aプラン'),
      (101, '背中脱毛コース', 'Bプラン'),
      (101, '指脱毛コース', 'Cプラン'),
      (101, '膝下脱毛コース', 'Cプラン')
    ON DUPLICATE KEY UPDATE plan = if(plan = '無料プラン', plan, values(plan));

CASE 演算子

    INSERT INTO service_plans (customer_id, category, plan) VALUES
      (101, '髭脱毛コース', 'Aプラン'),
      (101, '背中脱毛コース', 'Bプラン'),
      (101, '指脱毛コース', 'Cプラン'),
      (101, '膝下脱毛コース', 'Cプラン')
    ON DUPLICATE KEY UPDATE plan = (CASE WHEN plan = '無料プラン' THEN plan ELSE values(plan) END);

無料プランのコースはそのままで、それ以外のレコードが更新されました。

登録済みのレコードをもとにデータを更新する

背中脱毛コースに登録しているアンゴラ兎次郎さんが「アルパカ太郎さんと同じコースにしてくれ」と言ってきたとします。

アルパカ太郎さんと同じコースとプランに設定するSQLを実行します。

    INSERT INTO service_plans (customer_id, category, plan)
      SELECT
        102,
        category,
        plan
      FROM service_plans
      WHERE customer_id = 101
    ON DUPLICATE KEY UPDATE category = VALUES(category), plan = VALUES(plan);

アルパカ太郎さんのレコードを参照し、アンゴラ兎次郎さんの情報を書き換えました。
既に申し込んでいた背中脱毛コースは上書き（UPDATE）され、他のコースは新規レコードが追加（INSERT）されて、アルパカ太郎さんと同じ条件で登録されました。

※もし、アンゴラ兎次郎さんがアルパカ太郎さんが申し込んでいないコースに申し込んでいた場合、そのレコードは削除されないので、別途DELETEしてあげる必要があります。

REPLACE 構文

13.2.8 REPLACE 構文

MySQLには MySQL 拡張の REPLACE 構文というものがあって、INSERT ... ON DUPLICATE KEY UPDATE 構文と似たような動きをします。

REPLACE：

重複したレコードがあったら DELETE ＆ INSERT。無かったら INSERT する。

ON DUPLICATE KEY UPDATE :

重複したレコードがあったら UPDATE 。無かったら INSERT する。

REPLACE構文の方は、重複した行を一度削除してしまいます。

    REPLACE INTO service_plans (customer_id, category, plan) VALUES
      (101, '髭脱毛コース', '無料プラン'),
      (101, '背中脱毛コース', '無料プラン'),
      (101, '指脱毛コース', 'Aプラン'),
      (101, '膝下脱毛コース', 'Bプラン');