AUTO_INCREMENTの値が型の上限値に達した際の振る舞いが気になったので、いくつか調べてみました。
AUTO_INCREMENTのドキュメントでは情報が足りませんでした。
例えば、一意性さえ確保できれば良いテーブルのidが上限に達した際に、1から順に再度振り直したくなるようなことがあるかもしれません。（アンチパターンの香り）
影響範囲の調査とか調整とかの工数を考えると、明らかにやるべきでない操作ですが、やむを得ない事情がある場合だってあるかもしれません（弊社には今の所ありません。良かった）。そんなときのために、知見をメモしておきます。
前提
- UNSIGNED INT 型の上限値: 4294967295
- UNSIGNED INT型のidカラムにAUTO_INCREMENTを指定
検証
idが上限値に達したテーブルに対し、さらにINSERTを実施する
| id | data |
| 10 | hoge |
| 11 | hoge |
| 4294967294 | hoge |
| 4294967295 | hoge |
INSERT INTO `id_test_table` (`id`, `data`) VALUES (NULL, 'hoge');#1062 - '4294967295' は索引 'PRIMARY' で重複しています。重複エラーが出て、テーブルにデータは入りません。
idを指定してINSERTする
INSERT INTO `id_test_table` (`id`, `data`) VALUES (1, 'hoge');| id | data |
| 1 | hoge |
| 10 | hoge |
| 11 | hoge |
| 4294967294 | hoge |
| 4294967295 | hoge |
重複が無ければ、問題なくINSERTできます。
この状態で再度INSERTした場合、次に割り当てられるidはなんでしょうか。
INSERT INTO `id_test_table` (`id`, `data`) VALUES (NULL, 'hoge');#1062 - '4294967295' は索引 'PRIMARY' で重複しています。AUTO_INCREMENTの値がリセットされるわけではないため、都合よく「2」などにはならず、当然のように重複エラーとなります。
こんな操作で「2」が入ったら、それこそバグの原因になってしまうので幸い。
AUTO_INCREMENTの値をリセットする
それでは、今度はテーブルのauto incrementをリセットしてみましょう。
ALTER TABLE tbl_name AUTO_INCREMENT = value; でリセットできます。
ALTER TABLE id_test_table AUTO_INCREMENT = 2;テーブル情報から、現状のauto incrementの値を確認します。
SELECT auto_increment FROM information_schema.tables WHERE table_name = 'id_test_table';auto_increment 4294967295ダメみたいですね。
データを一部消してAUTO_INCREMENTの値をリセットする
レコードを一部DELETEしました。
| id | data |
| 1 | hoge |
| 10 | hoge |
| 11 | hoge |
この状態で、auto incrementの値をリセットしてみます。
ALTER TABLE id_test_table AUTO_INCREMENT = 15;SELECT auto_increment FROM information_schema.tables WHERE table_name = 'id_test_table';auto_increment 15この場合は無事「15」になりました。
INSERT INTO `id_test_table` (`id`, `data`) VALUES (NULL, 'hoge');| id | data |
| 1 | hoge |
| 10 | hoge |
| 11 | hoge |
| 15 | hoge |
問題なくINSERTされます。
「指定したidの値より大きい値を持つレコードがある場合、変更できない」という仕様のようです。
15より低い値で入れてみると、やはり更新されません。
ALTER TABLE id_test_table AUTO_INCREMENT = 10;SELECT auto_increment FROM information_schema.tables WHERE table_name = 'id_test_table';auto_increment 16冒頭の「idの採番を1から振り直す」なんてことをしたい場合には、指定したい値以上のidを持つレコードを削除してやる必要がありそうです。とんでもねえなおい。
実際に「idの採番を1から振り直す」ようなケースでは、テーブルにid:1~上限値までのデータが全て残っているような状態は珍しいのではないでしょうか。
つまるところ、
「指定したい値以上のidを持つレコードを削除」は
「テーブルのレコードを全て削除」とほぼ同じ意味になりそうです。
とんでもねえなおい。
結論
テーブルの設計段階で型を十分に検討するべき。