AUTO_INCREMENTを設定する(連続した数値を自動でカラムに格納する)

テーブルを作成するときにカラムに AUTO_INCREMENT をつけると、データを追加した時にカラムに対して現在格納されている最大の数値に 1 を追加した数値を自動で格納することができます。カラムに連続した数値を自動で格納したい場合に便利です。ここでは MySQL で AUTO_INCREMENT の使い方について解説します。

(Last modified: )

AUTO_INCREMENTの使い方

整数または浮動小数点数のデータ型が設定されているカラムに対しては AUTO_INCREMENT を設定することができます。書式は次の通りです。 

CREATE TABLE db_name.tbl_name
  (col_name data_type AUTO_INCREMENT, ...)

カラムのデータ型のあとに AUTO_INCREMENT を記述します。 AUTO_INCREMENT を設定する場合は次の制約があります。

・AUTO_INCREMENT はテーブルごとに 1 つのカラムにしか設定できません。
・AUTO_INCREMENT が設定されたカラムにはインデックスが設定されている必要があります。
・AUTO_INCREMENT が設定されたカラムには DEFAULT 制約は設定できません。
・AUTO_INCREMENT が設定されたカラムに正の数値を格納した場合だけ正しく動作する。

インデックスを設定する必要があるので、実際には次のような書式になります。インデックスを自動的に作成する PRIMARY KEY や UNIQUE を設定しても構いません。(インデックスについては「インデックスを作成する(INDEX)」で解説します)。 

CREATE TABLE db_name.tbl_name
  (col_name data_type AUTO_INCREMENT, ... , INDEX (col_name))

AUTO_INCREMENT が設定されたカラムには任意の値を格納できますが、 NULL または 0 を格納すると現在カラムに格納されている最大の値に 1 を加算した値を自動で格納します。

-- --

では実際に試してみます。次のようなテーブルを作成します。 id カラムに AUTO_INCREMENT を設定しています。

create table staff(id int auto_increment, name varchar(10), index(id));

AUTO_INCREMENTの使い方(1)

作成したテーブルのカラム情報を取得してみます。

show columns from staff;

AUTO_INCREMENTの使い方(2)

Extra カラムの値をみていただくと、 AUTO_INCREMENT が設定された id カラムには auto_increment と表示されています。

テーブルにデータを追加します。 AUTO_INCREMENT カラムには値を設定せずにデータを追加します。

insert into staff(name) values('Yamada');

AUTO_INCREMENTの使い方(3)

「DEFAULT 制約が設定されていないカラムのデフォルト値」で解説しましたが、 DEFAULT 制約が設定されていないカラムは値を設定せずにデータを追加すると NULL が格納されます。 id カラムには AUTO_INCREMENT が設定されていますので、 NULL が格納されると自動的に現在カラムに格納されている最大の値に 1 を加算した値を自動で格納します。テーブルを作成した直後の場合は 1 が格納されます。

ではデータを取得してみます。

select * from staff;

AUTO_INCREMENTの使い方(4)

追加されたデータの id カラムの値が 1 になっていることが確認できました。

それではもう 1 つテーブルにデータを追加し、そのあとでデータを取得して確認してみます。

insert into staff(name) values('Suzuki');

AUTO_INCREMENTの使い方(5)

2 番目に追加されたデータの id カラムの値は、現在カラムに格納されている最大の値 1 に 1 を加算した 2 が格納されていることが確認できました。

このようにカラムに AUTO_INCREMENT を設定すると、自動的に連続した数値が格納されていきます。

AUTO_INCREMENTが設定されたカラムに次に追加される値を確認する

※ このブロックで解説している AUTO_INCREMENT を使って次に追加される値を確認するために、 MySQL 8.0.15 の環境で INFORMATION_SCHEMA.TABLES から AUTO_INCREMENT の値を取得しようとすると常に NULL しか取得できなかったため、事前に次の SQL を実行しています。

set @@SESSION.information_schema_stats_expiry = 0;

AUTO_INCREMENTが設定されたカラムに次に追加される値を確認する(1)

-- --

AUTO_INCREMENT が設定されたカラムに自動で格納される値を確認するには INFORMATION_SCHEMA.TABLES テーブルから次のように参照することができます。(下記では使用しているデータベース名が mydb 、対象のテーブル名が staff の場合です)。

