MySQL サーバーを再起動するとAUTO_INCREMENT の値が戻る

InnoDB では AUTO_INCREMENT のカウンタはメモリ上に保持します。

14.6.5.1 従来の InnoDB の自動インクリメントロック https://dev.mysql.com/doc/refman/5.6/ja/innodb-auto-increment-traditional.html

InnoDB テーブルに AUTO_INCREMENT カラムを指定すると、InnoDB データディクショナリ内のテーブルハンドルに、カラムに新しい値を割り当てる際に使用される自動インクリメントカウンタと呼ばれる特別なカウンタが含まれます。このカウンタは、ディスク上には格納されず、メインメモリー内にのみ格納されます。

InnoDB では、ai_col という名前の AUTO_INCREMENT カラムを含むテーブル t に自動インクリメントカウンタを初期化するために、次のようなアルゴリズムが使用されます。サーバーの起動のあとで、テーブル t への最初の挿入をするために、InnoDB は次のステートメントと同等なものを実行します。

SELECT MAX(ai_col) FROM t FOR UPDATE;

何が問題かというと、最新のレコードを削除して MySQL サーバーを再起動すると AUTO_INCREMENT の値が巻き戻る。

>select * from test;

1
2
3
4
5

>show create table test;
CREATE TABLE `test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

>delete from test where id = 5;
>show create table test;
CREATE TABLE `test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

ここでMySQLを再起動

>show create table test;
CREATE TABLE `test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

まあこのように SELECT MAX(id) FROM test FOR UPDATE; の値に巻き戻る。 AUTO_INCREMENTした値が一意であることを見越して、
他のテーブルの外部キーにしてたりすると、整合性が取れなくなってしまう。

対策としては
・外部キー制約入れる
・AUTO_INCREMENTに頼らず、別に採番テーブルを用意する
・論理削除してレコードを delete しない(!)