SQLでAUTO_INCREMENT属性を使って連番IDを自動生成する方法

SQLで連番IDを自動生成したい場合、MySQLでは整数列にAUTO_INCREMENTを付け、通常のINSERTではID列を指定しません。重要なのは、AUTO_INCREMENTがSQL全製品に共通するキーワードではないことです。PostgreSQLでは標準SQLのidentity列、SQL ServerではIDENTITY(seed, increment)を使います。まず接続先DBMSを確定し、その製品の仕組みに合わせてください。

自動採番値は『欠番のない受付番号』ではなく、行を一意に識別する技術的なキーとして扱うのが基本です。MySQL InnoDBでは、採番後にトランザクションがロールバックされても値は再利用されないため、1、2、4のような欠番は正常に起こります。請求書番号や法定帳票番号など連続性に業務要件がある番号は、主キーとは別の採番設計、監査、再発行ルールが必要です。

DB製品別の選び方

MySQLのAUTO_INCREMENT列には、公式リファレンス上、1つの表につき1列、索引が必要、DEFAULT値を指定できないなどの条件があります。多くの設計ではBIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEYのように主キーへ使いますが、必要な最大件数、外部キーの型、アプリケーション言語の整数範囲をそろえて決めます。将来の上限を見ずに小さな型を選ぶと、サービス稼働中の型拡張が大きな移行になります。

MySQLでの基本手順

テーブルを作成する

新規テーブルでは、自動採番列を明示したうえで主キーまたは一意索引を設定します。次の例は構造を示す最小例です。名前や型は実データに合わせ、作成前に開発環境でDDLを確認してください。

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(200) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

行を追加するときは、通常はidを列一覧から外します。NULLを指定して生成させる動作も公式仕様にありますが、列を省略する書き方は、アプリケーションが主キーを所有しない意図を明確にできます。0の扱いはNO_AUTO_VALUE_ON_ZEROというSQLモードに左右されるため、0を採番要求の代用品として設計しない方が安全です。

INSERT INTO orders (customer_name)
VALUES ('山田商店');

SELECT LAST_INSERT_ID();

生成されたIDを取得する

MySQLではLAST_INSERT_ID()または使用中の公式ドライバーが提供する生成キー取得APIを使います。取得は同じ接続の挿入処理と結び付け、SELECT MAX(id)で推測してはいけません。別の利用者が同時にINSERTすると、最大値は自分が作成した行とは限らないからです。接続プールを使う場合も、INSERTと取得の間で物理接続が変わらないAPIを選びます。

複数行INSERTでどのIDが返るか、トリガー内で別表へINSERTした場合にドライバーが何を返すかは、採用するAPIと製品仕様で確認します。アプリケーション側では、生成キーを受け取った後に対象行の業務キーも照合し、別行へ紐付ける事故を防ぎます。主キー値をクライアントが先読みして予約する設計は、並行処理と障害復旧を複雑にします。

欠番と順序を正しく理解する

InnoDBでは一度生成された自動採番値は、挿入が失敗したりトランザクションがロールバックされたりしても失われ、再利用されないことがあります。並列INSERT、バルクINSERT、INSERT ... SELECT、サーバー障害からの復旧でも、値の並びを業務上の連続番号として期待すべきではありません。欠番を埋めるために既存IDを更新すると、外部キー、ログ、キャッシュ、監査証跡が壊れる危険があります。

数値の大小は概ね生成順を反映する場面がありますが、厳密なイベント時刻や確定順序の代わりにはなりません。トランザクションAが先にIDを得ても、トランザクションBが先にコミットすることがあります。表示順や処理順が必要なら、確定時刻、状態遷移番号、業務シーケンスなど目的専用の列を持たせます。

InnoDBの並行INSERT

MySQLのInnoDBにはinnodb_autoinc_lock_modeがあり、単純INSERT、バルクINSERT、混在INSERTの並行性と採番方法に影響します。設定変更は複製方式や再現性にも関係するため、記事中の数値だけを見て変更してはいけません。現在値、バイナリログ形式、レプリカ構成、実際のINSERT形態をDBAと確認し、負荷試験と復旧試験を行います。通常のアプリ開発者は既定値を前提にしつつ、IDの連続性へ依存しない実装にします。

現在値の確認と変更

次に採番される値はメタデータから確認できますが、その値は並行処理で直ちに変わり得ます。確認結果を予約値として使わないでください。InnoDBではALTER TABLE ... AUTO_INCREMENT = Nでカウンターを指定できますが、公式資料では現在列に存在する最大値以下へ下げる用途には制約があります。既存行と衝突する値を強制するとINSERT失敗や不整合の原因になります。

本番でのリセット要求が『テストデータを消して1から始めたい』という意味なら、まず環境を取り違えていないか、外部キーや監査保存義務がないかを確認します。TRUNCATE TABLEは全行を削除する破壊的操作であり、採番を戻すためだけに実行してはいけません。バックアップを作成し、復元テスト、承認、メンテナンス時間、アプリ停止を揃えられない場合は実施しません。

既存テーブルへ追加する場合

既存データがある表へ自動採番列を追加すると、行への値付与、表再構築、ロック、外部キー追加が発生し得ます。先にステージング環境で同じ行数と索引構成を使って所要時間を測ります。大規模表ではオンラインDDLの対応可否やレプリカ遅延も確認し、アプリケーションの読み書きを止めるか段階移行にするかを決めます。

1. スキーマ、行数、外部キー、トリガー、レプリケーション、バックアップ復元時間を確認する。
2. 追加後の型と符号、主キー・一意索引、参照側の型を設計する。
3. 本番相当データでDDL時間、ロック、ディスク使用量、レプリカ遅延を測る。
4. 変更前に復元可能なバックアップと、旧スキーマへ戻す手順を用意する。
5. 変更後に件数、重複、NULL、外部キー、生成キー取得、同時INSERTを検証する。

