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 を分割するのが良さそうです。