MySQLのLEFT JOINを使って連番の未使用最小値を検索する方法

MySQLテーブルで未使用の最小値を見つけるには？

MySQLのテーブルから、連番の中で使われていない最小の値を調べたいケースはよくあります。例えば、採番管理やIDの再利用などがその代表例です。こうした場合、LEFT JOIN（左外部結合）を使うことで効率的に未使用の値を検出できます。

この記事では、実際にサンプルテーブルを作成し、未使用の最小値を取得するクエリの手順を解説します。

1. サンプルテーブルの作成

まずは、連番を格納するためのテーブル「FindValue」を作成しましょう。以下のようにCREATE TABLE文を実行します。

mysql> create table FindValue
    -> (
    -> SequenceNumber int
    -> );
Query OK, 0 rows affected (0.56 sec)

2. テストデータの挿入

次に、INSERT文を使っていくつかのレコードを挿入します。あえて「112」だけをスキップした連続した数値を登録してみましょう。

mysql> insert into FindValue values(109);
Query OK, 1 row affected (0.14 sec)
mysql> insert into FindValue values(110);
Query OK, 1 row affected (0.15 sec)
mysql> insert into FindValue values(111);
Query OK, 1 row affected (0.13 sec)
mysql> insert into FindValue values(113);
Query OK, 1 row affected (0.13 sec)
mysql> insert into FindValue values(114);
Query OK, 1 row affected (0.17 sec)

3. 登録データの確認

SELECT文ですべてのレコードを表示して、正しく登録されていることを確認します。

mysql> select * from FindValue;

実行結果は以下のとおりです。「112」が欠けていることがわかります。

+----------------+
| SequenceNumber |
+----------------+
|            109 |
|            110 |
|            111 |
|            113 |
|            114 |
+----------------+
5 rows in set (0.00 sec)

4. 未使用の最小値を検索するクエリ

ここからが本題です。同じテーブル同士をLEFT JOINで結合し、「自分の次の値（SequenceNumber + 1）」が存在しない行を抽出します。さらにORDER BYとLIMIT 1を組み合わせることで、最初に見つかった欠番＝未使用の最小値を取得できます。

mysql> select tbl1.SequenceNumber+1 AS ValueNotUsedInSequenceNumber
    -> from FindValue AS tbl1
    -> left join FindValue AS tbl2 ON tbl1.SequenceNumber+1 = tbl2.SequenceNumber
    -> WHERE tbl2.SequenceNumber IS NULL
    -> ORDER BY tbl1.SequenceNumber LIMIT 1;

実行結果

クエリを実行すると、以下のように未使用の値「112」が返されます。

+------------------------------+
| ValueNotUsedInSequenceNumber |
+------------------------------+
|                          112 |
+------------------------------+
1 row in set (0.00 sec)

クエリの仕組み解説

このSQLの動作原理を簡単に整理すると、以下のようになります。

① 自己結合： 同じテーブルを「tbl1」と「tbl2」という2つの別名で扱い、tbl1の次の値（＋1）とtbl2の値を結合条件にします。
② 欠番の検出： LEFT JOINのため、結合相手が存在しない場合はtbl2側がNULLになります。WHERE句でNULLを絞り込むことで、次の値が存在しない＝欠けている番号を特定できます。
③ 最小値の取得： ORDER BYで昇順ソートし、LIMIT 1で先頭の1件だけを返すことで、最も小さい未使用値を取得します。

なお、この手法はインデックスが適切に設定されたカラムに対しては高速に動作しますが、レコード数が非常に多いテーブルでは自己結合のコストを考慮してください。また、連番の開始値より前の欠番（例：108以前）を検出したい場合は、別途シーケンスの基準値との比較処理が必要になる点にも注意しましょう。