MySQL の INSERT ... ON DUPLICATE KEY UPDATE 構文 は、大量のデータを1度に INSERT, UPDATE するという構文です。

しかし、この構文を使っている箇所について、下記の事象が発生しました。

意図せず AUTO_INCREMENT が進む
int で保存できる最大値に達する (カンスト)
新しくデータが保存できなくなる

今回は、1. のところについての対処方法の共有をしたいと思います。

`INSERT ... ON DUPLICATE KEY UPDATE 構文` について

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文

この構文を使えば、1本のSQLで、INSERT、UPDATE を実行することができます。

意図せず AUTO_INCREMENT が進んでしまった理由を調べていたところ、

UPDATE 文になった場合でも、AUTO_INCREMENT が進むらしいということが分かりました。

それでは、検証を始めます。

確認環境

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.43, for osx10.13 (x86_64) using  EditLine wrapper

検証

準備

CREATE DATABASE test;

CREATE TABLE `tmp_a` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uniq` varchar(20) NOT NULL,
  `cnt` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx01` (`uniq`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

現在の AUTO_INCREMENT を確認します。

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

INSERT 文が発行される場合 (id カラムに値を指定しない)

変更前

mysql> SELECT * FROM tmp_a;
Empty set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

実行

mysql> INSERT INTO tmp_a (id, uniq, cnt)
    -> VALUES
    ->     (null,'u1', 1), (null,'u2', 2), (null,'u3', 3)
    -> ON DUPLICATE KEY UPDATE
    ->  uniq=VALUES(`uniq`),
    ->  cnt=VALUES(`cnt`)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

変更後

データが登録されたことが確認できます。

mysql>  SELECT * FROM tmp_a;
+----+------+-----+
| id | uniq | cnt |
+----+------+-----+
|  1 | u1   |   1 |
|  2 | u2   |   2 |
|  3 | u3   |   3 |
+----+------+-----+
3 rows in set (0.00 sec)

AUTO_INCREMENT も 3 進みました。

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

UPDATE 文が発行される場合 (id カラムに値を指定する)

変更前

mysql>  SELECT * FROM tmp_a;
+----+------+-----+
| id | uniq | cnt |
+----+------+-----+
|  1 | u1   |   1 |
|  2 | u2   |   2 |
|  3 | u3   |   3 |
+----+------+-----+
3 rows in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

実行

cnt を 1000 倍にして、更新します。

mysql> INSERT INTO tmp_a (id, uniq, cnt)
    -> VALUES
    ->     (1,'u1', 1000), (2,'u2', 2000), (3,'u3', 3000)
    -> ON DUPLICATE KEY UPDATE
    ->  uniq=VALUES(`uniq`),
    ->  cnt=VALUES(`cnt`)
    -> ;
Query OK, 6 rows affected (0.01 sec)
Records: 3  Duplicates: 3  Warnings: 0

変更後

cnt が更新されました。

mysql> SELECT * FROM tmp_a;
+----+------+------+
| id | uniq | cnt  |
+----+------+------+
|  1 | u1   | 1000 |
|  2 | u2   | 2000 |
|  3 | u3   | 3000 |
+----+------+------+
3 rows in set (0.00 sec)

AUTO_INCREMENT も変更されずそのままでした。

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              4 |
+----------------+
1 row in set (0.01 sec)

これは意図通り。

UPDATE 文が発行される場合 (id カラムに値を指定しない)

変更前

mysql> SELECT * FROM tmp_a;
+----+------+------+
| id | uniq | cnt  |
+----+------+------+
|  1 | u1   | 1000 |
|  2 | u2   | 2000 |
|  3 | u3   | 3000 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              4 |
+----------------+
1 row in set (0.01 sec)

実行

mysql> INSERT INTO tmp_a (id, uniq, cnt)
    -> VALUES
    ->     (null,'u1', 1111), (null,'u2', 2222), (null,'u3', 3333)
    -> ON DUPLICATE KEY UPDATE
    ->  uniq=VALUES(`uniq`),
    ->  cnt=VALUES(`cnt`)
    -> ;
Query OK, 6 rows affected (0.01 sec)
Records: 3  Duplicates: 3  Warnings: 0

変更後

mysql> SELECT * FROM tmp_a;
+----+------+------+
| id | uniq | cnt  |
+----+------+------+
|  1 | u1   | 1111 |
|  2 | u2   | 2222 |
|  3 | u3   | 3333 |
+----+------+------+
3 rows in set (0.01 sec)

AUTO_INCREMENT が先に進んでしまいました。

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              7 |
+----------------+
1 row in set (0.00 sec)

ここで少し休憩です。

INSERT ... ON DUPLICATE KEY UPDATE 構文 を使うとき
UPDATE する場合、AUTO_INCREMENT を進めないためには、下記カラムの値を指定する必要があることが分かりました。

id (AUTO_INCREMENT)
uniq (DUPLICATE KEY)

INSERT, UPDATE 文が発行される場合

今回の問題発生箇所での使われ方です。

変更前

mysql> SELECT * FROM tmp_a;
+----+------+------+
| id | uniq | cnt  |
+----+------+------+
|  1 | u1   | 1111 |
|  2 | u2   | 2222 |
|  3 | u3   | 3333 |
+----+------+------+
3 rows in set (0.01 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              7 |
+----------------+
1 row in set (0.00 sec)

実行

mysql> INSERT INTO tmp_a (id, uniq, cnt)
    -> VALUES
    ->     (1,'u1', 1000), (2,'u2', 2000), (3,'u3', 3000),
    ->     (null,'u4', 4), (null,'u5', 5), (null,'u6', 6)
    -> ON DUPLICATE KEY UPDATE
    ->  uniq=VALUES(`uniq`),
    ->  cnt=VALUES(`cnt`)
    -> ;
Query OK, 9 rows affected (0.02 sec)
Records: 6  Duplicates: 3  Warnings: 0

変更前

登録、更新されたデータは意図通りでした。

mysql> SELECT * FROM tmp_a;
+----+------+------+
| id | uniq | cnt  |
+----+------+------+
|  1 | u1   | 1000 |
|  2 | u2   | 2000 |
|  3 | u3   | 3000 |
|  7 | u4   |    4 |
|  8 | u5   |    5 |
|  9 | u6   |    6 |
+----+------+------+
6 rows in set (0.00 sec)

6 つ進んでしまいました！！！

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             13 |
+----------------+
1 row in set (0.01 sec)

どうやら、登録しようとしているデータ全部を更新しなければ、AUTO_INCREMENT が進むようです。

対処方法

id カラムを削除する

DB を利用している側の制約上、削除できない可能性もありますが、

id カラムを削除すれば AUTO_INCREMENT のことを考えなくて良くなります。

INSERT文とUPDATE文を分ける

UPDATE 文のみであれば、AUTO_INCREMENT が更新されないことが分かったので、

SQL を分割するのが良さそうです。