auto_incrementの番号を振り直す方法

2021年10月28日2021年10月29日

auto_incrementを設定したカラムの番号を1からに戻したいときなど、指定の番号から振り直す方法をまとめました。

auto_incrementはレコードを全件削除しても自動で振り直されない

あるテーブルに登録した全レコードを全件削除しても、新規で追加したレコードは前回から引き継いだ連番で挿入されていきます。

たとえば、下記のようなユーザー情報を扱うテーブルがあったとします。

mysql> describe users;
+-------------+--------------+------+-----+-------------------+-----------------------------+
| Field       | Type         | Null | Key | Default           | Extra                       |
+-------------+--------------+------+-----+-------------------+-----------------------------+
| id          | int          | NO   | PRI | NULL              | auto_increment              |
| name        | varchar(100) | YES  |     | NULL              |                             |
| created_at  | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED           |
| modified_at | timestamp    | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
+-------------+--------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

実際に数件のデータを挿入します。

mysql> insert into users values (0, '鈴木', current_timestamp, current_timestamp);
mysql> insert into users values (0, '田中', current_timestamp, current_timestamp);
mysql> select * from users;
+----+--------+---------------------+---------------------+
| id | name   | created_at          | modified_at         |
+----+--------+---------------------+---------------------+
|  1 | 鈴木   | 2021-10-28 01:02:26 | 2021-10-28 01:02:26 |
|  2 | 田中   | 2021-10-28 01:02:34 | 2021-10-28 01:02:34 |
+----+--------+---------------------+---------------------+
2 rows in set (0.00 sec)

このあと、挿入したデータをいったん消したあと再挿入してみます。

mysql> delete from users;
mysql> insert into users values (0, '佐々木', current_timestamp, current_timestamp);
mysql> select * from users;
+----+-----------+---------------------+---------------------+
| id | name      | created_at          | modified_at         |
+----+-----------+---------------------+---------------------+
|  3 | 佐々木    | 2021-10-28 01:05:41 | 2021-10-28 01:05:41 |
+----+-----------+---------------------+---------------------+
1 row in set (0.00 sec)

IDは1で振り直されず、3で登録されました。

auto_incrementを振り直したいときは、別途設定が必要です。

auto_incrementを指定の番号から振り直す方法

auto_incrementを振り直すときは、以下で可能です。

mysql> alter table users auto_increment = 1;

1から振り直すように設定しました。

もし「10からにしたい」場合は、auto_increment = 1の部分をauto_increment = 10に変更すればOK。

実際に既存のデータを削除した上で、auto_incrementを1から振り直し、データを挿入してみます。

mysql> delete from users;
mysql> alter table users auto_increment = 1;

mysql> insert into users values (0, '加藤', current_timestamp, current_timestamp);

mysql> select * from users;
+----+--------+---------------------+---------------------+
| id | name   | created_at          | modified_at         |
+----+--------+---------------------+---------------------+
|  1 | 加藤   | 2021-10-28 01:11:28 | 2021-10-28 01:11:28 |
+----+--------+---------------------+---------------------+
1 row in set (0.00 sec)

無事にIDが1になっていることが確認できました。