AUTO_INCREMENTの値は、保存済みIDや取得関数と分ける

AUTO_INCREMENTは、INSERT時のID生成に使う列属性です。欠番のない伝票番号やコミット順を保証する仕組みではありません。テーブル関連の親ページから子ページを選択でき、定義変更と確認はAUTO_INCREMENTの追加と確認方法で扱います。主キー/UNIQUEによる一意性の保護、外部キーの参照、アクセス権限も別の設計です。

2026-10-26にMySQL8.4.13 / MariaDB11.8.9-ubu2404の新規InnoDB検体で確認しました。既存本番の採番値・行・設定は変更していません。他製品の既存章は残しますが、同じカウンタ/取得関数/リセット構文として実行するものではありません。

1. 製品・モード・増分を読み取る

SELECT VERSION(), DATABASE(), @@SESSION.sql_mode,
       @@SESSION.auto_increment_increment, @@SESSION.auto_increment_offset,
       @@GLOBAL.innodb_autoinc_lock_mode;

この検体の増分/offsetは1/1です。実環境ではsession設定やpoolごとの接続を調べます。MySQLとMariaDBの既定や変更可能範囲を混同せず、確認のために既存サーバーのロックモードやreplication設定を変えません。後の旧例が示す1,2,101等も、空の表とこの条件を前提とした値です。

2. 明示IDとLAST_INSERT_IDの違いを確かめる

次は新規の使い捨てcwiki_w27データベースでだけ実行します。既存表を置き換えたり、本番へ作成権限を追加したりする手順ではありません。同じ接続で順に比較します。

-- New disposable database cwiki_w27 only, with increment=1 and offset=1.
CREATE TABLE cwiki_w27.numbers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(40) NOT NULL
) ENGINE=InnoDB;

INSERT INTO cwiki_w27.numbers (label) VALUES ('first');
SELECT LAST_INSERT_ID();

最初の自動採番は1で、LAST_INSERT_IDも1です。次にid=100を明示します。

INSERT INTO cwiki_w27.numbers (id,label) VALUES (100,'explicit');
SELECT LAST_INSERT_ID();

100の行は保存されますが、この明示INSERT後のLAST_INSERT_IDは1のままでした。直前の行のIDを常に返す関数でも、表全体の最大IDを返す関数でもありません。明示IDは入力した値を別に管理します。

INSERT INTO cwiki_w27.numbers (label) VALUES ('automatic A'),('automatic B');
SELECT LAST_INSERT_ID();

SELECT id,label FROM cwiki_w27.numbers ORDER BY id;

次の2行は101/102、LAST_INSERT_IDは最初の自動採番101でした。最終行102ではありません。新しい別接続の取得値は初め0で、そちらのINSERTで1になっても元接続は101を保持しました。アプリではINSERTと取得を同じ接続で扱い、失敗したINSERT後の取得値や再接続/別poolの値を成功IDとして使いません。trigger/複数行/driverの取得APIの条件も確認します。

3. MAX(id)は次の番号の予約ではない

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='cwiki_w27' AND TABLE_NAME='numbers';
SELECT MAX(id) AS stored_max FROM cwiki_w27.numbers;

MAXは保存されている行の最大値です。カウンタにはROLLBACK・失敗・予約した未使用値等の影響があり、常にMAX+1とは限りません。検体では取消した行は残らず、消費した番号は次のINSERTで再利用されませんでした。手動ROLLBACK後のLAST_INSERT_IDも取消前の取得値へ戻らず、その時の値を保持しました。取得値があることと行のCOMMIT成功は別です。

最後の行をDELETEした別検査でも番号は詰まらず、次のIDは保存済みMAX+1より大きくなりました。欠番を埋めるための再採番、TRUNCATE/全行DELETE/DROPは行や参照を壊し得ます。次のカウンタを低く設定しても既存行のIDを振り直す操作ではありません。欠番そのものを障害と見なさず、監査ログ/業務番号の要件を別に決めます。

4. 0、型の上限、主キーでない索引の条件

今回の通常modeでは0のINSERTも自動採番でした。NO_AUTO_VALUE_ON_ZEROを検体接続だけに追加すると、0が文字どおり保存されました。既存データや設定を変える「修正」ではなく、対象接続の条件を確認するための比較です。AUTO_INCREMENTを通常の非UNIQUE索引に定義した別検体では、明示値1を重ねて保存できました。属性だけで一意制約が自動追加されると考えず、PRIMARY/UNIQUEを確認します。

