なぜ戻らないのか

MySQLの仕様でした。SQL全般の仕様なのかは分かりません。
公式サイトに説明があります。

すべてのロックモード (0、1、および 2) では、自動インクリメント値を生成したトランザクションがロールバックされると、これらの自動インクリメント値が「失われます」。「INSERT のような」ステートメントが完了したかどうか、およびそれを含むトランザクションがロールバックされたかどうかに関係なく、自動インクリメントカラムの値は一度生成されたら、ロールバックできません。このような失われた値は再使用されません。したがって、テーブルの AUTO_INCREMENT カラムに格納されている値にはギャップが存在する可能性があります。

簡単にまとめると、実データ生成の有無にかかわらず下記の仕様が適用されます。

AUTO_INCREMENTの値は一度生成されたら戻らない
AUTO_INCREMENTの値は再使用されない
AUTO_INCREMENTが設定されたカラムは連番にならない可能性がある

検証

テスト用のテーブルとデータを作成します。
テーブル・データの作成は公式サイトに掲載されているSQLを利用しました。

テーブル作成

CREATE TABLE animals (
	id MEDIUMINT NOT NULL AUTO_INCREMENT,
	name CHAR(30) NOT NULL,
	PRIMARY KEY (id)
);

mysql> SHOW columns FROM animals;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| name  | char(30)     | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

テストデータ作成

INSERT INTO animals (name)
		VALUES('dog'), ('cat'), ('penguin'), ('lax'), ('whale'), ('ostrich');

mysql> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

ロールバック実行

データを作成後、ロールバックで処理を戻しています。
実データは作成されません。

START TRANSACTION;
INSERT INTO animals (name)
		VALUES('rabbit');
ROLLBACK;

次にロールバックなしで実データを作成します。

INSERT INTO animals (name) VALUES('rabbit');

データを確認すると、AUTO_INCREMENTが設定されたidカラムでは7が飛ばされ8が登録されています。
ロールバック後もAUTO_INCREMENTの値は戻らないことが確認できました。

mysql> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
|  8 | rabbit  |
+----+---------+
7 rows in set (0.00 sec)

まとめ

AUTO_INCREMENTで連番は保証できない(-_-;)

参考記事

採用PR

弊社で一緒に働く仲間を募集しています。
全てのオタクを幸せにしたい方、是非ご覧ください！

【MySQL】AUTO_INCREMENTの値はロールバック後も戻らない