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