MySQL の AUTO_INCREMENT（自動で連番を振る機能）は、行を消しても番号が戻りません。テストデータを入れ直したら id が 101 から始まった、ということがよく起きます。次に振られる番号は SHOW CREATE TABLE で確かめられ、1から振り直すには TRUNCATE か ALTER TABLE を使います。以下では MySQL 8.0 の例で、その手順と、欠番ができる理由、欠番を埋めないほうがよい理由を順に示します。
このシナリオで考える
注文を記録する orders テーブルを使います。id 列に AUTO_INCREMENT を付けているので、INSERT で id を書かなければ 1, 2, 3 と番号が振られます。
CREATE TABLE orders (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
customer VARCHAR(20) NOT NULL,
amount INT NOT NULL
);
INSERT INTO orders (customer, amount)
VALUES ('佐藤', 1200), ('鈴木', 800), ('高橋', 3500);
SELECT * FROM orders;+----+----------+--------+
| id | customer | amount |
+----+----------+--------+
| 1 | 佐藤 | 1200 |
| 2 | 鈴木 | 800 |
| 3 | 高橋 | 3500 |
+----+----------+--------+以下の例は、この3行が入った状態から続けて実行していきます。
次に振られる番号を確かめる
テーブルが持っている「次の番号」は、SHOW CREATE TABLE の最後の行で確かめられます。いちばん確実な方法です。
SHOW CREATE TABLE orders;-- 出力の最後の行
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciAUTO_INCREMENT=4 は、次の INSERT で id が 4 になるという意味です。テーブルが空で一度も INSERT していないときは、この表示自体が出ません。
確かめ方はほかにもあります。用途がそれぞれ違うので、表に分けました。
| 方法 | わかること | 注意点 |
|---|---|---|
SHOW CREATE TABLE |
テーブルの次の番号 | 出力が長いので最後の行を見る |
information_schema.TABLES の AUTO_INCREMENT 列 |
テーブルの次の番号 | MySQL 8.0 では古い値が出ることがある（落とし穴の節で説明） |
SHOW TABLE STATUS |
テーブルの次の番号 | 上と同じしくみなので、古い値が出ることがある |
SELECT LAST_INSERT_ID() |
自分の接続で最後に振られた番号 | 複数行を1文で入れたときは、最初の行の番号が返る |
LAST_INSERT_ID() はテーブルではなく接続ごとの値です。ほかの人の INSERT には影響されません。アプリで「今入れた行の id」を取るときはこちらを使います。上の3行を1文で入れた直後なら、返るのは 3 ではなく 1 です。
行を消しても番号が詰まらない理由
AUTO_INCREMENT は一度配った番号を二度と使いません。行を消しても、ロールバックしても同じです。id が 4 の行を入れて消し、もう1行入れると 5 になります。
INSERT INTO orders (customer, amount) VALUES ('田中', 500); -- id 4
DELETE FROM orders WHERE id = 4;
INSERT INTO orders (customer, amount) VALUES ('伊藤', 700); -- id 5
START TRANSACTION;
INSERT INTO orders (customer, amount) VALUES ('渡辺', 900); -- id 6
ROLLBACK;
INSERT INTO orders (customer, amount) VALUES ('山本', 1500); -- id 7
SELECT * FROM orders;+----+----------+--------+
| id | customer | amount |
+----+----------+--------+
| 1 | 佐藤 | 1200 |
| 2 | 鈴木 | 800 |
| 3 | 高橋 | 3500 |
| 5 | 伊藤 | 700 |
| 7 | 山本 | 1500 |
+----+----------+--------+ロールバックした INSERT の 6 も欠番になりました。番号を戻すと、同時に動いているほかの INSERT と番号がぶつかるおそれがあります。だから MySQL は配った番号を戻しません。欠番ができる主な場面は次のとおりです。
| 場面 | 欠番になる理由 |
|---|---|
| DELETE で行を消した | 消した行の番号は再利用されない |
| トランザクションをロールバックした | INSERT の時点で番号はもう配られている |
INSERT ... ON DUPLICATE KEY UPDATE で更新になった |
追加用に取った番号が使われずに残る |
| INSERT … SELECT などをいくつも同時に流した | 8.0 の既定の設定（innodb_autoinc_lock_mode=2）では、まとめて取った番号が余ることがある |
MySQL 5.7 までは、サーバーを再起動すると次の番号が「今の最大値＋1」に戻っていました。8.0 からは次の番号がディスクに残るので、再起動しても欠番は埋まりません。
ALTER TABLE で次の番号を変える
次の番号を自分で決めたいときは ALTER TABLE ... AUTO_INCREMENT = N を使います。ただし、列に入っている最大値より大きい番号にしか変えられません。
-- 最大値（7）以下を指定しても、次の番号は 8 のまま
ALTER TABLE orders AUTO_INCREMENT = 3;
-- 最大値より大きければ、その番号から振られる
ALTER TABLE orders AUTO_INCREMENT = 100;
INSERT INTO orders (customer, amount) VALUES ('中村', 2000);
SELECT * FROM orders WHERE id >= 7;+-----+----------+--------+
| id | customer | amount |
+-----+----------+--------+
| 7 | 山本 | 1500 |
| 100 | 中村 | 2000 |
+-----+----------+--------+小さい値を指定してもエラーは出ません。気づかないまま「戻したつもり」になりやすいので、変えたあとは SHOW CREATE TABLE で確かめてください。
番号を1から振り直す（TRUNCATE と DELETE）
テストデータを全部消して1から始めたいなら、TRUNCATE TABLE がいちばん手早い方法です。TRUNCATE はテーブルを作り直すので、番号も最初の値に戻ります。DELETE FROM orders で全行を消しただけでは、番号は戻りません。
DELETE FROM orders; -- 全行を消す
INSERT INTO orders (customer, amount) VALUES ('小林', 400);
-- id は 101 になる
TRUNCATE TABLE orders; -- 中身を空にして番号も戻す
INSERT INTO orders (customer, amount)
VALUES ('佐藤', 1200), ('鈴木', 800);
SELECT * FROM orders;+----+----------+--------+
| id | customer | amount |
+----+----------+--------+
| 1 | 佐藤 | 1200 |
| 2 | 鈴木 | 800 |
+----+----------+--------+ほかのテーブルから外部キーで参照されているテーブルは、TRUNCATE できません。子テーブルが空でもエラー 1701（Cannot truncate a table referenced in a foreign key constraint）になります。そのときは次の順で振り直します。
- 子テーブル（例：
order_items）の行を DELETE で消す - 親テーブル（
orders）の行を DELETE で消す ALTER TABLE orders AUTO_INCREMENT = 1;を実行する
DELETE FROM order_items;
DELETE FROM orders;
ALTER TABLE orders AUTO_INCREMENT = 1;
INSERT INTO orders (customer, amount) VALUES ('加藤', 600); -- id 1テーブルが空なら最大値がないので、1 を指定できます。DELETE・TRUNCATE・DROP の違いは「MySQLのDELETE・TRUNCATE・DROPの違いと使い分け」で説明しています。
よくある落とし穴
欠番を詰めようとして id を振り直さないでください。 id はほかのテーブルの外部キーや、URL・ログ・メールに書かれた注文番号から参照されています。番号を変えると、そうした参照がすべて別の行を指すか、どこも指さなくなります。主キーは「行を見分けるための値」で、並び順や件数を表す値ではありません。主キーの考え方は「MySQLの主キー・外部キー設計：自然キーとサロゲートキーの基本」にまとめています。
画面に 1, 2, 3 と通し番号を出したいだけなら、表示するときに ROW_NUMBER() で振れば足ります。
-- id が 1, 3, 4 の行があるとき
SELECT ROW_NUMBER() OVER (ORDER BY id) AS no, id, customer
FROM orders;+----+----+-----------+
| no | id | customer |
+----+----+-----------+
| 1 | 1 | 加藤 |
| 2 | 3 | 山田 |
| 3 | 4 | 佐々木 |
+----+----+-----------+もう1つの落とし穴は、information_schema.TABLES で見た番号が古いままのことです。MySQL 8.0 はこの表の統計の値を、既定で24時間ためておきます（information_schema_stats_expiry = 86400）。INSERT したあとも前の番号が出ることがあるので、確かめるときは次のどちらかを使ってください。
-- その接続だけ、ためた値を使わないようにする
SET SESSION information_schema_stats_expiry = 0;
SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders';
-- または、そのテーブルの統計を取り直す
ANALYZE TABLE orders;INT の上限にも気をつけてください。INT UNSIGNED は約42億、INT（符号あり）は約21億で番号が尽き、INSERT がエラーになります。欠番が多いテーブルでは、件数より早く上限に近づきます。何十億件も入りそうなら、最初から BIGINT UNSIGNED にしておくと安心です。
まとめ
次の番号は SHOW CREATE TABLE で確かめるのがいちばん確実です。番号を1から振り直すなら TRUNCATE、外部キーで参照されているテーブルなら DELETE のあとに ALTER TABLE ... AUTO_INCREMENT = 1 を使います。ALTER TABLE は最大値より大きい番号にしか変えられません。欠番は DELETE やロールバックで必ずできるもので、詰めると外部キーや外部に書かれた番号との対応が崩れます。通し番号が欲しいときは、表示のときに ROW_NUMBER() で振ってください。
もっと学ぶなら【PR】
- MySQL徹底入門 第5版：MySQL の機能を広く解説した本。日付関数などの細かい動きを調べたいときに
- SQLアンチパターン 第2版：テーブル設計やクエリでやりがちな失敗と、その直し方をまとめた本
※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 Reference Manual: AUTO_INCREMENT Handling in InnoDB
- MySQL 8.0 Reference Manual: Using AUTO_INCREMENT
- MySQL 8.0 Reference Manual: TRUNCATE TABLE Statement
- MySQL 8.0 Reference Manual: The INFORMATION_SCHEMA TABLES Table
アイキャッチ画像: Photo by Brett Jordan on Unsplash