TINYINT UNSIGNEDの255まで保存した検体では、次の自動採番はMySQLで1062、MariaDBで167となり、元の255は残りました。全ての上限到達を同じエラー番号に固定しません。BIGINT UNSIGNEDも有限で、明示的に大きな値を入れる移行・増分・予約の消費・アプリの整数表現も考慮します。現在値と余裕を監視し、「実質無限だから確認不要」としません。

5. IDの推測しにくさは認可の代わりではない

連番でもUUIDでも、対象オブジェクトを読む/編集する権限を毎回検証します。UUIDを知っていることだけでアクセスを許可しません。UUIDv7には時刻由来のフィールドがあり、ULIDはUUIDの版ではなく別の形式です。両者の整列性/索引コスト・分散生成・保存形式は採用実装と負荷で評価し、無条件の速度/安全性を保証しません。

この記事の要点

AUTO_INCREMENT は MySQL/MariaDB で自動連番を生成するカラム属性 定義: id INT AUTO_INCREMENT PRIMARY KEY。テーブルあたり 1 つのみ 採番後のROLLBACKや行削除でも番号は詰まらない → 連番が飛ぶ（ギャップ） InnoDB の innodb_autoinc_lock_mode で挙動 3 段階（traditional / consecutive / interleaved） 他 DB の対応: PostgreSQL は SERIAL / IDENTITY、Oracle は SEQUENCE / IDENTITY、SQL Server は IDENTITY

AUTO_INCREMENT の基本

旧例の注意：別のusers用の旧例です。明示100の後のLAST_INSERT_IDは100ではなく前の自動採番値のままです。PHPの旧mysql拡張名とMySQL C APIの同名関数も混同せず、現行driverを確認します。

-- 基本定義
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()

初期値・ステップの変更

旧例の注意：開始値/増分/offsetの代替例です。途中まで採番した表へ設定した後も1から始まる意味ではありません。既存pool/replication設定を変更しません。

-- 開始値を 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, ... と採番される
-- マルチマスター構成で重複を避ける用途

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

これがよく問題になる挙動です。 InnoDBの採番カウンタはtransactionの取消対象ではなく、行をROLLBACKしても消費した番号は戻りません。行のtransaction処理自体が無効になる意味ではありません:

旧例の注意：直前のusers定義ではcreated_atが必須なのにこのINSERTは省略しており、strict modeでそのまま成功する例ではありません。新しい検体例で欠番を確認します。

-- 現在の次採番値が 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 ではない）

なぜそうなるか: 採番には専用の同期/ロックモードがあり、行の確定順や取消と同じ状態ではありません。どのモードでも採番後の取消による欠番があり、lock mode変更を欠番対策にしません。

InnoDB の auto-increment ロックモード

値	名称	挙動
0	traditional	INSERT系文でAUTO-INCロックを文の終了まで保持。全処理の安全性/速度の順位ではない
1	consecutive	単純INSERT等とbulk/mixed等で同期が異なる。MySQL5.7では既定、MariaDBの対象版の既定も別途確認
2	interleaved	AUTO-INCの表ロックを使わず並行採番。最速/無待機とは断定しない。MySQL8.4では既定、replication方式を照合

旧例の注意：設定の旧記録です。本番設定/再起動を追加しません。replication方式/製品版の条件を確認し、row-basedだけで全複製の整合が保証されるとは扱いません。

-- 現在のモード確認
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符号付きの上限は2,147,483,647です。失敗番号は版/操作でも異なり一律167ではありません。型は範囲/参照先/アプリ互換/容量から決め、フレームワークの一つの既定値を全表への変更指示にしません。

リセット方法

旧例の注意：実行しない旧削除/リセット例です。TRUNCATE/全行DELETE/DROPはデータを失い、欠番修復ではありません。設定値変更も既存IDを振り直さず、復旧の代用品にはなりません。

-- テスト環境で番号を 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

旧例の注意：PG用の代替例です。同じusersのCREATEを順次実行せず、sequence/identityと取得関数のscopeや既存参照を確認します。今回はPG実行未検証です。

-- 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

旧例の注意：Oracle用です。SEQUENCEのキャッシュ停止で欠番がなくなる保証はありません。同じ表へ別製品の定義を流しません。今回はOracle実行未検証です。

-- 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

旧例の注意：SQL Server用です。IDENTITY_INSERT/RESEEDを自動対処にしません。DBCC後の次値は既存行/DELETE/TRUNCATEの状態と増分でも異なり、常に1とは限りません。今回は実行未検証です。

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	生成値を使わない更新でも消費する場合がある。明示値/文の形/engine/lock modeを確認
INSERT IGNORE	無視/失敗でも消費する場合がある。全操作/モードで固定しない
REPLACE	置換は行/依存へ作用する。idの明示有無でも異なり、番号修復に使わない
サーバー再起動	MySQL5.7以前と8.0以降の永続化を区別。MariaDBは10.2.4以降で永続化。再起動による1へのリセットを保証しない
SEQUENCE のキャッシュ	Oracle のキャッシュ単位で予約 → 未使用分は飛ぶ

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