主キーを自動採番へ置き換える移行では、新旧キーの対応表を保持します。外部表を一度に更新せず、参照整合性を確認しながら段階的に切り替えます。アプリが旧キーをURLや外部APIへ公開している場合、DB内の変更だけでは終わりません。キャッシュ、検索索引、メッセージ、データウェアハウスまで影響範囲を洗い出してください。

PostgreSQLとSQL Serverへの置き換え

PostgreSQLのidentity列

PostgreSQLではGENERATED ALWAYS AS IDENTITYまたはGENERATED BY DEFAULT AS IDENTITYを使えます。ALWAYSは通常のINSERTで明示値を受け入れにくくし、BY DEFAULTは明示値が指定されたときそちらを使う設計です。移行ツールが既存IDを投入する必要があるかを基準に選びます。identity列でも一意性は自動的に主キーを意味しないため、必要なPRIMARY KEYやUNIQUE制約を別に定義します。

SQL ServerのIDENTITY

SQL ServerのIDENTITY(seed, increment)は、初期値と増分を列定義で指定します。値の一意性そのものは主キーや一意制約で保証します。明示値を挿入するIDENTITY_INSERTは移行など限定用途にとどめ、通常処理へ組み込まないでください。生成値の取得は接続ライブラリやOUTPUT句など、対象SQL Server版の公式手段を選びます。

DB移行ではキーワードだけ置換しても不十分です。開始値、増分、最大型、欠番、トランザクション、複数行INSERTの返却、バックアップからの復元、複製構成を比較します。ORMを使っていても、実際に発行されるDDLとINSERTをログで確認し、同時実行テストを行います。

設計上の注意

• 自動採番IDだけで業務上の重複を防がず、注文番号や外部IDには別の一意制約を置く。
• URLへ連番を出すと件数推測を招くため、認可を必ず実装し、必要なら公開用識別子を分ける。
• ID型は親表と子表で符号・サイズまで一致させ、暗黙変換を避ける。
• 削除済みIDを再利用せず、監査ログやイベントとの参照を安定させる。
• 採番値を時刻、件数、処理成功の証明として使わない。
• トリガーで独自連番を作る前に、並行性、デッドロック、障害復旧を設計レビューする。

元記事から引き継いだSQL例

以下のカスタムコードブロックは元記事から文字列を変えずに引き継いでいます。リセット、手動設定、トリガーなど運用リスクの高い例も含まれるため、上記の製品別仕様とバックアップ方針を優先してください。本番では対象表名、既存最大値、外部キー、権限を確認し、破壊的な削除や採番の巻き戻しを行わないでください。

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');
INSERT INTO users (username, email) VALUES ('jane_smith', '[email protected]');

SHOW TABLE STATUS LIKE 'users';

ALTER TABLE users AUTO_INCREMENT = 1;

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) AUTO_INCREMENT=1000;

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE TABLE order_items (
    item_id INT NOT NULL AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (item_id)
);

CREATE TRIGGER before_insert_orders<br>BEFORE INSERT ON orders<br>FOR EACH ROW<br>BEGIN<br>    SET NEW.order_date = NOW();<br>END;

ALTER TABLE users AUTO_INCREMENT = 1000;

SET @@auto_increment_increment = 2;

トラブルシューティング

採番上限が迫っている、レプリケーション構成で値が衝突する、既存主キーを変更する、数億行規模の表へDDLを行う、誤ってTRUNCATEや大量削除を実行した場合は、自己判断で値を調整せずDBAへ連絡します。障害時には書き込みを止め、実行したSQL、時刻、接続先、影響件数、バックアップ時点を保存してください。

よくある質問

AUTO_INCREMENTの欠番は直すべきですか？

通常は直しません。ロールバックや並行処理で欠番は発生します。主キーの役割は一意な識別であり、連続性が必要な業務番号は別に設計します。

次のIDをSELECT MAX(id)+1で作れますか？

同時実行で同じ値を計算する競合が起きるため使いません。DBMSの自動採番機能と、同一接続の生成キー取得手段を使います。

削除後に1から振り直してよいですか？

本番では参照、監査、外部連携を壊す可能性があります。テスト専用環境でも接続先、バックアップ、外部キーを確認し、全行削除を伴う操作は承認後に限定します。

AUTO_INCREMENT列は主キーになりますか？

属性だけでは主キー制約を意味しません。一般にはPRIMARY KEYまたは必要な一意索引を明示します。

UUIDとどちらがよいですか？

単一DB内の小さく効率的なキーなら連番が扱いやすい一方、分散生成や外部公開ではUUID系が適する場合があります。索引サイズ、生成場所、公開要件で選び、認可はどちらでも必須です。

PostgreSQLでもAUTO_INCREMENTと書けますか？

同じキーワードを前提にせず、現在はidentity列を検討します。MySQL、PostgreSQL、SQL ServerでDDLと生成値取得方法を分けてください。

公式情報源

• MySQL 公式リファレンス
• MySQL 公式リファレンス
• PostgreSQL 公式ドキュメント
• Microsoft Learn

まとめ

MySQLのAUTO_INCREMENTは、連番主キーをDBに生成させる便利な属性です。ただし欠番は正常に起こり、順序や業務番号を保証しません。ID列を省略してINSERTし、同じ接続の公式APIで生成値を受け取り、SELECT MAXや採番の巻き戻しを避けてください。PostgreSQLはidentity列、SQL ServerはIDENTITYを使い、既存表の変更やリセットはバックアップと切り戻しを含む移行作業として扱います。