MySQLでInnoDBエンジンを使うとAUTO INCREMENTの番号が飛ぶ現象

知ってる方からしたら今更何言ってんだって話だと思うんですが、データベースにあまり詳しくない自分からすると、かなり謎現象で悩まされました。

やりたかった事と発生した現象

PHPからMySQLへデータを入れていきたい。でもデータ重複を回避したいので、特定のカラムにユニークを設定。データを追加し重複のテストをしていた所、auto increment設定したIDが飛び飛びになってしまったという現象です。

テストテーブルを作って再現してみます

CREATE TABLE test (
  id int auto_increment primary key,
  name varchar(255) unique
) ENGINE = InnoDB;

idをauto increment、nameをuniqueにしたテーブルを作りテストしてみます。

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

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

まずは普通にレコードを1行挿入してみます。
selectするとnameに「yamada」が入っているのが分かります。

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

そして、わざと同じ「yamada」という値を入れ、insertすると、重複しているよ!というエラーが出ています。uniqueに設定しているので、特に問題ありませんね。

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

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

今度は重複しない「'tanaka'」を入れてみます。
その後selectしてみると、idが2じゃなくて7になっている・・・

番号が飛ぶ原因

調べてみると、MySQLのストレージエンジンとして採用されたInnoDBの仕様だと言うことがわかりました。高速化の為にそうしているらしいです。

欠番を作らない方法

「--innodb_autoinc_lock_mode=0」オプションを付けて起動
もしくは
my.cnfのmysqldに「innodb_autoinc_lock_mode=0」と記述
のどちらかで「従来」ロックモードというものになり、欠番が発生しなくなるそうです。

とはいえ、高速化の為にやっている事をわざわざやめるのもなぁ、、という感じもして悩ましい。。

参考にしたサイト

以下の記事が参考になりました。ありがたや。