[MySQL] AUTO_INCREMENTのリセット方法を考える。

エンジニアたるもの、データベースを上手に使いこなすことが求められます。 でも、そもそも設計がイケていないデータベースの場合、どんなにあがいても活用するのが難し場面も多いので、 メンテナンス方法も含め、いざ自分が設計をする場面になったときに、いろいろな手法を知っておかないといけません。 前置きが長くなりましたが、今仕事で使っているデータベースで、ID値にINTが指定されていて、日々大量のincrementが行われているテーブルがあって、 たまに不要なデータや間違って登録されたデータをごっそりと削除される場面を見つけてしまいました。 その時に採番されるIDに大きな抜け穴がポッカリと空いてしまうのが少し気持ちが悪かったので、 何か対応方法が無いか考えたことを、ブログに残しておきます。

AUTO_INCREMENTについて

データベース活用で、安定して使える為ポイントの一つに、UNIQUEなID値（idじゃなくてもいいけど）を持たせることがあります。 通常ID値は、AUTO_INCREMENTとして、次のように書くだけで、自動的に採番されてそのテーブルのユニークコードとして扱うことができ、 別テーブルからのリレーションなどの対応に使うのが一般的です。 CREATE TABLE user( id INT AUTO_INCREMENT PRIMARY KEY, uuid VARCHAR(100), mail VARCHAR(100) NOT NULL, pass VARCHAR(100) NOT NULL, status VARCHAR(10), create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); でも、このAUTO_INCREMENTって、一度でも採番されると、DELETEをしても、その後２度と同じ数値は扱えません。 要するに、1~5番まで採番された場合、2番を削除(DELETE)すると、2番は永久欠番という事になります。 この状態で2番が永久欠番はわかるんですが、5番を削除した場合、次に採番されるのは、6番になります。 できれば、DELETEしたタイミングで、INCREMENTが最終値も現状最終値にセットしてくれればいいんですが、 MySQLはそこまでの親切機能はありません。 INT設定も、数値の上限があるので、枯渇しないようにする必要もあるので、それも踏まえた設計が必要です。

AUTO_INCREMENTの最終値を変更する方法

永久欠番と言っても、MySQLのコマンドで、AUTO_INCREMENTの最終値を変更することができます。 1. 現在登録されている最終値(id)を取得 SELECT MAX(id) FROM your_table; 2. 取得した値の +1 で設定 ALTER TABLE your_table AUTO_INCREMENT = 取得した値+1; ユーザーログデータなどで、激しくレコードを消費する場合、最終idのレコードを削除したタイミングでこの処理を挟むことで、 まあまあ見た目が綺麗なidの並びになります。 注意ポイントとしては、運用しているテーブルでやる場合は、ちゃんとトランザクションをセットして運用しないと incrementがユニークじゃ無くなる可能せもありますからね。

開発時などでのテーブル一括削除のお作法

上記を元に考えた時に、検証環境などで、ローカルでSQLなどがDockerでセットされているような場合、 DELETE直後に、以下のコマンドを実行するだけでいいです。 1. テーブルデータの一括削除 DELETE FROM your_table; 2. AUTO_INCREMENTをリセット ALTER TABLE your_table AUTO_INCREMENT = 1; 常に1番から始まる気持ちよさを体験できるでしょう。

他の方法を検討

ALTER TABLEは、テーブルの設定をいじるので、本番で使うのは躊躇してしまいます。 なので他の方法を検討してみたいと思います。

1. 「仮登録」用テーブルを設ける

Excel取り込みなどを一旦 temp_xxx テーブルに入れる。 確定時に本番テーブルへ INSERT。 この時点で確定したレコードにだけ AUTO_INCREMENT が消費される。 メリット： 本番テーブルに不要な「やり直しデータ」が入らず、番号も綺麗。 デメリット： テーブル構造や処理フローが少し複雑に。

2. 「ID見た目用」別カラムを用意する

id（内部識別）とは別に display_id を設ける。 CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, display_id INT UNIQUE, ... ); display_id は空いている最小値を検索して手動で設定 SELECT MIN(t1.id + 1) AS next_available FROM orders t1 LEFT JOIN orders t2 ON t1.id + 1 = t2.display_id WHERE t2.display_id IS NULL; メリット： 抜け番を詰められる／可視上の連番を維持できる デメリット： 処理は少し複雑でINSERTロジックの工夫が必要

シーケンス専用テーブルを作ってIDを管理する

auto_increment を使わず、独自の連番をテーブルで管理 CREATE TABLE sequence ( name VARCHAR(50) PRIMARY KEY, current_value INT ); 登録前に最小の空き番号を探して使う方式。 メリット： 完全に自由な連番制御 デメリット： ロジック構築コストが高い／パフォーマンス面の考慮が必要

あとがき

基本的にAUTO_INCREMENTの値はイジらないのがオススメです。 ブログでここまで書いていて、いろいろな方法を考えてみたけど、どれも必ずデメリットが存在するので、 いっそのこと「抜け番を気にしない」というマインドセットが一番効率的だという結論にたどりつきました。（なんじゃそりゃ） でも、開発環境でのINCREMENTリセットは、やる価値はありそうです。 まあ何にせよ、こうした手順も知らないよりも知っておいた方がいいという知識の泉の話でした。