項目	AUTO_INCREMENT	UUID
サイズ	型次第。INTは4/BIGINTは8byte	UUIDは128bit。36文字等の文字列表現と保存方式/文字コードを区別
連番性	増分/欠番があり、確定順の保証ではない	版/生成方式次第。v7は時刻由来で、無欠番の連番ではない
分散採番	複数writerの設定/構成管理が必要	生成実装/衝突/保存規則を設計する
インデックス効率	順次値でも負荷/競合と索引設計で評価	版/分布/保存方式で異なる。全UUIDをランダムとしない
外部公開	形式にかかわらず対象ごとの認可が必要	推測困難性は認可の代わりではない

UUIDv7は時刻由来のフィールドを持ち、ULIDは別の形式です。型/保存方式・生成規則・索引への作用を実装と負荷で比較し、一律の性能/安全性を保証しません。

FAQ

Q: 連番に絶対穴を空けたくない
A: AUTO_INCREMENTは無欠番を保証しません。業務番号は確定/取消/再試行/監査の要件から別設計にします。採番表の排他や同じ接続・transactionだけで外部処理まで無欠番になるとは限らず、単純なUPDATE/SELECT断片を完成手順としません。

Q: 既存データを別 DB から移行したい
A: 元ID/型/参照/重複・同時書込みと復旧条件を先に照合します。ALTER TABLEのAUTO_INCREMENT値にMAX(id)+1の式は直接書けず、検体では1064でした。単にMAXを読んで埋め込むだけの手順を競合対策とも扱いません。

Q: BIGINT UNSIGNED AUTO_INCREMENT の上限到達は現実的か？
A: 上限18,446,744,073,709,551,615は有限です。挿入行数だけでなく明示した大きな値/増分/失敗時の消費/移行値も確認し、現在値の監視やアプリ側の整数互換を省略しません。

元の色付きSQLと結果表

元v3のSQL/3段階の出力表を保持します。新しい使い捨て検体で1→明示100→自動101を照合済みですが、実表の最大値+1や無欠番を一般に保証する例ではありません。別の表名による旧例として読み、本番へ作成/再採番を追加しません。

AUTO_INCREMENTとはMariaDBでテーブルを作成する際にカラムに設定できる属性です。

値を省略すると採番されますが、常に保存済みの最大値+1ではありません。次の元例は空の検体/増分1/offset1/同時書込みなしの場合です。

INSERT文で対象のカラムに任意の値を設定することも可能です。

以下、例となります。

・テーブルを作成する

CREATE TABLE test_table_2(
    col1 INT PRIMARY KEY AUTO_INCREMENT
    , col2 INT
);

 

・AUTO_INCREMENTを設定したカラムに値を指定せず、INSERT文を実行する

INSERT INTO test_table_2(
    col2
)VALUES(
    999
);

 

・テーブルを確認すると、col1に自動で値が設定されている。

col1	col2
1	999

 

・AUTO_INCREMENTを設定したカラムに任意の値を指定して、INSERT文を実行する

INSERT INTO test_table_2(
    col1
    , col2
)VALUES(
    100
    ,999
);

 

・テーブルを確認すると、col1に任意の値が設定されている。

col1	col2
1	999
100	999

 

・この状態で、もう一度AUTO_INCREMENTを設定したカラムに値を指定せず、INSERT文を実行する

INSERT INTO test_table_2(
    col2
)VALUES(
    999
);

 

・この限定した検体では、次の採番で101が保存される。

col1	col2
1	999
100	999
101	999

 

確認範囲と一次資料

新規の小規模検体で掲載SQLと限定した失敗/保持条件を確認しています。本番の全負荷/同時実行/全engine/replication/再起動復旧/他製品・driverの実行保証ではありません。元のコードも記録として保持し、実行を推奨しない例には直前で注意を示します。

• MySQL8.4 AUTO_INCREMENT / LAST_INSERT_ID / InnoDBの採番
• MySQL8.4の型属性 / 統計キャッシュの設定
• MariaDB AUTO_INCREMENT / MariaDB InnoDB採番
• SQL Server DBCC CHECKIDENT
• UUID RFC9562 / ULID仕様 / OWASP: IDの複雑さとアクセス制御