MySQL & MariaDBの世界

MySQLのAUTO_INCREMENTの確認・設定・リセット方法【SQLコマンド付き解説】

MySQL
佐川 直弘 | Naohiro Sagawa
By 佐川 直弘 | Naohiro Sagawa (運営者)
※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　
目次

MySQLのAUTO_INCREMENTとは？

AUTO_INCREMENTの基本

MySQLのAUTO_INCREMENTは、データベースのテーブルで自動的に番号を増加させる機能です。一般的には主キー（PRIMARY KEY）として使用され、手動でIDを指定しなくても、データの挿入ごとに連番が付与されます。

AUTO_INCREMENTの主な用途

  • 会員IDの自動生成：ユーザー登録時に一意のIDを付与
  • 注文番号の管理：注文ごとに自動的に番号を割り振る
  • 商品コードの付与：商品の一意な識別番号として使用

AUTO_INCREMENTを適切に活用することで、データの一意性を保ちつつ、管理の手間を削減できます。

MySQLでAUTO_INCREMENTの値を確認する方法【3つのSQLコマンド】

AUTO_INCREMENTの確認方法① SHOW TABLE STATUS を使う

SHOW TABLE STATUS LIKE 'テーブル名';

このコマンドを実行すると、AUTO_INCREMENTの現在の値がAuto_incrementカラムに表示されます。

AUTO_INCREMENTの確認方法② INFORMATION_SCHEMA.TABLES から取得する方法

SELECT AUTO_INCREMENT FROM information_schema.tables 
WHERE table_schema = 'データベース名' AND table_name = 'テーブル名';

この方法では、対象のテーブルに設定されているAUTO_INCREMENTの値を直接取得できます。

AUTO_INCREMENTの確認方法③ SHOW CREATE TABLE を使う方法

SHOW CREATE TABLE テーブル名;

このコマンドを実行すると、テーブルの作成時のDDL（データ定義言語）が表示され、その中にAUTO_INCREMENTの設定が含まれています。

AUTO_INCREMENTの設定と変更方法

新規テーブル作成時にAUTO_INCREMENTを設定する

新しいテーブルを作成する際に、カラムにAUTO_INCREMENTを設定するには、以下のSQLを使用します。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
);

既存テーブルのAUTO_INCREMENT値を変更する

既存のテーブルのAUTO_INCREMENT値を変更したい場合は、以下のコマンドを使用します。

ALTER TABLE users AUTO_INCREMENT = 1000;

このコマンドを実行すると、次に挿入されるデータのIDは1000からスタートします。

AUTO_INCREMENTの増分を変更する（MySQL 8.0以降）

デフォルトでは、AUTO_INCREMENTの値は1ずつ増加しますが、増加量を変更することも可能です。

SET @@auto_increment_increment = 5;

この設定を行うと、AUTO_INCREMENTの値が5ずつ増加します。（例: 1, 6, 11, …）

AUTO_INCREMENT使用時の注意点【データ破損を防ぐ】

AUTO_INCREMENTの最大値制限

AUTO_INCREMENTはカラムのデータ型によって最大値が決まります。例えば、INT型の場合、最大値は 2,147,483,647 です。これを超えると新しいIDを発行できなくなり、エラーになります。

対策

  • BIGINTを使用することで、より大きな値まで対応可能
  • 必要に応じてデータアーカイブやリセットを検討

データ削除後のAUTO_INCREMENTの動作

MySQLでは、レコードを削除してもAUTO_INCREMENTの値は自動的にはリセットされません。

DELETE FROM users WHERE id = 100;

このように特定のIDを削除しても、次に挿入される値はそのまま継続されます。

リセットする方法
TRUNCATE TABLEを使用すると、すべてのデータを削除し、AUTO_INCREMENTの値をリセットできます。

TRUNCATE TABLE users;

この操作を行うと、次の挿入時にIDが1から再スタートします。

トランザクションとAUTO_INCREMENT

MySQLでは、AUTO_INCREMENTの値はトランザクションのロールバック（ROLLBACK）をしても元に戻りません。

START TRANSACTION;
INSERT INTO users (name) VALUES ('Alice');
ROLLBACK;

この場合、データは挿入されませんが、AUTO_INCREMENTの値は増えたままとなります。これにより、IDの飛び番号が発生することがあります。

よくある質問（FAQ）

Q1: AUTO_INCREMENTの値が飛び飛びになるのはなぜ？

A: INSERT後にROLLBACKすると、番号がリセットされないため。削除や失敗した挿入も原因になります。

Q2: AUTO_INCREMENT の値をリセットするには？

A: TRUNCATE TABLE テーブル名; を実行するとリセットされます。

Q3: AUTO_INCREMENT の現在値を取得する方法は？

A: SHOW TABLE STATUSまたはINFORMATION_SCHEMA.TABLESを使用してください。

Q4: AUTO_INCREMENT の値が最大値を超えたらどうなる？

A: INT の最大値を超えるとエラーになるため、BIGINT に変更する必要があります。