MySQLでUNIQUEに引っかかった時のAUTO INCREMENTの挙動

ちとうちの子から

MySQLでユニークキーとPKあるテーブルに対してON DUPLICATE KEY UPDATE張って、uniqueでの重複させるとauto increment値飛んじゃうんだね。。。

という興味深い話を聞いたので、早速実験。

CREATE TABLE test (
    id SERIAL,
    name VARCHAR(256) UNIQUE,
    PRIMARY KEY(id)
);

mysql> insert into test(name) values('n1');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
+----+------+
1 row in set (0.00 sec)

うんまぁ、ここまでは前提。

んでわ、確認。

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'

mysql> INSERT INTO test (name) values ('n2') ON DUPLICATE KEY UPDATE name='n3';
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
+----+------+
2 rows in set (0.00 sec)

……あ、ほんどだ飛んでる。

mysql> INSERT INTO test (name) values ('n3') ON DUPLICATE KEY UPDATE name='n4';
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
|  4 | n3   |
+----+------+
3 rows in set (0.00 sec)

……続けると飛んでないなぁ。

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> INSERT INTO test (name) values ('n4') ON DUPLICATE KEY UPDATE name='n5';
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
|  4 | n3   |
|  6 | n4   |
+----+------+
4 rows in set (0.00 sec)

うん、エラーが起きると飛ぶ………はて？

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> INSERT INTO test (name) values ('n5') ON DUPLICATE KEY UPDATE name='n6';
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
|  4 | n3   |
|  6 | n4   |
| 10 | n5   |
+----+------+
5 rows in set (0.00 sec)

あ、回数分とんだｗ

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n6');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO test (name) values ('n7') ON DUPLICATE KEY UPDATE name='n8';
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
|  4 | n3   |
|  6 | n4   |
| 10 | n5   |
| 13 | n6   |
| 14 | n7   |
+----+------+
7 rows in set (0.00 sec)

うん「UNIQUEでエラーするとエラー回数分飛んで、一回insertするとリセットされる」ぽい。

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n8');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  3 | n2   |
|  4 | n3   |
|  6 | n4   |
| 10 | n5   |
| 13 | n6   |
| 14 | n7   |
| 20 | n8   |
+----+------+
8 rows in set (0.00 sec)

しつこく確認＆別に「ON DUPLICATE KEY UPDATE」じゃなくても、飛ぶねぇ……

……あれ？じゃぁ、PKの時にも飛ぶのん？

mysql> drop table test;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE test (
    ->     id SERIAL,
    ->     name VARCHAR(256) UNIQUE,
    ->     PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> insert into test(name) values('n1');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test(name) values('n2');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  2 | n2   |
+----+------+
2 rows in set (0.00 sec)

前提作り直して。

mysql> insert into test(id, name) values(1, 'n3');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into test(id, name) values(1, 'n3');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into test(id, name) values(1, 'n3');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into test(name) values('n3');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  2 | n2   |
|  3 | n3   |
+----+------+
3 rows in set (0.00 sec)

ほむ、PKだと飛ばないのか。

mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n1');
ERROR 1062 (23000): Duplicate entry 'n1' for key 'name'
mysql> insert into test(name) values('n4');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | n1   |
|  2 | n2   |
|  3 | n3   |
|  9 | n4   |
+----+------+
4 rows in set (0.00 sec)

UNIQUEで飛ぶのを確認。

ほむ……まぁ「auto increment、そもそも"連番保証"とかしてないし(確か、MariaDB の Galera Cluster だとノード数に応じて飛んだはずだし)」、ってのはあるんですが。
記憶はしておいて損がないネタ、かも、しれない……誰かよかったら「仕組み側」を書いていただけると喜んで読みに行きますｗｗｗ

がるの健忘録

エンジニアでゲーマーで講師で占い師なおいちゃんのブログです。

MySQLでUNIQUEに引っかかった時のAUTO INCREMENTの挙動