MySQLでテーブルのデータを削除した後にAUTO_INCREMENTを1から再開させる方法

MySQLでデータ削除後にAUTO_INCREMENTを1に戻す方法

MySQLでは、DELETE文でレコードを削除してもAUTO_INCREMENTの値はリセットされず、次に挿入されるレコードには続きの番号が割り当てられます。自動インクリメントの値を1に戻したい場合は、TRUNCATE TABLEコマンドを使用します。本記事では、実際のSQL例を使ってその動作を確認していきます。

検証用テーブルの作成

まず、テーブルを作成します。

mysql> create table DemoTable1796
    (
    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StudentName varchar(20)
    );
Query OK, 0 rows affected (0.00 sec)

テストデータの挿入

INSERTコマンドを使って、いくつかのレコードを挿入します。

mysql> insert into DemoTable1796(StudentName) values('Chris Brown');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1796(StudentName) values('David Miller');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1796(StudentName) values('John Doe');
Query OK, 1 row affected (0.00 sec)

レコードの確認

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

mysql> select * from DemoTable1796;

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

+-----------+--------------+
| StudentId | StudentName  |
+-----------+--------------+
|         1 | Chris Brown  |
|         2 | David Miller |
|         3 | John Doe     |
+-----------+--------------+
3 rows in set (0.00 sec)

DELETE文で削除した場合の挙動

まず、DELETE文ですべてのレコードを削除してみます。

mysql> delete from DemoTable1796;
Query OK, 3 rows affected (0.00 sec)

この状態で新しいレコードを挿入するとどうなるでしょうか。

mysql> insert into DemoTable1796(StudentName) values('John Doe');
Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1796;
+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
|         4 | John Doe    |
+-----------+-------------+
1 row in set (0.00 sec)

ご覧のとおり、DELETE文で全行を削除しても、新しく挿入されたレコードには「4」というIDが割り当てられました。これはDELETE文ではAUTO_INCREMENTのカウンターがリセットされないためです。

TRUNCATE TABLEでカウンターをリセットする

AUTO_INCREMENTを1に戻すには、TRUNCATE TABLEコマンドを使用します。

mysql> truncate table DemoTable1796;
Query OK, 0 rows affected (0.00 sec)

TRUNCATE実行後にもう一度レコードを挿入して確認してみましょう。

mysql> insert into DemoTable1796(StudentName) values('John Doe');
Query OK, 1 row affected (0.00 sec)
mysql> select * from DemoTable1796;
+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
|         1 | John Doe    |
+-----------+-------------+
1 row in set (0.00 sec)

今度は新しいレコードに「1」から採番されていることが確認できました。TRUNCATE TABLEはテーブル内のすべての行を削除すると同時に、AUTO_INCREMENTカウンターも初期化するためです。

補足：TRUNCATE以外のリセット方法

なお、WHERE句で一部の行だけを削除したい場合など、TRUNCATEが使えないケースでは、ALTER TABLE文で明示的にカウンターをリセットすることも可能です。

mysql> ALTER TABLE DemoTable1796 AUTO_INCREMENT = 1;

用途に応じてTRUNCATE TABLEとALTER TABLEを使い分けることで、柔軟にIDの採番を管理できます。