SQL

INSERT … ON DUPLICATE KEY UPDATEについて分かりやすくまとめてみた【 MySQL】

INSERT ... ON DUPLICATE KEY UPDATEについて分かりやすくまとめてみた【 MySQL】

今回は、MySQLのINSERT … ON DUPLICATE KEY UPDATE構文について分かりやすくまとめてみます。

具体例を交えつつ書くので、参考にして頂けると幸いです。

 

INSERT … ON DUPLICATE KEY UPDATEについて分かりやすくまとめる【 MySQL】

INSERT … ON DUPLICATE KEY UPDATE構文とは一言でいうと、データによってINSERT処理とUPDATE処理を切り分けたい場合に使用する構文です。

 

INSERT … ON DUPLICATE KEY UPDATE構文は以下の形式で記述されます。

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

 

ここで行われているのは以下の3つの処理です。

  • 対象のレコードがあるかどうかをチェックする(SELECT文)
  • 対象のレコードが無かったらデータを挿入する(INSERT文)
  • 対象のレコードがあったらデータを更新する(UPDATE文)

 

つまり、INSERT … ON DUPLICATE KEY UPDATE構文を使うことで、上記のSELECT、INSERT、UPDATEの処理を一括で実行することができるのです。(めっちゃ便利)

 

より具体的な処理の流れとしてはこんな感じです。

  1. PRIMARY KEYもしくはUNIQUE制約が付けられているカラムのVALUESの値が既にデータベースにあるかをチェックする
  2. まだ無い場合はINSERT処理を実行して1行目のVALUESのデータを新たに挿入する
  3. 既に存在する場合はUPDATE処理を実行してUPDATEの後ろに指定してあるカラムのデータを更新する

 

UPDATEの部分では、以下のようにカラムの名前を指定した後にVALUES(カラム名)とすることで、1行目のVALUESで指定した値を取得してセットすることができます。

UPDATE カラム1の名前 = VALUES(カラム1の名前), カラム2の名前 = VALUES(カラム2の名前)

 

または、以下のように普通に値を指定することも可能です。

UPDATE カラム1の名前 = 1, カラム2の名前 = 'hogefuga'

 

INSERT … ON DUPLICATE KEY UPDATE構文を実際に使ってみる

言葉で説明しただけではイメージが掴みにくいと思うので、実際にINSERT … ON DUPLICATE KEY UPDATE構文を使ってみたいと思います。

 

今回はPHPUnitの中で上記の構文を使って実験してみました。

まずはデーターベースが空の状態で以下の構文を実行します。(今回の例では、idカラムがPRIMARY KEYです)

<?php

namespace Tests\Services;

use Tests\TestCase;
use Illuminate\Support\Facades\DB;

class SqlServiceTest extends TestCase
{
    /** @test */
    public function sqlを試す()
    {
        $sql = <<<SQL
            INSERT INTO users (id, name, email, password) 
            VALUES (1, 'taro', 'example@test.com', 'hogehoge@123') 
            ON DUPLICATE KEY
            UPDATE 
                name = VALUES(name),
                password = VALUES(password)
        SQL;

        // SQLの実行
        DB::statement($sql);
    }
}

 

以下のようにVALUESに指定した値が挿入されました。

VALUESに指定した値が挿入される

 

次に、idはそのままにname、email、passwordの値を変えてみます

予想としては、id(PRIMARY KEY)が1のデータは既にテーブルに存在するため、新たにデータが挿入されることはなく、UPDATEの後ろに指定したデータが更新されるはずです。

<?php

namespace Tests\Services;

use Tests\TestCase;
use Illuminate\Support\Facades\DB;

class SqlServiceTest extends TestCase
{
    /** @test */
    public function sqlを試す()
    {
        $sql = <<<SQL
            INSERT INTO users (id, name, email, password) 
            VALUES (1, 'taroko', 'example@test2.com', 'fugafuga@123') 
            ON DUPLICATE KEY
            UPDATE 
                name = VALUES(name),
                password = VALUES(password)
        SQL;

        // SQLの実行
        DB::statement($sql);
    }
}

 

新たにデータが挿入されることはなく、UPDATEの後ろに指定したデータだけが更新されましたね。

UPDATEの後ろに指定したデータだけが更新される

 

 

最後に、id以外の値はそのままに、idだけ2に変更してみます。

この場合はPRIMARY KEYが一致するデータがデータベースに存在しないため、VALUESに指定したデータが新たに挿入されるはずです。

<?php

namespace Tests\Services;

use Tests\TestCase;
use Illuminate\Support\Facades\DB;

