MySQL で AUTO_INCREMENT の値をリセットする方法

テーブルのレコード数が増えてきて、レコードを大量に削除した後で、AUTO_INCREMENT の値を現在のレコードに合わせてリセットしたいような時ありませんか？

ここでは MySQL で AUTO_INCREMENT の値をリセットする方法をご説明します。

MySQL で現在の AUTO_INCREMENT の値を確認する

まずは、あるテーブルの現在の AUTO_INCREMENT の値を確認してみましょう。

test_db と言う名前のデータベースに、以下のようなシンプルな table1 というテーブルがあって、10 レコードが入っています。

CREATE TABLE table1 (
	id INT NOT NULL AUTO_INCREMENT,
    value1 VARCHAR(20) NULL,
	PRIMARY KEY (id)
);

テーブルの現在の AUTO_INCREMENT の値は次のクエリーで確認できます。

ANALYZE TABLE テーブル名;
  
SELECT 	AUTO_INCREMENT
FROM  	INFORMATION_SCHEMA.TABLES
WHERE 	TABLE_SCHEMA = 'データベース名'
		AND TABLE_NAME  = 'テーブル名';

まず、ANALYZE TABLE 文でテーブルの情報を最新のものに更新します。

そして、INFORMATION_SCHEMA.TABLES から、データベース名とテーブル名を指定して、AUTO_INCREMENT の値を取得しています。

以下のクエリーを実行して、test_db データベースの table1 テーブルの AUTO_INCREMENT の値を確認します。

ANALYZE TABLE table1;

SELECT 	AUTO_INCREMENT
FROM  	INFORMATION_SCHEMA.TABLES
WHERE 	TABLE_SCHEMA = 'test_db'
		AND TABLE_NAME  = 'table1';

AUTO_INCREMENT の値が 11 であることがわかりました。

MySQL で AUTO_INCREMENT の値をリセットする

ここで、table1 テーブルの id が 4 以上のレコードを削除しました。

現在のこのテーブルの AUTO_INCREMENT の値は 11 なので、次のデータをインサートすると id は 11 になります。

こんな状況で次の id の値を 11 ではなく、4 にしたいような時、AUTO_INCREMENT の値を現在の最大値 + 1 にリセットすることができます。

ALTER TABLE 文で、AUTO_INCREMENT に設定しようとした値が、AUTO_INCREMENT が設定されているカラムの最大値 + 1 より小さい場合は、AUTO_INCREMENT の値が最大値 + 1 に設定されます。

それを使って以下のクエリーで、AUTO_INCREMENT を現在の最大値 + 1 にリセットすることができます。

ALTER TABLE テーブル名 AUTO_INCREMENT = 1;

次のクエリーを実行して、table1 の AUTO_INCREMENT の値をリセットし、もう一度 AUTO_INCREMENT の値を確認してみます。

ALTER TABLE table1 AUTO_INCREMENT = 1;

AUTO_INCREMENT の値が 4 になっていますね。

試しに 1 レコード挿入すると、id に 4 が入りました。

以上、MySQL で AUTO_INCREMENT の値をリセットする方法をご説明しました。