この記事の要点

AUTO_INCREMENT は MySQL/MariaDB で自動連番を生成するカラム属性 定義: id INT AUTO_INCREMENT PRIMARY KEY。テーブルあたり 1 つのみ ロールバックや削除で値は消費される → 連番が飛ぶ（ギャップ） InnoDB の innodb_autoinc_lock_mode で挙動 3 段階（traditional / consecutive / interleaved） 他 DB の対応: PostgreSQL は SERIAL / IDENTITY、Oracle は SEQUENCE / IDENTITY、SQL Server は IDENTITY

AUTO_INCREMENT の基本

-- 基本定義
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL
);

-- INSERT 時に id 省略 → 自動採番
INSERT INTO users (name, created_at) VALUES ('taro', NOW());
INSERT INTO users (name, created_at) VALUES ('jiro', NOW());
-- id は 1, 2 と振られる

-- 明示的に値を入れることも可能（重複は不可）
INSERT INTO users (id, name, created_at) VALUES (100, 'special', NOW());
-- 次の自動採番は 101 になる

-- 直前に採番された値を取得
SELECT LAST_INSERT_ID();
-- アプリ側からも mysql_insert_id() / PDO::lastInsertId() / PreparedStatement.getGeneratedKeys()

初期値・ステップの変更

-- 開始値を 1000 から
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    amount INT
) AUTO_INCREMENT = 1000;

-- 既存テーブルの次採番値を変更
ALTER TABLE orders AUTO_INCREMENT = 5000;
-- → 次の INSERT で id = 5000

-- ステップ（増分）を変更（接続単位）
SET @@auto_increment_increment = 10;
SET @@auto_increment_offset = 1;
-- → 1, 11, 21, 31, ... と採番される
-- マルチマスター構成で重複を避ける用途

ロールバックでも値は消費される

これがよく問題になる挙動です。 AUTO_INCREMENT はトランザクション外で進むため、ロールバックしても番号は戻りません:

-- 現在の次採番値が 10 だと仮定
BEGIN;
INSERT INTO users (name) VALUES ('aaa');  -- id = 10
INSERT INTO users (name) VALUES ('bbb');  -- id = 11
ROLLBACK;

-- 10, 11 は使われずに飛んだ
INSERT INTO users (name) VALUES ('ccc');  -- id = 12（10/11 ではない）

なぜそうなるか: 採番に毎回行ロックを取ると並列性が落ちるので、MySQL は採番処理だけ独立させています。代償として「ロールバックで番号が穴あき」になります。

InnoDB の auto-increment ロックモード

値	名称	挙動
0	traditional	テーブルレベルのロック。直列化、最も安全だが遅い
1	consecutive	シンプル INSERT は軽量ミューテックス、バルク INSERT のみテーブルロック。MySQL 8.0 までの既定（5.7）
2	interleaved	常に軽量ミューテックス、最速。番号順保証なし。MySQL 8.0 既定

-- 現在のモード確認
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

-- 変更（再起動が必要、my.cnf に書く）
-- [mysqld]
-- innodb_autoinc_lock_mode = 2

-- ★ statement-based binlog レプリケーション + mode=2 は組み合わせ不可
--   レプリケーション先で順序がずれる可能性があるため
--   row-based binlog なら mode=2 OK

UNSIGNED と桁あふれ

型	最大値 (UNSIGNED)	用途
TINYINT UNSIGNED	255	ステータス系
SMALLINT UNSIGNED	65,535	マスタコード
MEDIUMINT UNSIGNED	16,777,215	—
INT UNSIGNED	4,294,967,295（約 43 億）	★ 一般的
BIGINT UNSIGNED	18,446,744,073,709,551,615	大規模・将来安心

21 億で詰まる事故: INT 符号付きの場合 21 億で ERROR 167 (22003): Out of range value。新規テーブルは BIGINT UNSIGNED 推奨（Laravel 8+ のデフォルトもこれ）。

リセット方法

-- テスト環境で番号を 1 に戻したい
TRUNCATE TABLE users;
-- → AUTO_INCREMENT も 1 にリセット

-- DELETE では戻らない
DELETE FROM users;
-- → 次の採番は元のまま

