MySQL の再帰 CTE

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

通常の CTE はテーブルを読みます。再帰 CTE は自分自身を読むため、各ラウンドの行が次のラウンドの行を生み出します。これが、SQL が階層をたどったり連番を生成したりする方法であり、どちらも単純な SELECT にはできません。始める前に、mysql -u root -p sakila でクライアントを開いてください。

再帰 CTE の仕組み

WITH RECURSIVE CTE_NAME AS (
  SELECT SEED_VALUE AS COLUMN_NAME
  UNION ALL
  SELECT NEXT_EXPRESSION FROM CTE_NAME WHERE STOP_CONDITION
)
SELECT COLUMN_NAME FROM CTE_NAME;

CTE_NAME を結果の名前に、COLUMN_NAME を CTE が生成する列に、SEED_VALUE を開始値に、NEXT_EXPRESSION を前の行から次の行を作る式に、STOP_CONDITION をいずれ偽になる条件に置き換えます。再帰 CTE には、UNION ALL でつながれた 2 種類の部分があります。

シード。アンカーメンバーとも呼ばれ、CTE をまったく参照せず、開始行を生成します。
再帰メンバー。CTE 自身から選択します。

ほとんどの再帰 CTE はそれぞれをちょうど 1 つずつ持ち、下の例もその形です。 MySQL はどちらも 2 つ以上を受け入れ、UNION ALL の代わりに UNION も受け入れます。UNION は各ラウンドの重複を取り除きます。どちらも一般的ではありませんが、出会ったときにそれと分かるようにしておく価値はあります。サーバーはシードを実行し、次に前のラウンドが生成したものに対して再帰部分を実行します。これを、あるラウンドが行を 1 つも生成しなくなるまで繰り返します。再帰部分の WHERE が、いずれそれを起こすものです。キーワードは WITH RECURSIVE で、CTE ではなく WITH に付けます。

5 まで数える

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
...
+------+
5 rows in set (0.00 sec)

シードが行 1 を作ります。再帰部分はその行を受け取って 2 を作り、次に 2 を受け取って 3 を作り、5 に達したところで止まります。WHERE n < 5 が次の行を生成しないためです。このような連番は見た目以上に役に立ちます。実データと結合すると、何も起きなかった日も含めて範囲内のすべての日に 1 行ずつが得られます。これはデータだけに対するクエリでは生成できません。

停止条件を省く

WHERE を取り除くと、再帰部分は常に次の行を生成します。

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers
)
SELECT n FROM numbers;

ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

サーバーは永遠に実行し続けるのではなく、これを止めます。制限は設定です。

SELECT @@cte_max_recursion_depth AS max_depth;

+-----------+
| max_depth |
+-----------+
|      1000 |
+-----------+
1 row in set (0.01 sec)

メッセージ内の回数は設定より 1 大きくなります。制限を超えたラウンドが、サーバーが止まる前に数えられるためです。 ERROR 3636 は、まず停止条件の欠落または誤りとして読んでください。 SET SESSION cte_max_recursion_depth = 5000; で制限を上げるのが正しいのは、デフォルトが許すより多くのラウンドが本当に必要なときだけです。クエリがそもそも止まらないものだった場合、それは誤りです。失敗するまでの時間が長くなるだけで、結局は失敗します。この設定は変数なので、上の 1,000 は MySQL の固定の性質ではなく、このサーバーの値です。

まとめ

CTE が自分自身を読むときは WITH RECURSIVE と書きます。
シード部分と再帰メンバーを与え、UNION ALL でつなぎます。
停止条件は再帰部分の WHERE に置きます。
ERROR 3636 は、設定に手を伸ばす前に、暴走した再帰として読みます。

MySQL Tutorial

ガイド

データの保存

クエリの作成

データの保護

パフォーマンスの最適化

AI で構築する

MySQL の運用

アプリケーションの接続

拡張機能の作成

VillageSQL is a drop-in replacement for MySQL with extensions.

再帰 CTE の仕組み

5 まで数える

停止条件を省く

まとめ

関連項目