MySQL で INSERT と UPDATE を1文で実現する～ ON DUPLICATE KEY UPDATE 編～

データベースにデータを登録する際に、INSERT と UPDATE か判断して実行しなければならないことがある。たとえば、カテゴリーの追加と更新を行うページで、ID が付与されている場合は ID をキーにして UPDATE、されていなければ INSERT を行うようなケースだ。前回は REPLACE 構文を使う方法を説明したが、REPLACE 構文は INSERT と DELETE を組み合わせて実行するため不便な点も多い。今回は、INSERT 構文で ON DUPLICATE KEY UPDATE を利用する方法を紹介する。

使い方

INSERT 構文で ON DUPLICATE KEY UPDATE を使う場合、INSERT 文と UPDATE 文を組み合わせたような SQL になる。

INSERT INTO
    table (
        id,
        category_name,
        update_count
    ) VALUES (
        1,
        'カテゴリー1',
        1
    ) ON DUPLICATE KEY UPDATE
        category_name = 'カテゴリー1',
        update_count = update_count + 1

ON DUPLICATE KEY UPDATE を使うと、テーブルの PRIMARY KEY か UNIQUE インデックスの値と、挿入しようとしているデータの値が同じであれば、UPDATE を実行する。
まとめると、

キーが一致する場合: 既存の行を UPDATE する
キーが一致しない場合: 新しい行を INSERT する

という動作になる。

注意点

ON DUPLICATE KEY UPDATE は INSERT か UPDATE しか行わないので、プログラマーは REPLACE 構文ほど注意を払う必要はない。ON DUPLICATE KEY UPDATE を利用する際の注意点をまとめてみた。

テーブルにプライマリーキーか、UNIQUE インデックスを用意しておかなければ ON DUPLICATE KEY UPDATE は利用できない
キーが複数行に一致する場合、MySQL は1行のみ更新する
AUTO_INCREMENT を使用しているテーブルで、キーが一致して UPDATE が実行された場合、LAST_INSERT_ID()関数は無意味になる。LAST_INSERT_ID() を使う場合は(id が AUTO_INCREMENT の列だとして)、「ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)」というような記述をする必要がある
ON DUPLICATE KEY UPDATE を利用する時は DELAYED オプションは無視される
ON DUPLICATE KEY UPDATE は MySQL の拡張であり、他のデータベースエンジンで利用できない。複数のデータベースエンジンに対応するようアプリケーションを作成する場合は、違いを吸収するような仕組みが必要になる