MySQLで削除後のAUTO_INCREMENTフィールドを振り直す方法｜1,3,5を1,2,3に連番化する

MySQLでは、行を削除した後もAUTO_INCREMENTの値は欠番のまま残ります。例えば、IDが「1,2,3,4,5」のテーブルから「2」と「4」を削除すると、残るのは「1,3,5」です。この記事では、この欠番を詰めて「1,2,3」という連番に振り直す方法を、実際のSQL文と実行結果とともにわかりやすく解説します。

1. サンプルテーブルを作成する

まず、AUTO_INCREMENTフィールドを持つテーブルを作成します。

mysql> create table DemoTable1955
    (
    UserId int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(UserId)
    );
Query OK, 0 rows affected (0.00 sec)

2. テストデータを挿入する

INSERTコマンドを使って、5件のレコードを挿入します。値を指定しなくても、AUTO_INCREMENTによって自動的に連番が割り振られます。

mysql> insert into DemoTable1955 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1955 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1955 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1955 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1955 values();
Query OK, 1 row affected (0.00 sec)

3. 挿入されたデータを確認する

SELECT文で全レコードを表示します。

mysql> select * from DemoTable1955;

実行結果は以下のとおりです。

+--------+
| UserId |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
+--------+
5 rows in set (0.00 sec)

4. 行を削除して欠番を作る

次に、UserIdが「2」と「4」の行を削除します。

mysql> delete from DemoTable1955 where UserId IN(2,4);
Query OK, 2 rows affected (0.00 sec)

削除後にテーブルを確認してみましょう。

mysql> select * from DemoTable1955;

実行結果：

+--------+
| UserId |
+--------+
|      1 |
|      3 |
|      5 |
+--------+
3 rows in set (0.00 sec)

このように、削除された行の分だけ欠番が発生し、「1,3,5」という不連続な状態になっています。

5. ユーザー変数を使って連番に振り直す

欠番を詰めて「1,2,3」に振り直すには、ユーザー定義変数を利用したUPDATE文が便利です。以下のクエリでは、変数@increment_valueを1ずつ増加させながら、UserIdを昇順に並べ替えて更新しています。

mysql> update DemoTable1955
    set UserId = (@increment_value := @increment_value + 1)
    order by UserId;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

このクエリのポイントは以下の2つです。

• ユーザー変数の初期化：変数が未定義の場合、NULL + 1 はNULLになるため、事前に SET @increment_value = 0; を実行しておくと安全です。
• ORDER BY句：更新順序をUserIdの昇順に指定することで、必ず小さい方から連番が割り振られます。

6. 更新結果を確認する

最後に、もう一度テーブルの内容を確認します。

mysql> select * from DemoTable1955;

実行結果：

+--------+
| UserId |
+--------+
|      1 |
|      2 |
|      3 |
+--------+
3 rows in set (0.00 sec)

これで、欠番のあった「1,3,5」が「1,2,3」という連番に振り直されました。

補足：注意点と代替方法

なお、AUTO_INCREMENTの主キーを更新する操作は、以下の点に注意が必要です。

• 外部キー制約：他のテーブルからこのIDを参照している場合、参照整合性が崩れる可能性があります。事前に外部キー制約を一時的に無効化するか、参照側も同時に更新する必要があります。
• AUTO_INCREMENTカウンタのリセット：連番を振り直した後は、ALTER TABLE DemoTable1955 AUTO_INCREMENT = 4; のように次回の採番値を調整しておくと、重複を防げます。
• テーブル再作成方式：MySQL 8.0以降ではウィンドウ関数（ROW_NUMBER）を使った方法や、新しいテーブルにデータを移し替えてから元のテーブルと入れ替える方法も有効です。

ユーザー変数を使った方法はシンプルで手軽なため、テストデータの整理や小規模なテーブルの採番修正に最適です。