MySQL8の再帰メソッドの使い方を教える

以前、カスタム関数を使用して MySQL でツリー構造を再帰的にクエリする方法についての記事を書きました。MySQL 8.0 以降、再帰クエリ構文がようやくサポートされるようになりました。

熱膨張係数

まず、CTEとは何かを理解しましょう。正式名称はCommon Table Expressionsです。

と
 cte1 AS (テーブル1からa、bを選択)
 cte2 AS (テーブル2からc、dを選択)
cte1からb、dを選択し、cte2に結合します。
ここで cte1.a = cte2.c;

cte1、cte2は定義したCTEであり、現在のクエリで参照できます。

CTE は派生テーブルに似た一時的な結果セットであることがわかります。両者の違いについてはここでは詳しく説明しません。MySQL 開発ドキュメントを参照してください: https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-examples

再帰クエリ

まず、再帰クエリの構文を見てみましょう。

再帰的なcte_name AS
（
  SELECT ... -- 初期行セットを返す
  UNION ALL / UNION DISTINCT
  SELECT ... -- 追加の行セットを返す
）
cteから*を選択します。

最終的な結果セットが、必要な「再帰ツリー構造」である CTE を定義します。RECURSIVE は、現在の CTE が再帰的であることを意味します。
最初のSELECTは「初期結果セット」です
2番目のSELECTは再帰部分であり、「最初の結果セット/最後の再帰によって返された結果セット」を使用して「新しい結果セット」を照会して取得します。
再帰結果セットが null を返すと、クエリは終了します。
最後に、UNION ALL は上記の手順ですべての結果セットを結合し (UNION DISTINCT は重複を削除します)、SELECT * FROM cte; を通じてすべての結果セットを取得します。

再帰部分には以下を含めることはできません。

SUM() などの集計関数
グループ化
注文する
制限
明確な

上記の説明は少し抽象的かもしれませんので、例を通してゆっくり理解していきましょう。

WITH RECURSIVE cte (n) AS -- ここで定義されたnは結果セットの列名と同等であり、次のクエリでも定義できます（
 選択1
 ユニオンオール
 n + 1 をテーブルから選択する (n < 5)
）
cteから*を選択します。


 - 結果
+------+
| いいえ |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+

初期結果セットはn = 1です
次に、再帰部分を見てみましょう。CTE が初めて実行されると、結果セットは n = 1 になります。n < 5 という条件が満たされていないことがわかったので、n + 1 が返されます。
再帰部分は2回目に実行され、CTE結果セットはn = 2になります。条件が満たされなくなるまで再帰が行われます。
最後に結果セットをマージします

例

最後に、ツリー構造の例を見てみましょう。

テーブル `c_tree` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `cname` varchar(255) COLLATE utf8mb4_unicode_ci デフォルト NULL,
 `parent_id` int(11) デフォルト NULL,
 主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 デフォルト CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

mysql> c_tree から * を選択します。
+----+---------+-----------+
| id | cname | 親 ID |
+----+---------+-----------+
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 1-1 | 1 |
| 5 | 1-2 | 1 |
| 6 | 2-1 | 2 |
| 7 | 2-2 | 2 |
| 8 | 3-1 | 3 |
| 9 | 3-1-1 | 8 |
| 10 | 3-1-2 | 8 |
| 11 | 3-1-1-1 | 9 |
| 12 | 3-2 | 3 |
+----+---------+-----------+

マイSQL> 
RECURSIVE tree_cte として
（
  parent_id = 3 の c_tree から * を選択
  ユニオンオール
  c_tree t から t.* を選択し、tree_cte tcte を t.parent_id = tcte.id で内部結合します。
）
tree_cte から * を選択します。
+----+---------+-----------+
| id | cname | 親 ID |
+----+---------+-----------+
| 8 | 3-1 | 3 |
| 12 | 3-2 | 3 |
| 9 | 3-1-1 | 8 |
| 10 | 3-1-2 | 8 |
| 11 | 3-1-1-1 | 9 |
+----+---------+-----------+