MySQLで1行を削除し、残りの行を正しいID順に並べ替える方法

MySQLで行を削除してIDを振り直す基本の手順

MySQLでは、特定の行を削除した後に、残りの行のIDを詰めて連番のように並べ替えたいケースがあります。ここでは、DELETE文で1行を削除し、その後UPDATE文を使って他の行のIDを調整する具体的な手順を解説します。

ステップ1：サンプルテーブルを作成する

まず、動作を確認するためのテーブルを作成しましょう。テーブル作成のクエリは以下の通りです。

mysql> create table ReorderSortDemo
-> (
-> UserId int
-> );
Query OK, 0 rows affected (0.57 sec)

ステップ2：テストデータを挿入する

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

mysql> insert into ReorderSortDemo values(14);
Query OK, 1 row affected (0.13 sec)
mysql> insert into ReorderSortDemo values(4);
Query OK, 1 row affected (0.10 sec)
mysql> insert into ReorderSortDemo values(6);
Query OK, 1 row affected (0.11 sec)
mysql> insert into ReorderSortDemo values(3);
Query OK, 1 row affected (0.09 sec)
mysql> insert into ReorderSortDemo values(8);
Query OK, 1 row affected (0.11 sec)
mysql> insert into ReorderSortDemo values(18);
Query OK, 1 row affected (0.08 sec)
mysql> insert into ReorderSortDemo values(1);
Query OK, 1 row affected (0.12 sec)
mysql> insert into ReorderSortDemo values(11);
Query OK, 1 row affected (0.08 sec)
mysql> insert into ReorderSortDemo values(16);
Query OK, 1 row affected (0.09 sec)

ステップ3：現在のレコードを確認する

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

mysql> select *from ReorderSortDemo;

実行結果は以下のようになります。

+--------+
| UserId |
+--------+
|     14 |
|      4 |
|      6 |
|      3 |
|      8 |
|     18 |
|      1 |
|     11 |
|     16 |
+--------+
9 rows in set (0.00 sec)

ステップ4：対象の行を削除する

次に、DELETE文で1行を削除します。ここでは「UserId = 8」の行を削除します。

mysql> delete from ReorderSortDemo where UserId=8;
Query OK, 1 row affected (0.20 sec)

削除後、テーブルの内容を再度確認してみます。

mysql> select *from ReorderSortDemo;

出力結果は以下の通りです。「UserId = 8」の行がなくなっていることがわかります。

+--------+
| UserId |
+--------+
|     14 |
|      4 |
|      6 |
|      3 |
|     18 |
|      1 |
|     11 |
|     16 |
+--------+
8 rows in set (0.00 sec)

ステップ5：UPDATE文でIDを詰めて並べ替える

削除されたIDより大きい値を持つ行に対して、UPDATE文で「UserId - 1」を設定することで、IDの欠番を埋めることができます。

mysql> update ReorderSortDemo
-> set UserId=UserId-1
-> where UserId > 8;
Query OK, 4 rows affected (0.22 sec)
Rows matched: 4 Changed: 4 Warnings: 0

ステップ6：最終結果を確認する

更新後のテーブルをもう一度確認しましょう。

mysql> select *from ReorderSortDemo;

出力結果は以下の通りです。削除前のID「9」以降の値がそれぞれ1つずつ繰り上がり、欠番がなくなったことが確認できます。

+--------+
| UserId |
+--------+
|     13 |
|      4 |
|      6 |
|      3 |
|     17 |
|      1 |
|     10 |
|     15 |
+--------+
8 rows in set (0.00 sec)

まとめ

このように、DELETE文で行を削除した後、UPDATE文と条件句（WHERE句）を組み合わせることで、残りの行のIDを簡単に詰めて並べ替えることができます。ただし、実際の運用ではAUTO_INCREMENT列の採番を気にせず、IDの連続性に依存しない設計にすることも検討するとよいでしょう。