MySQLで重複を防ぎ、AUTO_INCLIMENTで欠番を出さずINSERTする方法

以前調べた方法で、重複自体は簡単に防げたのですが、AUTO INCREMENTの番号が飛んでしまう現象がありました。

MySQLで採用されているInnoDBエンジンの仕様なので、基本的にはどうしようもありません。特に実害も無いのですが、ただ単純に番号が飛ぶのが気持ち悪い。。
何か他の方法で解決できないかを調べるとネットで見つけました。

重複を防ぎ、AUTO_INCLIMENTで欠番を出さずINSERTする方法

まずはtestテーブルを作ってテストしてみます。

テストするtestテーブルのデータ

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

testテーブルには、yamadaというデータが1件入っている状態です。

次の構文を入力し、重複回避ができるかテストします

mysql> INSERT INTO test (name) SELECT 'yamada' FROM dual WHERE NOT EXISTS(SELECT * FROM test WHERE name = 'yamada');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

INSERT SELECTはSELECTした結果をINSERTする構文です。上記の例だと、yamadaをINSERTしようとしていますね。
次に続くdualというのはテーブルのデータを参照する必要が無い場合に指定するダミーのテーブル。
そしてNOT EXISTSは、その後に続くサブクエリが1行でも返したらFALSEになるというものです。

つまり、NOT EXISTSの後のサブクエリでヒットしたら、INSERT処理をしないというものになり、上記の例だと既に「name」に「yamada」が入っている(重複している)ので、サブクエリにヒットしINSERTされなかった。という結果になります。

続いて重複しないデータでテストしてみます

次は重複しないtanakaというデータを入力してテストします。

mysql> INSERT INTO test (name) SELECT 'tanaka' FROM dual WHERE NOT EXISTS(SELECT * FROM test WHERE name = 'tanaka');
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

無事、tanakaが挿入されているのがわかります。この方法だと、重複したデータを挿入しようとしても、INSERTがされないため、AUTO_INCLIMENTの番号が飛びません。

AUTO_INCLIMENTの番号が飛ばない!

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

mysql> INSERT INTO test (name) SELECT 'tanaka' FROM dual WHERE NOT EXISTS(SELECT * FROM test WHERE name = 'tanaka');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test (name) SELECT 'tanaka' FROM dual WHERE NOT EXISTS(SELECT * FROM test WHERE name = 'tanaka');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test (name) SELECT 'tanaka' FROM dual WHERE NOT EXISTS(SELECT * FROM test WHERE name = 'tanaka');
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test (name) SELECT 'suzuki' FROM dual WHERE NOT EXISTS(SELECT * FROM test WHERE name = 'suzuki');
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

わざと重複するtanakaを挿入し、その後suzukiを挿入してみますが、AUTO_INCLIMENTの番号が飛ばず、無事データの挿入が出来ているのがわかります。

SQLって難しい・・・

ネットで調べまくって、ようやく綺麗な形でINSERTできる方法を見つけました。SELECTでINSERTする方法やサブクエリなど全然知りませんでした。SQLって本当に奥が深く難しいですね。。
一度ちゃんと本を読んで勉強してみようかと思います。

参考にしたサイト

以下の記事に助けられました。ありがたや。