MySQLでテーブルに自動採番する(AUTO_INCREMENT)

MySQLでテーブルに自動採番する方法について書いています。

テーブル作成時にAUTO_INCREMENTを使うと、指定したカラムに対して、番号を自動で採番してくれます。
また、AUTO_INCREMENTの値を確認する方法とリセットについても記載しました。

MySQLのバージョン8.0.32で、動作を検証しています。

テーブルに自動採番するには？

テーブルに自動採番のカラムを作成するにはAUTO_INCREMENTを使います。
例えば、下記のようにテーブルを作成します。

CREATE TABLE fruits (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(256),
  price INT,
  PRIMARY KEY (id)
);

果物の名前と価格を管理するfruitsテーブルを作成しました。
1カラム目に型をBIGINTを指定して、AUTO_INCREMENTにしています。

こうすることで、自動でidカラムに対して、数値がカウントアップされて採番されていきます。
例えば、idカラムに値を指定せずに、いくつかのデータを追加してみます。

INSERT INTO fruits (name, price) VALUE ('りんご', '120');
INSERT INTO fruits (name, price) VALUE ('みかん', '110');
INSERT INTO fruits (name, price) VALUE ('ぶどう', '200');

そして、fruitsテーブルのデータをSELECT文で確認します。

SELECT * FROM fruits;

そうすると、下記のように「1」から「3」までの数値がidカラムに自動で採番されました。

AUTO_INCREMENTの値を確認する

AUTO_INCREMENTの次の値を確認するには、SHOW TABLE STATUSを使います。
テーブルの状態を確認できるコマンドです。

SHOW TABLE STATUS WHERE name = "fruits";

SHOW TABLE STATUSで、現在選択中のデータベースに存在するテーブルの情報を表示してくれます。
WHEREでnameカラムを指定することで、fruitsテーブルのみのデータを表示しています。

実行すると、下記のようにfruitsテーブルのデータが表示されました。
Auto_incrementカラムを確認すると、次に設定される値が書かれています。

データを3行入れているので、次は「4」になっていることが確認できました。

SHOW TABLE STATUSの情報が更新されない

ちなみに、TRUNCATE TABLEでデータを削除した場合などに、SHOW TABLE STATUSの情報が更新されない場合はキャッシュが効いていることがあります。
こちらの記事にあるようにinformation_schema_stats_expiry変数の値を変えると良いです。

SET information_schema_stats_expiry = 1;

AUTO_INCREMENTの値をリセットする

AUTO_INCREMENTの値のリセットについてです。

AUTO_INCREMENTを初期値に戻す

AUTO_INCREMENTの値を初期値に戻したいときには、データをTRUNCATE TABLEで全て削除すれば、また「1」からカウントアップしてくれます。

TRUNCATE TABLE fruits;

このように任意のテーブルの値を空にして、最初からインサートすれば、また「1」から採番してくれます。

DELETEで消したのでAUTO_INCREMENTを戻したい

DELETE文で値を消したときには、AUTO_INCREMENTの値は進んだままになります。
例えば、 下記のように3件のデータがあります。

このときに2行目と3行目を消します。

DELETE FROM fruits WHERE id IN (2, 3);

そして、1行追加すると、下記のようにidカラムの番号が歯抜けになります。

ALTER TABLEを使うと、任意の位置からAUTO_INCREMENTを始めることができます。
さっきのように、1行目だけ残して、「2」から採番したいときには下記のように設定します。

ALTER TABLE fruits AUTO_INCREMENT = 2;

こうすると、「りんご」の行を残して、次に追加したときには下記のように「2」から採番されます。

MySQLのテーブルに自動採番する方法まとめ

今回はMySQLでテーブルに自動採番する方法について書きました。
記事の内容をまとめると、下記のようになります。