AUTO_INCREMENTとは、それを指定したカラムに対してMySQLが自動的に一意のシーケンス番号を生成する機能です。MySQLではおなじみの機能で、サロゲートキーとしての役割や値をそのままユーザIDなどのデータとして使用することも多いと思います（SQLアンチパターンで有名な“⁠IDリクワイアド⁠”になる場合もありますが⁠）⁠。

今回はこのAUTO_INCREMENTについて、いくつか紹介したいと思います。ストレージエンジンによって動作に違いがありますが、今回はMySQL5.7.18のInnoDBストレージエンジンを使用した前提で説明します。

AUTO_INCREMENTについて

AUTO_INCREMENTは、CREATE TABLE文やALTER TABLE文内でカラム名とデータ型を指定した後に記述することで設定できます。1テーブルに対してAUTO_INCREMENTカラムは1つのみです。セカンダリーインデックス、またはユニークキーがあるカラムに対して有効です。プライマリキーでなれけばならないと認識されている方がいると思いますが、そうではありません。

mysql> CREATE TABLE t0 ( 
            id INT PRIMARY KEY , 
            id2 INT AUTO_INCREMENT, 
            KEY(id2)
 );
Query OK, 0 rows affected (0.03 sec)

また、現在の対象テーブルのAUTO_INCREMENT値を取得する方法として以下があります。

1. SHOW CREATE TABLE文から確認
2. information_schema.tablesテーブルのauto_incrementカラムから確認
3. sys.schema_auto_increment_columnsビューのauto_incrementカラムを確認

AUTO_INCREMENTに関するオプション

以下のようなオプションがあります。

auto_increment_incrementとauto_increment_offsetは通常に使っていれば変更することはないでしょう。マルチマスターを構成する際に、それぞれのマスターで同時に挿入された行のAUTO_INCREMENT値が被らないように制御するため使用されたりします。

データ型による違い

最大値はデータ型に依存します。SIGNED属性ではなくUNSIGNED属性を使用することで、より多くの範囲をカバーできます。

最大値に達した挙動は、一意制約（プライマリキーやユニークキー）の有無により変わります。

• 一意制約がある場合、一意制約エラーとなり更新できません。
• 一意制約がない場合、最大値が繰り返し挿入されるようになります。

しかし、BIGINT型の場合は64bit環境におけるC言語で扱える最大値となるため、一意制約の有無に関わらずエラーとなります。また、最大値の挙動も特殊で18446744073709551615を明示的に指定すると挿入できますが、カラムに値を指定しないなど自動インクリメントで挿入する場合は18446744073709551615 - 1の値が限界値となります。

BIGINT型以外

mysql> INSERT INTO t0 (id) values (null);
ERROR 1062 (23000): Duplicate entry '65535' for key 'PRIMARY'

BIGINT型

mysql> INSERT INTO t0 (id) values (null);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

AUTO_INCロック

シーケンス番号を重複することなく振り分けるために、AUTO_INCロックというテーブルロックを取得します。これはトランザクションの間ずっとロックを保持するわけではなく、AUTO_INCREMENTを使用するステートメントが実行中のみロックします。

よって、INSERT..SELECT文やLOAD DATA INFILE文を使用して、AUTO_INCREMENTカラムを含むテーブルを長い時間に渡って更新していると、別のトランザクションから実行されるINSERTはそのステートメントが完了するまで待機することになります。

innodb_autoinc_lock_modeパラメータを使用することで、AUTO_INCロックを制御することができます。モードの変更にはMySQLの再起動が必要です。

0 ：従来ロックモード

このモードは下位互換のために残されているもので、通常あまり使用しません。以下のような1行や複数行で記述された単純なINSERT文（単純挿入）を含むすべての挿入文に対してAUTO_INCロックを取得する動作になります。

INSERT INTO t0 (id,id2,id3) VALUES (NULL,?,?),(NULL,?,?);

1：連続 ロックモード

デフォルトのロックモードです。挿入される行が事前に行数の把握できないINSERT..SELECT文やLOAD DATA INFILE文などの挿入文（一括挿入）時に対してAUTO_INCロックを取得します。

事前に行数の把握できる単純挿入は、これとは別の軽微な排他ロックを取得するため同時挿入性能は上がります。また、一括挿入のAUTO_INCREMENTの順番が守られるので、ステートメントベースレプリケーションでも正常にレプリケーションされます。

2：インターリーブ ロックモード

これはすべての挿入する文に対してAUTO_INCロックを取得しません。そのため、長時間に渡る一括挿入の実行間でも並列挿入ができるため、高速で処理されます。しかし、一括挿入のAUTO_INCREMENTの順番が守られないので、行ベースでのみレプリケーション可能となります。

その他、ロックモードによる挙動の違いは、詳しくはマニュアルをご参照ください。

MySQL再起動時の挙動

AUTO_INCREMENTの値はTRUNCATE TABLEをすることでリセットされますが、DELETEではリセットされません。しかし、各テーブルの最新値はディスクではなくメモリ上に保存されているため、MySQLを再起動することでAUTO_INCREMENTの値はリセットされてしまいます。再起動後、INSERTする時に以下のようなSQLが内部で発行され最新値を取得するようになっています。

SELECT MAX(AUTO_INCREMENTのカラム) FROM 対象テーブル for update;

よって、全件DELETEしたあとにMySQLを再起動することで、AUTO_INCREMENTが0にリセットされてしまった、という予期しない事象が起きるので、注意が必要です。

ちなみに、MySQL8.0.0時点では最新値をディスク上に保存するになり、再起動後も最新値が保たれるように実装されています。ただし、MySQL8.0シリーズは現在開発中であり正式リリースはされていません。

参考として、MySQL8.0 InnoDB AUTO_INCREMENT Counter Initializationをご覧ください。

AUTO_INCREMENTの値変更

現在の値を変更するには、ALTER TABLE .. AUTO_INCREMENT = ?を実行します。以下はAUTO_INCREMENTの値を10000へ変更する例です。

mysql> ALTER TABLE t0 AUTO_INCREMENT = 10000;

ただし、前述のMAX関数を使用したSQLで取得した値よりも小さい数値に変更はできませんので注意が必要です。

まとめ

AUTO_INCREMENTは自動で連番を作ってくれる便利なものですが、いくつかハマるポイントもあるので注意が必要です。