概要
今更ですが、MySQLで採番機能（シーケンス）が必要になったので、実装方法について自分用に整理してみました。
環境
MySQL 8.0.18
実装方法
方法１．LAST_INSERT_ID(expr) を使う
公式ドキュメントで紹介されている方法です。
MySQL :: MySQL 8.0 Reference Manual :: 12.15 Information Functions
１. 採番用のテーブルを用意し、ID=0のレコードをINSERTしておく
CREATE TABLE sequence (id INT NOT NULL) ENGINE=InnoDB;
INSERT INTO sequence VALUES (0);
２. LAST_INSERT_IDを使用し、連番を取得する
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();
注意点として、
- rollbackすると、同一のIDが再度発行される
- UPDATEで排他ロックがかかる（分離レベル：
REPEATABLE-READで検証）
が挙げられます。
方法２．AUTO_INCREMENTを使う
「MySQL で採番テーブル」で紹介されている方法です。
１．採番用のテーブルを用意する
CREATE TABLE seq_ai (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) engine = InnoDB;
２．新規レコードをINSERTし、LAST_INSERT_IDで連番を取得する
INSERT INTO seq_ai VALUES (NULL);
SELECT LAST_INSERT_ID();
３．採番テーブルにたまるレコードを削除する
DELETE FROM seq_ai WHERE id < :id; -- 2で取得した最新のレコードを残して削除
方法１の注意点1（rollback時の挙動）については、こちらの方法ではrollbackしても同じIDは発行されません（AUTO_INCREMENTのカウンタはrollbackしても戻らない）。
一方で、こちらの方法だと３のDELETE時に排他ロックが発生します（分離レベル：REPEATABLE-READで検証）。
方法３．方法１＋ストレージエンジンをMyISAMにする
方法１の注意点はどちらもトランザクションに起因する問題だったので、採番用テーブルのみストレージエンジンをMyISAMにします。
CREATE TABLE sequence (id INT NOT NULL) ENGINE=MyISAM;
INSERT INTO sequence VALUES (0);
シーケンスの取得方法は方法１と同じです。
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();
ストレージエンジンをMyISAMにすることで、
- rollbackしても同じIDが発行されない
- 排他ロックがかからない
という状態を実現できます。