class SqlServiceTest extends TestCase
{
    /** @test */
    public function sqlを試す()
    {
        $sql = <<<SQL
            INSERT INTO users (id, name, email, password) 
            VALUES (2, 'taroko', 'example@test2.com', 'fugafuga@123') 
            ON DUPLICATE KEY
            UPDATE 
                name = VALUES(name),
                password = VALUES(password)
        SQL;

        // SQLの実行
        DB::statement($sql);
    }
}

 

新たにidが2のレコードが挿入されました。

idが2のレコードが挿入される

 

INSERT … ON DUPLICATE KEY UPDATEのオートインクリメントの挙動について【注意が必要】

以上でINSERT … ON DUPLICATE KEY UPDATE構文についての説明は終わりですが、最後にこの構文の挙動として知っておいた方がいいことがあるのでお伝えしておきます。

 

それは、オートインクリメント時の挙動です。

オートインクリメント指定のidをもつデータに対してINSERT … ON DUPLICATE KEY UPDATEを実行したとき、INSERT処理が走る場合は単にレコードが増えるごとにidも一つずつ増えていくだけです。

これは何の問題もありません。

 

問題なのは、アップデート処理が走った場合です。

アップデート処理が走った場合、次にインサート処理が走った場合のidの値は、以前のUPDATE時に、オートインクリメントによりidの値が増加したものとして決定されます。

例えば、最初のINSERTによりid1のデータがテーブルに入ったとして、このレコードに対してUPDATE処理を実行した後に新たなレコードをINSERTしたら、そのときにオートインクリメントで指定されるidの値は2ではなく3だということです。

 

こちらも、先程と同様、実際に挙動を確かめてみましょう。

まずは以下のコードによりid1のデータを挿入します。(この例では、emailがunique制約を持っています)

<?php

namespace Tests\Services;

use Tests\TestCase;
use Illuminate\Support\Facades\DB;

class SqlServiceTest extends TestCase
{
    /** @test */
    public function sqlを試す()
    {
        $sql = <<<SQL
            INSERT INTO users (name, email, password) 
            VALUES ('taro', 'example@test.com', 'hogehoge@123') 
            ON DUPLICATE KEY
            UPDATE 
                name = VALUES(name),
                password = VALUES(password)
        SQL;

        // SQLの実行
        DB::statement($sql);
    }
}

 

id1のデータが挿入されました。

id1のデータが挿入される

 

次に、このレコードに対して更新処理をかけます。emailがunique制約を持っているため、emailの値を同じにしてnameもしくはpasswordの名前を変えれば更新処理が走るはずです。

以下のコードを実行します。

<?php

namespace Tests\Services;

use Tests\TestCase;
use Illuminate\Support\Facades\DB;

class SqlServiceTest extends TestCase
{
    /** @test */
    public function sqlを試す()
    {
        $sql = <<<SQL
            INSERT INTO users (name, email, password) 
            VALUES ('tarodesu', 'example@test.com', 'hogehoge@1234567') 
            ON DUPLICATE KEY
            UPDATE 
                name = VALUES(name),
                password = VALUES(password)
        SQL;

        // SQLの実行
        DB::statement($sql);
    }
}

 

更新処理が実行されました。

更新処理の実行

 

次に、emailの値を変更することにより、INSERT処理を走らせてみます。

オートインクリメントにより指定されるidの値に注目して下さい。

<?php

namespace Tests\Services;

use Tests\TestCase;
use Illuminate\Support\Facades\DB;

class SqlServiceTest extends TestCase
{
    /** @test */
    public function sqlを試す()
    {
        $sql = <<<SQL
            INSERT INTO users (name, email, password) 
            VALUES ('tarodesu', 'example@hogetest.com', 'hogehoge@1234567') 
            ON DUPLICATE KEY
            UPDATE 
                name = VALUES(name),
                password = VALUES(password)
        SQL;

        // SQLの実行
        DB::statement($sql);
    }
}

 

普通に考えたらid2のレコードが挿入されるはずですが、id3のレコードが新たに挿入されています。

id3のレコードが新たに挿入される

 

 

大量の更新処理が走るようなシステムでこの構文を使用すると、レコード数は全然増えていないのにidの数字はめっちゃ増えているみたいなことが起こりうるので、この辺の挙動には注意を払っておきましょう。

 

おわりに

今回はMySQLにおけるINSERT … ON DUPLICATE KEY UPDATE構文の基本および注意点について解説してみました。

少しでも参考になっていれば幸いです。

COMMENT

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

CAPTCHA