ちとうちの子から
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 だとノード数に応じて飛んだはずだし)」、ってのはあるんですが。
記憶はしておいて損がないネタ、かも、しれない……誰かよかったら「仕組み側」を書いていただけると喜んで読みに行きますｗｗｗ
