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 の拡張であり、他のデータベースエンジンで利用できない。複数のデータベースエンジンに対応するようアプリケーションを作成する場合は、違いを吸収するような仕組みが必要になる

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

Time limit is exhausted. Please reload CAPTCHA.