select AUTO_INCREMENT from information_schema.tables where table_schema='mydb' and table_name='staff';

AUTO_INCREMENTが設定されたカラムに次に追加される値を確認する(2)

テーブルを作成した直後は AUTO_INCREMENT の値が NULL となっています。

それでは実際にテーブルにデータを追加し、そのあとでデータを取得して確認してみると AUTO_INCREMENT が設定されたカラムには 1 が格納されていました。

insert into staff(name) values('Yamada');

AUTO_INCREMENTが設定されたカラムに次に追加される値を確認する(3)

ではあらためて INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT を取得してみます。

select AUTO_INCREMENT from information_schema.tables where table_schema='mydb' and table_name='staff';

AUTO_INCREMENTが設定されたカラムに次に追加される値を確認する(4)

AUTO_INCREMENT の値が 2 となっています。よって次に AUTO_INCREMENT が設定されたカラムに NULL などが格納された時には 2 が格納されます。

もう一度テーブルにデータを追加し、そのあとでデータを取得して確認してみると AUTO_INCREMENT が設定されたカラムには 2 が格納されていました。

insert into staff(name) values('Suzuki');

AUTO_INCREMENTが設定されたカラムに次に追加される値を確認する(5)

ではあらためて INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT を取得してみます。

AUTO_INCREMENTが設定されたカラムに次に追加される値を確認する(6)

AUTO_INCREMENT の値が 3 となっています。よって次に AUTO_INCREMENT が設定されたカラムに NULL などが格納された時には 3 が格納されます。

このように AUTO_INCREMENT が設定されたカラムに次にデータが追加された時に自動で格納される値は INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT カラムの値を取得することで確認できます。

最大の値が格納されたデータが削除された場合

現在 staff テーブルには 4 つのデータが格納されており、 id カラムには AUTO_INCREMENT が設定されています。

最大の値が格納されたデータが削除された場合(1)

INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT を取得すると 5 と表示されます。

select AUTO_INCREMENT from information_schema.tables where table_schema='mydb' and table_name='staff';

最大の値が格納されたデータが削除された場合(2)

それではテーブルに格納されているデータの中で id カラムの値が最大の 4 のデータを削除します。

delete from staff where id='4';

最大の値が格納されたデータが削除された場合(3)

ここであらためて INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT を取得すると値は前回と変わらず 5 のままです。

最大の値が格納されたデータが削除された場合(4)

確認のためにデータを追加し、そのあとでデータを取得して確認してみると AUTO_INCREMENT が設定されたカラムには 5 が格納されていました。

insert into staff(name) values('Hoshi');

最大の値が格納されたデータが削除された場合(5)

このようにテーブルからデータを削除しても、 AUTO_INCREMENT が設定されたカラムに次に自動的に格納される値に変化はありません。

AUTO_INCREMENTが設定されたカラムに指定した値を格納する

AUTO_INCREMENT が設定されているカラムは NULL または 0 を格納することで自動で値が格納されますが、値を指定して格納することもできます。

ではデータを追加してみます。

insert into staff values(12, 'Sugino');

AUTO_INCREMENTが設定されたカラムに指定した値を格納する(1)

AUTO_INCREMENT が設定されたカラムにも指定した値が格納されました。では、INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT を取得してみます。

AUTO_INCREMENTが設定されたカラムに指定した値を格納する(2)

先ほど追加したデータの AUTO_INCREMENT が設定されたカラムの値が INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT の値よりも大きい値でしたので、 INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT の値は今回追加したデータのカラムの値に 1 を加えた値である 13 になりました。

ではもう 1 つデータを追加してみます。

insert into staff values(8, 'Tomita');

AUTO_INCREMENTが設定されたカラムに指定した値を格納する(3)

では、INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT を取得してみます。

AUTO_INCREMENTが設定されたカラムに指定した値を格納する(4)

先ほど追加したデータの AUTO_INCREMENT が設定されたカラムの値が INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT の値よりも小さい値でしたので、 INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT の値は変わらずに 13 のままです。

このように AUTO_INCREMENT が設定されたカラムであっても指定した値を格納することができ、格納した値によっては INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT の値も変化します。

