MySQLでテーブルのAUTO_INCREMENT値を小さな値にリセットする方法

MySQLのAUTO_INCREMENT値を低い値に設定するには？

InnoDBエンジンを使用している場合、テーブルのAUTO_INCREMENT値を現在より低い値に設定することはできません。低い値を設定したい場合は、エンジンをInnoDBからMyISAMに変更する必要があります。

注意： MyISAMエンジンでは、より低い値を設定することが可能です。本記事ではMyISAMを使用して解説します。

公式ドキュメントの記載内容

カウンタを、すでに使用された値以下の値にリセットすることはできません。
MyISAMの場合、指定した値がAUTO_INCREMENTカラムの現在の最大値以下であれば、
その値は「現在の最大値 + 1」にリセットされます。
InnoDBの場合、指定した値がカラムの現在の最大値より小さくても、
エラーは発生せず、現在のシーケンス値は変更されません。

上記のとおり、MyISAMでは一部のIDを削除した後に再度auto_incrementを設定すると、削除後の残存ID（最終ID）をもとに、それより低い値からIDが採番されます。

実践例：MyISAMでの動作確認

1. MyISAMエンジンでテーブルを作成

まず、MyISAMエンジンを指定してテーブルを作成します。

mysql> create table DemoTable (Id int NOT NULL AUTO_INCREMENT PRIMARY KEY)ENGINE='MyISAM';
Query OK, 0 rows affected (0.23 sec)

2. レコードを挿入

INSERTコマンドを使ってレコードを挿入します。

mysql> insert into DemoTable values();
Query OK, 1 row affected (0.04 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.03 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.03 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.02 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.05 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.08 sec)

SELECT文でテーブルのレコードを確認します。

mysql> select *from DemoTable;

実行結果は以下のとおりです。

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

3. ID 4〜6を削除

次に、ID 4、5、6を削除します。

mysql> delete from DemoTable where Id=4 or Id=5 or Id=6;
Query OK, 3 rows affected (0.06 sec)

もう一度すべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

削除後の出力結果は以下のとおりです。

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

4. 新しいAUTO_INCREMENT値を設定

ここで、新しいauto_increment値を設定します。

通常であれば次のIDは7から始まるはずですが、MyISAMエンジンを使用しているため、値は「現在の最大値 + 1」にリセットされます。つまり、現在の最大値は3なので、新しく採番されるIDは 3 + 1 = 4 となります。

以下がそのクエリです。

mysql> alter table DemoTable auto_increment=4;
Query OK, 3 rows affected (0.38 sec)
Records: 3 Duplicates: 0 Warnings: 0

5. 動作確認

再度レコードを挿入し、すべてのレコードを表示して、AUTO_INCREMENT値が4から始まっていることを確認しましょう。

mysql> insert into DemoTable values();
Query OK, 1 row affected (0.03 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.06 sec)
mysql> insert into DemoTable values();
Query OK, 1 row affected (0.02 sec)

SELECT文ですべてのレコードを表示します。

mysql> select *from DemoTable;

実行結果は以下のとおりです。新しいIDが4から始まっていることがわかります。

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

まとめ

InnoDBではAUTO_INCREMENT値を現在の最大値より低い値にリセットすることはできませんが、MyISAMではALTER TABLE構文を使うことで、削除済みのID領域を再利用できる場合があります。ただし、MyISAMはトランザクションやクラッシュリカバリの面でInnoDBに劣るため、本番環境でエンジンを変更する際は慎重に検討してください。