[小ネタ] MySQLのAUTO_INCREMENTは勝手にテーブルのデータの最大値から始まる

MySQL の AUTO_INCREMENT

突然ですが、MySQLには他DBMSにあるシーケンスという種類のオブジェクトはありません。 マニュアルを探しても本当にありません。

その代わりテーブルのカラムに AUTO_INCREMENT を指定してテーブル固有の勝手に増えるIDを実現することが可能です。下記が例です。

CREATE TABLE `hogehoge` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

INSERT INTO hogehoge VALUES ();
INSERT INTO hogehoge VALUES ();
INSERT INTO hogehoge VALUES ();

-- => 3
SELECT MAX(id) FROM hogehoge;

AUTO_INCREMENTはコンフリクトするか？

ここで、本題ですが、前述の状態の id = 3 まで進んだテーブルの定義（DDL）をバックアップすると下記のようなDDLを mysqldump 氏が作成してくれます。

CREATE TABLE `hogehoge` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=4;

AUTO_INCREMENT=4 でシーケンスを初期値 4 にしてくれているようですね。この状態で新規INSERTすると 4 からINSERTされます。

INSERT INTO hogehoge VALUES ();

-- => 4
SELECT MAX(id) FROM hogehoge;

ただ、システムバックアップあるあるですが、バックアップ取得の際に、1. 上記のバックアップDDL発行と 2. バックアップDML（INSERT文）の取得の発行のタイミングが異なると、上記の AUTO_INCREMENT=4 に反して、下記のようなバックアップが取得されてしまいます。

INSERT INTO hogehoge VALUES (1);
INSERT INTO hogehoge VALUES (2);
INSERT INTO hogehoge VALUES (3);
INSERT INTO hogehoge VALUES (4); -- あっ……
INSERT INTO hogehoge VALUES (5); -- あっ！

どうやら、DDLからDMLのバックアップの隙間に新規データが2件挿入されたようですね。

「これってテーブル側のシーケンスが =4 の状態なのに、次にINSERTでコンフリクトするじゃん！」

と思ってしまいますが、そこはMySQL、 ちゃんとテーブルの最大値の次に大きい値を採番して勝手に回避してくれます。

試しに大きな値(100)を挿入して

INSERT INTO hogehoge VALUES (100);

-- => 100
SELECT MAX(id) FROM hogehoge;

再び自動採番させてみれば 101 が来るので、MySQLが勝手にテーブルの最大値を参照してINSERTのコンフリクトを回避していることが分かります。

INSERT INTO hogehoge VALUES ();

-- => 101
SELECT MAX(id) FROM hogehoge;

AUTO_INCREMENT は過去に戻るか？

逆に 100 101 を消して再度、 INSERT するとどうなるのでしょうか？

DELETE FROM hogehoge WHERE id >= 100;

INSERT INTO hogehoge VALUES ();

-- => 102
SELECT MAX(id) FROM hogehoge;

一度進んだシーケンスは戻りません。 まあ戻ったりしたら、DELETEしたデータを戻した際にコンフリクトするので妥当な仕様でしょう。

まとめ

• AUTO_INCREMENT は勝手にテーブルのデータの最大値から始まり、コンフリクトしない
• 一度進んだシーケンスは戻らない