MySQLのAUTO_INCREMENTは、INSERT時に連番を自動発番してくれる大変便利な機能ですが、INSERTする前1に次の番号を知り、予約、独占2しておきたいということがたまにあります。
そういう場合は、発番テーブルなり、発番サーバなり、発番の機構を別に作るわけです。ただ、そこまで頑張りたくないシーンではそういう機構を自作するのはいろいろ面倒に感じます。
そこで、別の選択肢として発番機構を自作せずに連番IDを予約する方法を紹介します。
この仕組みを実現する仕掛けとしては
- トランザクション
- InnoDB
- INSERT文
- AUTO_INCREMENTのidカラム
とありふれたものです。
DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL, -- titleは必須ということになっている
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
-- トランザクションを開始する
BEGIN;
-- AUTO_INCREMENTを回したいので適当なデータをINSERTする。
-- IGNOREによりtitleのNOT NULL制約を無視。エラーにならず止まらない。
INSERT IGNORE INTO posts () VALUE ();
-- 上INSERTで発番されたIDを取得する
SELECT LAST_INSERT_ID() INTO @next_id;
-- INSERTを無かったことにする
ROLLBACK;
-- 採番されたIDを確認
SELECT @next_id;
ロールバックしたら自動採番値ももとに戻ってしまうのではと思うかもしれませんが、MySQLのInnoDBはトランザクションでロールバックしても、一度増やした自動採番値は戻りません。欠番になります。
この連番ID予約方法は、InnoDBとトランザクションの性質を逆手に取った方法なわけです。連番発行機構を作るのに比べるとかなりハック感がありますが、場合によってはありなんじゃないでしょうか。
注意点としては、トランザクション分離レベルがREPEATABLE READ以上でないとファントムリードの危険性はありそうです。