MySQLでレコード削除後にキーを並べ替える方法をわかりやすく解説

MySQLでレコード削除後にキーを並べ替えるには？

MySQLのテーブルからレコードを削除すると、IDに欠番（ギャップ）が生じることがあります。この欠番を埋めてキーを連続した値に並べ替えたい場合は、UPDATE文に数値計算を組み合わせることで実現できます。レコードの削除にはDELETE文を使用します。

ここでは、実際のサンプルコードを使いながら手順を順番に解説していきます。

1. テーブルを作成する

まず、サンプル用のテーブルを作成します。

mysql> create table DemoTable1476
    -> (
    -> Id int
    -> );
Query OK, 0 rows affected (0.81 sec)

2. レコードを挿入する

INSERT文を使って、テーブルにいくつかのレコードを追加します。

mysql> insert into DemoTable1476 values(10);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1476 values(20);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1476 values(30);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1476 values(40);
Query OK, 1 row affected (0.12 sec)

3. レコードを確認する

SELECT文で、テーブル内のすべてのレコードを表示してみましょう。

mysql> select * from DemoTable1476;

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

+------+
| Id   |
+------+
|   10 |
|   20 |
|   30 |
|   40 |
+------+
4 rows in set (0.00 sec)

4. レコードを削除する

次に、DELETE文を使って特定のID（ここではId=30）のレコードを削除します。

mysql> delete from DemoTable1476 where Id=30;
Query OK, 1 row affected (0.16 sec)

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

mysql> select * from DemoTable1476;

実行結果は以下のとおりです。Id=30が削除され、欠番が発生していることがわかります。

+------+
| Id   |
+------+
|   10 |
|   20 |
|   40 |
+------+
3 rows in set (0.00 sec)

5. UPDATE文でキーを並べ替える

ここが本題のポイントです。削除によって生じた欠番を埋めるために、UPDATE文で削除した値より大きいIDを一括して減算します。

mysql> update DemoTable1476 set Id=Id-10 where Id > 30;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1 Warnings: 0

このクエリでは、「Id > 30」という条件に一致するレコードのIdから10を引くことで、欠番分だけ値を詰めています。

6. 最終結果を確認する

もう一度テーブルのレコードを表示して、並べ替えが正しく行われたか確認しましょう。

mysql> select * from DemoTable1476;

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

+------+
| Id   |
+------+
|   10 |
|   20 |
|   30 |
+------+
3 rows in set (0.00 sec)

まとめ

MySQLでレコードを削除した後にキーを並べ替えるには、以下の手順が有効です。

1. DELETE文で対象のレコードを削除する
2. UPDATE文で「Id=Id-削除した間隔」のように数値計算を行い、条件（例：Id > 削除した値）に一致するレコードのIDを詰める

この方法を使えば、削除後の欠番を解消し、IDを連続した値に整理できます。なお、実際の運用ではAUTO_INCREMENTのIDを頻繁に並べ替えると外部キーとの整合性に影響を与える可能性があるため、適用する際はデータベース設計全体を考慮することをおすすめします。