Help us understand the problem. What is going on with this article?

MySQLで発番テーブルを使わない連番IDの予約方法

More than 1 year has passed since last update.

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以上でないとファントムリードの危険性はありそうです。


  1. INSERTする前に連番値をテーブルのメタ情報から調べる方法も考えられますが、並行性と整合性を考えたときに果たして安全なのでしょうか? 

  2. 「どうしても連番じゃなきゃいや」でなければUUIDなどの予約不要なIDの仕組みも検討する価値があります 

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away