免責事項
- この翻訳は MySQL Server Blog の 記事 をユーザーが翻訳したものであり、Oracle公式の翻訳ではありません。
MySQL開発チームはこのたび Lab版 のMySQLサーバーをリリースしました("MySQL Server 8.0.0 Optimizer" として公開されています) 私が開発したこのリリースの特徴的な機能は (再帰)共通テーブル式…(再帰)CTE, (再帰)サブクエリー処理, WITH [RECURSIVE] 句としても知られています…です。
3年前、私はCTEをエミュレートする方法を ブログ で紹介しました。しかし、MySQLは今や本物のCTEを備えました。偽物ではなく！
これはこの新機能の全ての詳細を紹介するブログポストの最初の一つです。
派生テーブルはFROM句のサブクエリーです。下記の太字の部分がそれにあたります。
SELECT … FROM (SELECT …) AS derived_table ;
派生テーブルは古くからMySQLにありました。これは実際には非再帰CTEが「派生テーブルとして実装されている」ということができます。ここに最初の例があります。
WITH cte1(txt) AS (SELECT "This "),
cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
cte3(txt) AS (SELECT "nice query" UNION
SELECT "query that rocks" UNION
SELECT "query"),
cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;
+----------------------------+----------------------+
| MAX(txt) | MIN(txt) |
+----------------------------+----------------------+
| This is a query that rocks | This is a nice query |
+----------------------------+----------------------+
1 row in set (0,00 sec)
ここでは cte4 という、他のCTE cte3 と cte1 から派生した cte2 から派生したCTEを使っています。この記事の読者(Vasiliy)は同じことは派生テーブルを使っても表現できる、とコメントしました（訳注: この記事が最初に公開された時点では「派生テーブルではこれはできない」ように書いてあり、それに対しVasiliy氏が派生テーブルでも可能だ、と コメント したようです）
SELECT MAX(txt), MIN(txt)
FROM
(
SELECT concat(cte2.txt, cte3.txt) as txt
FROM
(
SELECT CONCAT(cte1.txt,'is a ') as txt
FROM
(
SELECT 'This ' as txt
) as cte1
) as cte2,
(
SELECT 'nice query' as txt
UNION
SELECT 'query that rocks'
UNION
SELECT 'query'
) as cte3
) as cte4;
しかしながら両方のクエリーを読み比べると、CTEを使ったクエリーはテーブル定義が "線形に" 記述されており上から下へと読むことができます。一方、派生テーブルを使ったクエリーは "ツリーのように" "ひっくり返って" (最初の単語である "This" が深い入れ子の中にあります) 見えます。CTEは読みやすいクエリーの記述を助け、将来的なメンテナンスの役に立つでしょう。
ブログの記事のサイズ制限のために、より詳しい非再帰CTEについては次の記事で補足する予定です。上記の例があなたの興味を惹くことをことを期待しています。今回は再帰CTE…個人的な意見ですが、こちらの方がより伝統的なSQLと比べて革新的でしょう…についても見ていきます。
再帰CTEは最初の行のセットから反復的に…処理は新しい行を派生させ、行セットを大きくし、肥大化した行セットを再度処理して、更に大きな行セットを作成し、それ以上新しい行が作成されなくなるまで処理をした…構成された行のセットです。
これを含む最も単純な構文は以下のようになるでしょう。
WITH RECURSIVE cte_name AS
(
SELECT ... <-- 最初の行セットを指定する
UNION ALL
SELECT ... <-- 次の結果セットを作成する処理を指定する
)
SELECT, INSERT, UPDATE, DELETEステートメント、またはSELECTのサブクエリーの中にこれを使うことができます。
それでは最初に1から10の整数を生成する例を見てみましょう。
WITH RECURSIVE my_cte AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0,00 sec)
この構文を比較する時には、
- CTEの名前は
my_cteです。 - CTEの定義は
SELECT * FROM my_cteというステートメントの前に置かれています。 - 最初の行セットは
SELECT 1 AS nであり、これは "1" で構成される行です。この行のメタデータはmy_cteのカラム定義として利用されます。my_cteは1つのINT型（"1" のデータ型）のAS nから派生したnという名前のカラムを持ちます。 - 処理は行セットをインプットとして受け取り、新しい行セットをアウトプットとして生成します。
SELECT 1+n FROM my_cte WHERE n<10で示されるように、my_cteからn<10にマッチする行を取り出し、取り出した行の全てに対してnがインクリメントされた行セットを生成します。 my_cteの定義は明確に再帰的で、my_cte(2つ目のSELECTのFROM句) への参照を含みます。このようにして再帰CTEと非再帰CTEを区別することができます。
この時、MySQLは以下のステップで処理をするでしょう。
- 0回目の反復: 最初の行セットを作成します S0: S0={1} です。
- 1回目の反復: 0回目の反復の結果(S0)を処理し、新しい行セット S1={1+1}={2} を得ます。
- 2回目の反復: 1回目の反復の結果(S1)を処理し、新しい行セット S2={1+2}={3} を得ます。
- このように処理が続きます。
- 9回目の反復: 8回目の反復の結果(S8)を処理し、新しい行セットS9={1+9}={10} を得ます。
- 10回目の反復: 9回目の反復の結果(S9)を処理します。S9には
n<10にマッチする行がないため、新しい行セットは生成されません。これが反復終了のトリガーになります。 - 外側のSELECTで使われている
my_cteの最終的な結果として、S0, S1, .., S9の和が返されます。{1, 2, .., 9, 10}です。
最初の行セットは "非再帰SELECT", "根拠になるSELECT", "シードのSELECT" として何度も参照されます。行を生成する処理は "再帰SELECT" として記述されます。これは自分自身で my_cte を読み取るため "再帰的" です。
より完全な構文はこのようになります。
WITH RECURSIVE cte_name [list of column names ] AS
(
SELECT ... <-- 最初の行セットを指定する
UNION ALL
SELECT ... <-- 最初の行セットを指定する
UNION ALL
...
SELECT ... <-- 次の結果セットを作成する処理を指定する
UNION ALL
SELECT ... <-- 次の結果セットを作成する処理を指定する
...
)
[, any number of other CTE definitions ]
よって、
- 最初の行セットとして、いくつかのSELECTの結果をUNIONしたものが定義できます。
- 新しい行を生成する処理を上記のUNIONされた行セットに対して定義できます。
- このCTEの定義は最初のCTEとして使用される他のCTEの定義に追従することができます。
- 一つの WITH 句の中で非再帰CTEと再帰CTE（WITH句の中に少なくとも一つの再帰CTEがある場合、最初の宣言は WITH RECURSIVE で始めなければいけません）を混ぜることができます。
- CTEのカラム名は（最初のクエリーに AS を使ってエイリアスをつける代わりに）CTEの名前の右に置くことができます。 cte_name(n) のように（訳注:
nがカラムの名前）
それでは画面のスペースを節約するために、1から10の例の代わりに1から6に例を減らして見ていきましょう。CTEの名前とカラム名は my_cte(n) の構文を使い、 my_cte の結果をテーブル作成に使います（訳注: CTE以外は以前からある CREATE TABLE .. SELECT .. ステートメントの構文です）
USE test;
CREATE TABLE numbers
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;
Query OK, 6 rows affected (0,40 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0,00 sec
CTEはINSERT（およびREPLACE）ステートメントにも使うことができます。
（訳注: 先の CREATE TABLE .. SELECT .. で作成したテーブルに追加で INSERT しています）
INSERT INTO numbers
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;
Query OK, 6 rows affected (0,12 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
12 rows in set (0,00 sec)
UPDATE(1テーブルおよび複数テーブル)ステートメントではこのようになります。
（訳注: n が平方数の場合、 n を0にアップデートするステートメントです）
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
UPDATE numbers, my_cte
# 値を0に変更
SET numbers.n=0
# 平方数(1と4)を `my_cte.n` \* `my_cte.n` で表現している
WHERE numbers.n=my_cte.n*my_cte.n;
Query OK, 4 rows affected (0,01 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 0 |
| 2 |
| 3 |
| 0 |
| 5 |
| 6 |
| 0 |
| 2 |
| 3 |
| 0 |
| 5 |
| 6 |
+------+
12 rows in set (0,00 sec)
そしてDELETE(1テーブルおよび複数テーブル)ステートメントではこのようになります。CTEがどのようにサブクエリーから参照されているかに注意してください。
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
DELETE FROM numbers
# 1から6の平均(=3.5)より大きい値を削除します
WHERE numbers.n > (SELECT AVG(n) FROM my_cte);
Query OK, 4 rows affected (0,01 sec)
SELECT * FROM numbers;
+------+
| n |
+------+
| 0 |
| 2 |
| 3 |
| 0 |
| 0 |
| 2 |
| 3 |
| 0 |
+------+
8 rows in set (0,00 sec)
同じくDELETEステートメントですが、CTEがサブクエリーの中で定義されており、DELETEの表面には出てきません。
DELETE FROM numbers
WHERE numbers.n >
(
WITH RECURSIVE my_cte(n) AS
(
SELECT 1
UNION ALL
SELECT 1+n FROM my_cte WHERE n<6
)
### 訳注: このCTEで1から6の行セットを作成し、以下のSELECTステートメントで1から6の平均(=3.5)を2で割っている
# 平均の半分、 3.5/2=1.75
SELECT AVG(n)/2 FROM my_cte
);
### 訳注: 最終的にWHERE numbers.n > 1.75 ということ
Query OK, 4 rows affected (0,07 sec)
+------+
| n |
+------+
| 0 |
| 0 |
| 0 |
| 0 |
+------+
4 rows in set (0,00 sec)
ちょっと柔軟だと思いませんか？
（訳注: このクエリーにおける）注意として、もし WHERE n<6 をタイプし忘れたらどうなるでしょうか？ クエリーは永遠に走り続け、いつまでも新しい行セットを作り続けます（6で停止する理由がどこにもないからです） もし mysql コマンドラインクライアントを使っているならCtrl + Cで停止することができますが、他のクライアントを使っている場合は新しいセッションを開いて KILL ステートメントで停止しなければならないでしょう。しかし、WITH RECURSIVE を試す時にはセッションを開始したタイミングで以下のステートメントを入力することで簡単にできます。
SET max_execution_time = 10000;
これで、もしWHERE句が正しくなくてどこかに行ってしまったクエリーも自動的に10秒後に中断されます。
今日のところはここまでです。次の記事でも引き続きこの話題についての説明をしますが、既にあなたがこの新機能を試すのに十分なものはここにあります。あなたがこれを試す前に、時間を節約するための2つのことを覚えておいてください。
- もし再帰CTEをとても長い文字列を生成するのに使う場合（たとえば、再帰SELECTの中でCONCAT()を使う場合） CTEのカラムのデータ型は非再帰SELECTのデータ型でだけ判断されることを覚えておいてください。非再帰SELECTの中で CAST (… AS CHAR()) を使って十分なカラム長を確保してください。そうでなければ、長い文字列は長さの制限を超えてしまうでしょう。
- 再帰CTEの定義の中（ AS (...) の部分）では、いくつか構文に制限があります（その理由については次の記事で説明しようと思っています）
- 全てのSELECTはUNION ALLで繋がなければいけません。
- 再帰SELECTにGROUP BYを使った集計関数（SUM()のような）、ORDER BY、LIMIT、DISTINCTを使用してはいけません（このルールは非再帰SELECT/根拠になるSELECT/シードSELECTには適用されません）
- 再帰SELECTはFROM句の中にCTEを一つだけ指定しなければならず、サブクエリーの中には利用できません。もちろん他のテーブルとCTEをJOINしてCTEにすることはできます。これは階層を組み立てるのにとても便利です（たとえば上司テーブルと従業員テーブルがあった時に、 "誰が直接または間接に Mrs.X に報告するのか？" という質問に対して答える時に） もしJOINをこのように使う場合、CTEはLEFT JOIN構文の右側にあってはいけません。
以上です。いつも
select unhex("5468616E6B20796F7520666F722063686F6F73696E67204D7953514C21") as final_words;
+-------------------------------+
| final_words |
+-------------------------------+
| Thank you for choosing MySQL! |
+-------------------------------+