MySQL で ON DUPLICATE KEY UPDATE を使ってたら、カンストした話し

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

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

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

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

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

[MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文](https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html)

この構文を使えば、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 を分割するのが良さそうです。