MySQLの再帰CTE(WITH RECURSIVE)を徹底解説:階層データの走査と数列生成を実践マスター

再帰CTE(Common Table Expression:共通テーブル式)とは、自分自身の名前を参照するサブクエリのことです。WITH RECURSIVE句を使って定義し、必ず終了条件を設ける必要があります。再帰CTEは、数列の生成、階層データの走査、グラフ探索などに活用できる強力な機能です。

基本構文

WITH RECURSIVE cte_name (col1, col2, ...) AS (
 -- 非再帰部分(基底ケース):初期行を返す
 SELECT col1, col2 FROM table_name
 UNION ALL
 -- 再帰部分:cte_name自身を参照
 SELECT col1, col2 FROM cte_name WHERE condition
)
SELECT * FROM cte_name;

• 最初のSELECTが基底ケース(アンカー)となり、処理の出発点となる初期行を提供します。
• UNION ALLによって、各反復処理で得られた行が結果に追加されます(DISTINCTを指定すると重複行が除去されます)。
• 2つ目のSELECTが再帰ケースで、WHERE条件が成立しなくなるまで繰り返し実行されます。

例1:最初の5つの奇数を生成する

WITH RECURSIVE odd_no (sr_no, n) AS (
 SELECT 1, 1
 UNION ALL
 SELECT sr_no + 1, n + 2
 FROM odd_no
 WHERE sr_no < 5
)
SELECT * FROM odd_no;

+-------+---+
| sr_no | n |
+-------+---+
|     1 | 1 |
|     2 | 3 |
|     3 | 5 |
|     4 | 7 |
|     5 | 9 |
+-------+---+

基底ケースで (1, 1) を返し、以降の各反復処理では sr_no が1ずつ、n が2ずつ増加していきます。sr_no が5に達した時点で再帰は停止し、合計5行の奇数列が得られます。

例2:社員の組織階層を走査する

より実践的な例として、上司と部下の関係からなる組織階組織階層を辿るクエリを見てみましょう。

-- 前提テーブル: employees(id, name, manager_id)
WITH RECURSIVE org_chart (id, name, level) AS (
 -- 基底ケース:最上位の管理者(manager_idがNULL)を取得
 SELECT id, name, 0
 FROM employees
 WHERE manager_id IS NULL
 UNION ALL
 -- 再帰ケース:直属の部下を順に検索
 SELECT e.id, e.name, oc.level + 1
 FROM employees e
 JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

このクエリは、最上位の管理者(レベル0)を起点として、manager_id を手がかりに各階層の部下を再帰的に検索することで、組織全体のツリー構造を構築します。部署の階層表示や権限チェックなど、さまざまな場面で応用できます。

押さえておきたいポイント

• 無限ループを防ぐため、再帰側のSELECTには必ずWHERE句で終了条件を指定しましょう。
• MySQLのデフォルトの再帰上限は1000回です。システム変数 cte_max_recursion_depth で変更可能ですが、深い階層を扱う場合は設計の見直しも検討してください。
• パフォーマンスを優先するなら UNION ALL を使用し、重複行の除去が本当に必要な場合のみ UNION DISTINCT を使いましょう。

まとめ

再帰CTEは WITH RECURSIVE を使って、基底ケースと再帰ケースからなる自己参照クエリを定義できる機能です。組織図やカテゴリツリーのような階層データの走査、数列の生成、グラフ探索において、MySQL開発では欠かせないテクニックといえます。終了条件を適切に設定すれば、これまでアプリケーション側で処理していた複雑な階層ロジックも、シンプルなSQLだけで表現できるようになります。