連番やデータ番号のすきまを埋めるには

PHPエラーの原因と対策
141 view

　1、2、3…と続くデータ番号。データが削除されると1、3、4、5…のようにすきまができるのが普通です。通常は、このすきまを埋める必要はありませんが、番号が「座席番号」のような用途の場合、すきまを埋めたいこともあります。

　たとえば、次のようなデータがあります。id列は連番を割り当てていますが、何件かのデータが削除されたことですきまができています。次に追加するidを「8」ではなく、すきまの先頭の「2」にしたい場合、どうすればよいでしょうか。

mysql> SELECT id, name FROM members6;
+----+----------+
| id | name     |
+----+----------+
|  1 | 桜井洋子 |
|  3 | 横山和弘 |
|  4 | 金田由美 |
|  5 | 山本正人 |
|  7 | 斉藤高志 |
+----+----------+
5 rows in set (0.00 sec)

　まず、テーブルを自己結合して、id列を並べてみます。自己結合とは、1つのテーブルを“1人2役”のように使い、同じテーブルを結合する操作です。

mysql> SELECT a.id, b.id
    -> FROM members6 a LEFT OUTER JOIN members6 b
    -> ON a.id = b.id;
+----+------+
| id | id   |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  7 |    7 |
+----+------+
5 rows in set (0.01 sec)

　上の例ではid列が同じ値同士を結合しましたが、今度は、1つ番号をずらして結合してみます。つまり、左側のid=1の行は右側のid=2の行、左側のid=2の行は右側のid=3の行と結合します。それには、ON句を変更して、左側のテーブルのid列に1を加算した値と結合します。

mysql> SELECT a.id, b.id AS next_id
    -> FROM members6 a LEFT OUTER JOIN members6 b
    -> ON a.id + 1 = b.id;
+----+---------+
| id | next_id |
+----+---------+
|  1 |    NULL |
|  3 |       4 |
|  4 |       5 |
|  5 |    NULL |
|  7 |    NULL |
+----+---------+
5 rows in set (0.00 sec)

　すると、next_idがNULLになるデータが発生します。これは、「自分のid + 1」というデータが存在しないことを示しています。つまり、「1」の次の「2」が存在せず、また、「5」の次の「6」が存在しないのです。この結果から、「2」と「6」が欠けていることがわかります。
　next_idがNULLのデータ（b.id IS NULL）の中から、最も小さい「1」に1を加算した「2」を取得すれば、次のデータに割り当てる「すきま」を取得することができます。SQL文は次のとおりです。

mysql> SELECT MIN(a.id) + 1
    -> FROM members6 a LEFT OUTER JOIN members6 b
    -> ON a.id + 1 = b.id
    -> WHERE b.id IS NULL;
+---------------+
| MIN(a.id) + 1 |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

　この値をINSERT文に反映すれば、連番のすきまをうめていくことができます。次の例ではid列に「2」が割り当てられています。

mysql> INSERT INTO members6(id, name, age)
    ->   SELECT MIN(a.id) + 1, '佐藤史郎', 29
    ->   FROM members6 a LEFT OUTER JOIN members6 b
    ->   ON a.id + 1 = b.id
    ->   WHERE b.id IS NULL;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT id, name FROM members6 ORDER BY id;
+----+----------+
| id | name     |
+----+----------+
|  1 | 桜井洋子 |
|  2 | 佐藤史郎 |
|  3 | 横山和弘 |
|  4 | 金田由美 |
|  5 | 山本正人 |
|  7 | 斉藤高志 |
+----+----------+
6 rows in set (0.00 sec)

（西沢直木著：『SQL逆引きレシピ (PROGRAMMER’S RECiPE)』より）