-- 明示的に
ALTER TABLE users AUTO_INCREMENT = 1;
-- ただし既存最大値+1 までしか戻せない
-- 既存 max(id) = 100 のテーブルで AUTO_INCREMENT = 1 にしても、
-- 次採番は 101 になる（重複回避のため）

-- 完全リセットしたい場合
TRUNCATE TABLE users;       -- データ全消去 + リセット
DROP TABLE users; CREATE TABLE users ...  -- 再作成

PostgreSQL の SERIAL / IDENTITY

-- SERIAL（PostgreSQL 9 以前から使える慣用）
CREATE TABLE users (
    id SERIAL PRIMARY KEY,    -- 実体は INTEGER + SEQUENCE
    name TEXT
);
-- BIGSERIAL なら BIGINT 版

-- IDENTITY（PostgreSQL 10 以降、SQL 標準）
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);
-- BY DEFAULT なら明示値も入れられる
CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT
);

-- 次採番値を変更
ALTER SEQUENCE users_id_seq RESTART WITH 1000;
-- IDENTITY の場合
ALTER TABLE users ALTER COLUMN id RESTART WITH 1000;

-- 直近採番値
SELECT lastval();
SELECT currval('users_id_seq');

Oracle の SEQUENCE / IDENTITY

-- 12c 以前: SEQUENCE + トリガ or NEXTVAL
CREATE SEQUENCE seq_users START WITH 1 INCREMENT BY 1;

INSERT INTO users (id, name) VALUES (seq_users.NEXTVAL, 'taro');

-- 12c 以降: IDENTITY 列
CREATE TABLE users (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    name VARCHAR2(100)
);

-- SEQUENCE のオプション
CREATE SEQUENCE seq_orders
    START WITH 1000
    INCREMENT BY 1
    NOCACHE       -- キャッシュなし（飛びを最小化、性能↓）
    NOCYCLE;

SQL Server の IDENTITY

CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,     -- 初期 1、増分 1
    name NVARCHAR(100)
);

-- 直近採番値
SELECT SCOPE_IDENTITY();    -- 現スコープのみ（推奨）
SELECT @@IDENTITY;          -- 全スコープ（トリガ起因で別値の罠あり）

-- 明示的に IDENTITY 列に値を入れる
SET IDENTITY_INSERT users ON;
INSERT INTO users (id, name) VALUES (100, 'special');
SET IDENTITY_INSERT users OFF;

-- リセット
DBCC CHECKIDENT ('users', RESEED, 0);
-- → 次採番は 1

ギャップ（番号の飛び）の主な原因

原因	説明
ROLLBACK	採番後に取消 → 番号は消費されたまま
INSERT ... ON DUPLICATE KEY UPDATE	重複時も採番カウンタは進む（MySQL 5.7+）
INSERT IGNORE	無視されたレコードでも採番
REPLACE	削除+挿入なので新採番される
サーバー再起動	MySQL 8.0 まで InnoDB はディスク上の MAX(id) を見て次値決定（5.7 までは再起動でリセット）
SEQUENCE のキャッシュ	Oracle のキャッシュ単位で予約 → 未使用分は飛ぶ

主キーとしての ID か、UUID か

項目	AUTO_INCREMENT	UUID
サイズ	4 / 8 byte	16 byte（VARCHAR(36) なら 36 byte）
連番性	あり（推測可）	なし
分散採番	難しい	容易
インデックス効率	連続 → B-Tree に優しい	ランダム → ページ分割多発
外部公開	推測されると危険	安全

UUID を使う場合はUUID v7 / ULID（時系列順序を持つ UUID）が B-Tree インデックスに優しくおすすめです。

FAQ

Q: 連番に絶対穴を空けたくない
A: AUTO_INCREMENT では不可能。業務上必要なら採番用テーブルを作って UPDATE seq SET v = v + 1; SELECT v; をトランザクション内で実行（性能と引き換え）。

Q: 既存データを別 DB から移行したい
A: 一旦そのままの id で INSERT → 最後に ALTER TABLE t AUTO_INCREMENT = (MAX(id)+1) で次採番値を調整。

Q: BIGINT UNSIGNED AUTO_INCREMENT の上限到達は現実的か？
A: 約 1844 京。1 秒に 1 億件 INSERT しても 6000 年保ちます。実質無限と考えて OK。