MySQLで自動インクリメントIDを予約する方法

MySQLでは、トランザクションを活用することで、自動インクリメント（AUTO_INCREMENT）のID値を事前に予約できます。INSERTを実行した後にROLLBACKでロールバックしても、AUTO_INCREMENTカウンターは巻き戻らないという仕組みを利用するテクニックです。

基本構文

自動インクリメントIDを予約するための基本的な構文は以下のとおりです。

START TRANSACTION;
insert into yourTableName values(),(),(),();
ROLLBACK;
SELECT LAST_INSERT_ID() INTO @anyVariableName;

この構文のポイントは、トランザクション内で空のVALUES句を持つINSERT文を実行し、その直後にROLLBACKを行う点です。これにより、実際にはデータは保存されませんが、IDだけが採番され、LAST_INSERT_ID()関数でその値を取得できます。

動作確認用テーブルの作成

まず、理解を深めるためにサンプルテーブルを作成しましょう。テーブル作成のクエリは以下のとおりです。

mysql> create table reservingAutoIncrementDemo
    -> (
    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.45 sec)

トランザクション内でのレコード挿入とロールバック

次に、トランザクションを開始し、INSERTコマンドで複数のレコードを挿入してみます。その後、ROLLBACKで変更を取り消します。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into reservingAutoIncrementDemo values(),(),(),();
Query OK, 4 rows affected (0.15 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select *from reservingAutoIncrementDemo;
+--------+
| UserId |
+--------+
| 1      |
| 2      |
| 3      |
| 4      |
+--------+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

トランザクション内ではIDが1〜4まで採番されていることがわかります。しかし、ROLLBACKを実行した後は、テーブルからデータは削除されます。それでも、AUTO_INCREMENTカウンターの値は元に戻りません。

予約されたIDの取得

ここで、自動インクリメントされたIDを変数に格納して予約するクエリを実行します。

mysql> SELECT LAST_INSERT_ID() INTO @IncrementedValue;
Query OK, 1 row affected (0.00 sec)

LAST_INSERT_ID()は、現在のセッションで最後に挿入された行のAUTO_INCREMENT値を返す関数です。今回の場合、4行挿入しているため、最初のIDである「1」が返されます。

予約値の確認

予約された自動インクリメント値を確認してみましょう。以下のクエリを実行します。

mysql> select @IncrementedValue;

実行結果は以下のとおりです。

+-------------------+
| @IncrementedValue |
+-------------------+
| 1                 |
+-------------------+
1 row in set (0.00 sec)

まとめ

この手法を使えば、ROLLBACKによってデータ自体は登録されないものの、採番済みのID値をユーザー変数として保持できます。連番の事前確保や、IDを採番してから別テーブルへ関連データを登録するような処理において便利なテクニックです。ただし、同時実行環境では他のセッションとの競合に注意し、必要に応じて適切なロック戦略を検討してください。