【MySQL】AUTO_INCREMENTの値が戻る＠InnoDBエンジンのテーブル

Tweet

Category MySQL 2010/07/21

MySQLサーバー再起動の場合

まずこれ。

サーバーが再起動すると、auto_incrementの値が select max(id)+1 になってしまう。

InnoDB は、ai_col を名づけた AUTO_INCREMENT カラムを含むテーブル T に自動インクリメント カウンタを初期化する為に、次のアルゴリズムを利用します:サーバの起動の後で、テーブル T への最初の挿入をする為に、InnoDB はこのステートメントと同等な物を実行します:

SELECT MAX(ai_col) FROM T FOR UPDATE;

（中略）

InnoDB はサーバが起動している限り、メモリ内の自動インクリメント カウンタを利用します。サーバが停止し再起動した時、先ほど説明があったように、InnoDB は、テーブルへの最初の INSERT に対する各テーブルのカウンタを再初期化します。

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

MySQLデータベース最適化（optimize table）の場合

他にもこれ。

auto_incrementのカラムを持つInnoDBエンジンのテーブルを作成

create table t (id int primary key auto_increment) engine=InnoDB;

5行追加、idが1から5のレコードを作る。

insert into t (id) values (0), (0), (0), (0), (0);

連番の大きい方を物理削除。

delete from t where id in (4, 5);

最適化を実行。

optimize table t;

また2行追加。auto_incrementに連番を取らせてみる

insert into t (id) values (0), (0);

結果

select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |    ← あれ？また4が使われた
|  5 |    ← あれ？また5が使われた
+----+

auto_increment にはシーケンスとしての動作を期待しているので（私は）、戻るのは大変具合が悪い。

InnoDBでも、一回発行した連番は二度と使わないようになってくれるとうれしい。

しかし MySQL5.5、5.6のマニュアルを見ても変わっていないようなので、仮にそうなるとしてもかなり先のことになりそうです。