MySQLでAUTO_INCREMENT列のID断片化（欠番）を処理する方法

MySQLにおける自動インクリメントIDの断片化とは

データベースで連番を管理する際、一意なIDの採番に関して問題が発生することがあります。特に、行を削除したりサーバーを再起動したりすると、AUTO_INCREMENTの値に「欠番（断片化）」が生じる場合があります。

MySQL 5.6のInnoDBでは、INSERT文にID列を明示的に含めることで、自動採番のIDを再利用（任意の値を指定）することが可能です。

IDに断片化（欠番）が発生する主なケース

• 最大の番号を持つIDを削除した場合
• MySQLサーバーを起動・停止した場合
• 新しいレコードを挿入した場合

実際の動作を確認する例

まず、auto_increment属性を使ってIDを自動採番するテーブルを作成します。

mysql> create table UniqueAutoId
   -> (
   -> id int auto_increment,
   -> Unique(id)
   -> );
Query OK, 0 rows affected (0.45 sec)

次に、テーブルにレコードを5件挿入します。

mysql> insert into UniqueAutoId values();
Query OK, 1 row affected (0.13 sec)

mysql> insert into UniqueAutoId values();
Query OK, 1 row affected (0.16 sec)

mysql> insert into UniqueAutoId values();
Query OK, 1 row affected (0.07 sec)

mysql> insert into UniqueAutoId values();
Query OK, 1 row affected (0.10 sec)

mysql> insert into UniqueAutoId values();
Query OK, 1 row affected (0.10 sec)

すべてのレコードを表示して確認してみましょう。

mysql> select *from UniqueAutoId;

実行結果は以下のとおりです。IDが1から5まで連番で採番されています。

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

最大IDを削除した場合の挙動

DELETE文を使って、id=5のレコード（最大のID）を削除します。

mysql> DELETE from UniqueAutoId where id=5;
Query OK, 1 row affected (0.14 sec)

レコードを表示すると、以下のようになります。

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

この状態で新しいレコードを挿入すると、削除されたid=5が再利用されることがあります。

途中のIDを削除した場合の挙動

今度は、連番の途中にあるid=2のレコードを削除してみます。

mysql> delete from UniqueAutoId where id=2;
Query OK, 1 row affected (0.15 sec)

再度、レコードを表示します。

+----+
| id |
+----+
|  1 |
|  3 |
|  4 |
+----+
3 rows in set (0.00 sec

まとめ

このように、途中のIDを削除するとid=2が欠けた状態（断片化）が発生します。AUTO_INCREMENTの値は一度採番されると基本的に巻き戻らないため、削除による欠番はそのまま残ります。アプリケーション側で連番の連続性を前提とした処理を行っている場合は、この挙動を十分に理解しておくことが重要です。