こんにちは、物書きエンジニアです。
今回は、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の処理を一括で実行することができるのです。(めっちゃ便利)
より具体的な処理の流れとしてはこんな感じです。
- PRIMARY KEYもしくはUNIQUE制約が付けられているカラムのVALUESの値が既にデータベースにあるかをチェックする
- まだ無い場合はINSERT処理を実行して１行目のVALUESのデータを新たに挿入する
- 既に存在する場合はUPDATE処理を実行してUPDATEの後ろに指定してあるカラムのデータを更新する
UPDATEの部分では、以下のようにカラムの名前を指定した後にVALUES(カラム名)とすることで、１行目の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に指定した値が挿入されました。
次に、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の後ろに指定したデータだけが更新されましたね。
最後に、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のレコードが挿入されました。
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のデータが挿入されました。
次に、このレコードに対して更新処理をかけます。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のレコードが新たに挿入されています。
大量の更新処理が走るようなシステムでこの構文を使用すると、レコード数は全然増えていないのにidの数字はめっちゃ増えているみたいなことが起こりうるので、この辺の挙動には注意を払っておきましょう。
INSERT … ON DUPLICATE KEY UPDATEについて分かりやすくまとめてみた【おわりに】
今回はMySQLにおけるINSERT … ON DUPLICATE KEY UPDATE構文の基本および注意点について解説してみました。
少しでも参考になっていれば幸いです。
Twitterで発信中！
Twitterでプログラミング学習やブログ運営に関するTipsを発信しています。
ぜひフォローをお願いします！
【SQL】こちらの記事もおすすめです
