MySQL:auto_incrementが最大値まで達したとき

Tweet

Pocket

auto_incrementにしたカラムの型がintなら2147483647、bigintなら9223372036854775807がautoincrementの最大になる。

実際に最大値までauto_incrementを進めてみる。

 ALTER TABLE table1 AUTO_INCREMENT= 2147483647;

auto_incrementが最大値に達するともうそのテーブルには行を追加することはできなくなります。

oracleのシーケンスならCYCLEオプションがあるけどmysqlのauto_incrementにはそのような機能は無いらしい。

ではauto_incrementが最大値に達したとき、どうすればよいのか？

単純に

 ALTER TABLE table1 AUTO_INCREMENT= 1;

としても、table1に1より大きいキーが存在する限りauto_incrementは1に戻らない。

 DELETE FROM table1; --データを全部消す。
 ALTER TABLE table1 AUTO_INCREMENT= 1;

ならばauto_incrementは1に戻るので、循環シーケンスと似たような動きにするにはINSERTトリガーで

1. 対象テーブルのauto_increment値が最大に達しているかチェック。最大なら2.へ
2. 対象テーブルのデータを別テーブルに退避
3. 対象テーブルのデータをDELETE
4. ALTER TABLE～でauto_incrementを初期値に設定

という流れになるけど、INSERTの度に1.のチェックを走らせるのはちょっと嫌（1/2147483647の為に余計な処理を入れたくない）。また、退避後のデータをどう扱うかも微妙（UNIONするにしても別のキーが要るし・・・）だから
int autoincrementでキーの枯渇が心配ならbigint型にしたほうがよいと思う。
bigintの最大値は9223372036854775807。intの約42億倍もある。
これは1日に1億個のキーを消費しても2億年持つという計算になる。