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)』より）