テーブル作成時にAUTO_INCREMENTで自動で格納される値の初期値を設定する

AUTO_INCREMENT が設定されたカラムに設定される初期値は 1 から開始されますが任意の値から開始することもできます。初期値を設定する場合の書式は次の通りです。 

CREATE TABLE db_name.tbl_name
  (col_name data_type AUTO_INCREMENT, ... , INDEX (col_name))
  AUTO_INCREMENT = value

AUTO_INCREMENT が設定されたカラム( col_name )に最初に自動で格納される初期値( value )を設定してテーブルを作成します。

では実際に試してみます。次のようなテーブルを作成します。 id カラムに AUTO_INCREMENT を設定しています。初期値は 20 としました。

create table customer(id int auto_increment, name varchar(10), index(id)) auto_increment = 20;

テーブル作成時にAUTO_INCREMENTで自動で格納される値の初期値を設定する(1)

明示的に指定しているのでテーブルを作成した直後に INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT を取得すると指定した 20 と表示されます。

select AUTO_INCREMENT from information_schema.tables where table_schema='mydb' and table_name='customer';

テーブル作成時にAUTO_INCREMENTで自動で格納される値の初期値を設定する(2)

ではデータを追加し、そのあとでデータを取得しています。

insert into customer(name) values('Nakano');

テーブル作成時にAUTO_INCREMENTで自動で格納される値の初期値を設定する(3)

AUTO_INCREMENT が設定されたカラムには 20 が格納されたことが確認できました。

なお初期値を指定しても増減は 1 と変わりませんので、次に AUTO_INCREMENT カラムが設定されたカラムに自動で格納される値は 20 に 1 を加えた 21 となります。

テーブル作成時にAUTO_INCREMENTで自動で格納される値の初期値を設定する(4)

AUTO_INCREMENTで自動で格納される値をリセットする

作成済みのテーブルで AUTO_INCREMENT によって次に自動で格納される値を指定したり、テーブルそのものは削除せずにデータだけをすべて削除した場合に AUTO_INCREMENT で自動で格納される値をリセットして 1 から開始したい場合には ALTER TABLE 文を使って変更することができます。 

ALTER TABLE tbl_name AUTO_INCREMENT = value

では実際に試してみます。次のようなテーブルを作成しました。 id カラムには AUTO_INCREMENT が設定されています。テーブルを作成したあとデータを 3 つ追加しました。

AUTO_INCREMENTで自動で格納される値をリセットする(1)

このテーブルの INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT の値は 4 となっています。

select AUTO_INCREMENT from information_schema.tables where table_schema='mydb' and table_name='staff';

AUTO_INCREMENTで自動で格納される値をリセットする(2)

ここで DELETE 文を使ってテーブルに格納されているデータをすべて削除し、最初からデータを追加する場合で考えてみます。

delete from staff;

AUTO_INCREMENTで自動で格納される値をリセットする(3)

データの削除を行っても INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT の値はリセットされないため、データを追加すると AUTO_INCREMENT によって自動で追加される値は 4 となります。

AUTO_INCREMENTで自動で格納される値をリセットする(4)

そこで ALTER TABLE 文を使って AUTO_INCREMENT で次に格納される値を 1 にリセットします。次のように実行してください。

alter table staff auto_increment = 1;

AUTO_INCREMENTで自動で格納される値をリセットする(5)

INFORMATION_SCHEMA.TABLES テーブル の AUTO_INCREMENT の値は指定した 1 となっています。

AUTO_INCREMENTで自動で格納される値をリセットする(6)

これで次にデータを追加した時に、 AUTO_INCREMENT によって自動で格納される値は 1 から開始されます。

※ なお DELETE 文ではなく TRUNCATE TABLE 文を使用するとデータの削除だけではなく AUTO_INCREMENT もリセットされます。これは TRUNCATE TABLE が一度テーブルを削除して作り直すことによってデータを削除しているためです。DELETE 文とは他にも動作が異なる部分がありますので、詳しくは TRUNCATE TABLE 文の解説を確認してください。

-- --

AUTO_INCREMENTの使い方について解説しました。

( Written by Tatsuo Ikura )

プロフィール画像

著者 / TATSUO IKURA

これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。