【MySQL】AUTO_INCREMENTの値が増える、飛ぶ、欠番が発生する

Tweet

Category MySQL 2011/09/10

問題

auto_incrementの値が飛び飛びになるんですけど。

答え

MySQL5.1.22以降、InnoDBのAUTO_INCREMENTの性能が向上しましたが、それに伴って、AUTO_INCREMENTの連番が飛ぶ場合が発生するようです。

プライマリーキー以外のユニークキーで重複してエラーとなる場合などです。

なお、MyISAMエンジンでは発生しません。

再現方法。

１、テーブルを作ります。ストレージエンジンはInnoDBにします。

CREATE TABLE test1 (
  id int PRIMARY KEY AUTO_INCREMENT,
  a int NOT NULL UNIQUE
) ENGINE = InnoDB;

２、1行入ります

insert into test1 (a) values (1);
+----+---+
| id | a |
+----+---+
|  1 | 1 |
+----+---+

３、プライマリキー以外のユニーク制約により挿入に失敗させます。

insert into test1 (a) values (1);
# ERROR 1062: Duplicate entry '1' for key 'a'
insert into test1 (a) values (1);
# ERROR 1062: Duplicate entry '1' for key 'a'
insert into test1 (a) values (1);
# ERROR 1062: Duplicate entry '1' for key 'a'
insert into test1 (a) values (1);
# ERROR 1062: Duplicate entry '1' for key 'a'

４、ここで1行挿入すると、AUTO_INCREMENTの値が飛んでます。

insert into test1 (a) values (2);
+----+---+
| id | a |
+----+---+
|  1 | 1 |
|  7 | 2 |
+----+---+

シーケンスなどがあったとしても、シーケンスから値を取得してしまったら後戻りはしないので、AUTO_INCREMENT値が増えるのを異常とは考えずに、まあそういうものということでいいじゃないか　と　思うことにします。

参考

説明と、設定による動作の変え方。

http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-auto-increment-configurable.html