MySQLの INSERT … ON DUPLICATE KEY UPDATE 構文について調べました

ども、たきたきでっす。

タイトルどおりなのですが、MySQLの INSERT … ON DUPLICATE KEY UPDATE 構文について調べてみました。 使用しているMySQLのバージョンは 5.6.36 です。

テスト用のテーブルを作成

CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `url` VARCHAR(255) NOT NULL,
  `cnt` INT NOT NULL,
  `created` DATETIME NOT NULL,
  `updated` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `url_UNIQUE` (`url` ASC));

URLごとのアクセス数を保持するテーブルを作成しました。 主キーはidで、urlに関してはUNIQUE制約を入れてあります。

INSERT … ON DUPLICATE KEY UPDATE を使わない場合

データを登録する場合、

INSERT INTO test (url, cnt, created, updated) values('https://www.takitaki.net/', 1, NOW(), NOW());

こんな感じになると思います。当然、もう一度同じデータを入れようとすると、urlにUNIQUE制約がついているので、

ERROR 1062 (23000): Duplicate entry 'https://www.takitaki.net/' for key 'url_UNIQUE'

このような感じでエラーになってしまいます。

実際に、testテーブルを使って、URLごとのカウントを取る場合ですが、

  1. testテーブルに対して、urlをキーにデータが存在しているかを確認する
  2. データが存在している場合、cntの値を1増やす
  3. データが存在していない場合、データを登録する

このような感じになると思います。 SELECT文の後にINSERTかUPDATEを行うことになると思います。

INSERT … ON DUPLICATE KEY UPDATE を使った場合

以下のSQL1文で済みます。

mysql> INSERT INTO test (url, cnt, created, updated) values('https://www.takitaki.net/', 1, NOW(), NOW()) ON DUPLICATE KEY UPDATE cnt = cnt + 1, updated = NOW();
Query OK, 2 rows affected (0.01 sec)

test テーブルを見てみると、

mysql> select * from test;
+----+---------------------------+-----+---------------------+---------------------+
| id | url                       | cnt | created             | updated             |
+----+---------------------------+-----+---------------------+---------------------+
|  1 | https://www.takitaki.net/ |   2 | 2020-03-10 14:06:13 | 2020-03-10 14:11:47 |
+----+---------------------------+-----+---------------------+---------------------+
1 row in set (0.00 sec)

cntの値が1増えて、updatedも更新されております。

INSERT … ON DUPLICATE KEY UPDATEを使うと新規データでも、

mysql> INSERT INTO test (url, cnt, created, updated) values('https://www.takitaki-tools.net/', 1, NOW(), NOW()) ON DUPLICATE KEY UPDATE cnt = cnt + 1, updated = NOW();
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+----+---------------------------------+-----+---------------------+---------------------+
| id | url                             | cnt | created             | updated             |
+----+---------------------------------+-----+---------------------+---------------------+
|  1 | https://www.takitaki.net/       |   2 | 2020-03-10 14:06:13 | 2020-03-10 14:11:47 |
|  4 | https://www.takitaki-tools.net/ |   1 | 2020-03-10 14:14:29 | 2020-03-10 14:14:29 |
+----+---------------------------------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)

問題なく登録されております。

ちなみに、idが2じゃなくてなんで4なの? と思うところかと思うのですが、まず DUPLICATE KEY UPDATEを使わなかった時にUNIQUE制約違反でエラーとなりましたがあの時に裏で1つ採番されております。
そして、先程のDUPLICATE KEY UPDATE文でcntの値を1増分させたSQLでも裏で1つ採番されているので合わせて2つidが採番されたことになり、次のINSERT文が正常に動作した時にidが4として登録されております。

注意事項

UNIQUE制約が複数カラムある場合、正常に動作しないです。 例を上げると

mysql> INSERT INTO test (id, url, cnt, created, updated) values(1, 'https://www.takitaki-tools.net/', 1, NOW(), NOW()) ON DUPLICATE KEY UPDATE cnt = cnt + 1, updated = NOW();
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;
+----+---------------------------------+-----+---------------------+---------------------+
| id | url                             | cnt | created             | updated             |
+----+---------------------------------+-----+---------------------+---------------------+
|  1 | https://www.takitaki.net/       |   3 | 2020-03-10 14:06:13 | 2020-03-10 14:39:29 |
|  4 | https://www.takitaki-tools.net/ |   1 | 2020-03-10 14:14:29 | 2020-03-10 14:14:29 |
+----+---------------------------------+-----+---------------------+---------------------+
2 rows in set (0.00 sec)

例としては微妙なのですが、idとurlにUNIQUE制約がある(idに関しては主キーなのでUNIQUE制約が入っている)場合、期待した動作にならない場合があります(本当であれば、takitaki-tools.netの方のcntが1増えて欲しかった)

また、裏で採番されてしまうためidがガンガン消費されてしまうので頻繁に使用されるテーブルの場合、int程度であれば最大値(2,147,483,647)に達してしまうかもしれません(その場合は、int型ではなく、bigint型にすれば9,223,372,036,854,775,807までいけるので心配不要になりそうですw)

最後に

万能ではないかもしれませんがバッチ処理でデータの存在チェック→INSERT or UPDATEを実行的な処理は多いと思うのでINSERT … ON DUPLICATE KEY UPDATE 構文は覚えておいて損は無いと思います。 というか全然知らなかった…w

参考にしたサイト

https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html 
公式サイト

コメント

タイトルとURLをコピーしました