ON DUPLICATE KEY UPDATEをAUTO_INCREMENTのテーブルには発行しない方がいい

AUTO_INCREMENTはINSERTするたびにカウントアップして発番してくれるが、ON DUPLICATE KEY UPDATEを使用してINSERTではなく既存行のUPDATEになった場合もカウントアップされてしまう。実際にINSERTしている行数はたいしたことがないのに、ON DUPLICATE KEY UPDATEされる回数が多いせいで、AUTO_INCREMENT値が大きくなってしまい、INTなどの比較的小さい型にしていた場合にAUTO_INCREMENTが上限値に達してしまう。

以下のテーブルをもとにINSERTされていないのにAUTO_INCREMENT値が増えていることを実際に確認する。

mysql> CREATE TABLE on_dup_autoinc (id INT AUTO_INCREMENT PRIMARY KEY, ukey CHAR(1) UNIQUE KEY, cnt INT DEFAULT 1);
Query OK, 0 rows affected (0.01 sec)

mysql> DESC on_dup_autoinc;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| ukey  | char(1) | YES  | UNI | NULL    |                |
| cnt   | int(11) | YES  |     | 1       |                |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

AUTO_INCREMENTの初期値はデフォルトの1となっている。

mysql> pager grep Auto_increment
PAGER set to 'grep Auto_increment'
mysql> SHOW TABLE STATUS LIKE 'on_dup_autoinc'\G
Auto_increment: 1
1 row in set (0.00 sec)

mysql> nopager
PAGER set to stdout

まずはテーブルにデータを2件登録する。すると想定通り次に採番される値は3になる。

mysql> INSERT INTO on_dup_autoinc(ukey) VALUES('A');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO on_dup_autoinc(ukey) VALUES('B');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM on_dup_autoinc;
+----+------+------+
| id | ukey | cnt  |
+----+------+------+
|  1 | A    |    1 |
|  2 | B    |    1 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> pager grep Auto_increment
PAGER set to 'grep Auto_increment'
mysql> SHOW TABLE STATUS LIKE 'on_dup_autoinc'\G
Auto_increment: 3
1 row in set (0.00 sec)

mysql> nopager
PAGER set to stdout

ここで既にあるukey = 'A'のデータを再度INSERTする。既にあるので、ON DUPLICATE KEY UPDATE句が適用されて、cntが現在の値 + 1 となる。

mysql> INSERT INTO on_dup_autoinc(ukey) VALUES('A') ON DUPLICATE KEY UPDATE cnt = cnt + 1;
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM on_dup_autoinc;
+----+------+------+
| id | ukey | cnt  |
+----+------+------+
|  1 | A    |    2 |
|  2 | B    |    1 |
+----+------+------+
2 rows in set (0.00 sec)

INSERTされていないのでAUTO_INCREMENT値が3のままかと思いきや、カウントアップされて4になっている。

mysql> pager grep Auto_increment
PAGER set to 'grep Auto_increment'
mysql> SHOW TABLE STATUS LIKE 'on_dup_autoinc'\G
Auto_increment: 4
1 row in set (0.00 sec)

mysql> nopager
PAGER set to stdout

実際に存在しないukeyをINSERTしてみると、idが4で発番されて、3が欠番になっている。

mysql> INSERT INTO on_dup_autoinc(ukey) VALUES('C') ON DUPLICATE KEY UPDATE cnt = cnt + 1;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM on_dup_autoinc;
+----+------+------+
| id | ukey | cnt  |
+----+------+------+
|  1 | A    |    2 |
|  2 | B    |    1 |
|  4 | C    |    1 |
+----+------+------+
3 rows in set (0.00 sec)