いまさらMySQL 8.0で共通テーブル式（CTE : Common Table Expressions）

先に[ウィンドウ関数（Window Functions）を取り上げた](https://qiita.com/hmatsu47/items/6cc0e69f3895f3e4a486)のでMySQL 8.0でのサポート順とは逆になりますが（CTEは8.0.1）、いまさらながらCTE（Common Table Expressions）を試してみました。

# 0. CTEとは

メインのSQLの問い合わせを実行するために補助的に使う、一時テーブルを（WITH句を使って）定義するものです。
…というと、派生テーブル（FROM句のサブクエリ）と何が違うの？となりますが、（非再帰のCTEはWITH句を使うか使わないか等の書式の違いを除いて、派生テーブルとほぼ同じようなものですので）実質的な違いは「再帰的に書ける」ところです。

MySQL 8.0でのCTEの使い方については、以下の記事・資料が参考になります。

 * **[13.2.11.9 WITH Syntax (Common Table Expressions)](https://dev.mysql.com/doc/refman/8.0/en/with.html)（MySQL 8.0 Reference Manual）**
 * **[MySQL 8.0 Lab版: MySQLの (再帰)共通テーブル式(CTE)](https://yakst.com/ja/posts/4322)（Yakst翻訳記事）**
 * **[MySQL8.0 の共通テーブル式（CTE）を使ってみよう](https://www.s-style.co.jp/blog/2017/07/884/)（SmartStyle TECH BLOG）**

# 1. 非再帰CTE（テストは省略）

前述の[Yakst翻訳記事](https://yakst.com/ja/posts/4322)（オリジナルは[MySQL Server Blogの記事](http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/)）以外に（日本語で）あまり触れられていないような気がしますが、理由はやはり「派生テーブルでほぼ同じ内容が書けるから目新しさがない」からでしょうね。
というわけで…ここでも省略します（笑）。

もっとも、WITH句で切り出してSQLの前に持ってきたり、複数のCTEを順序立てて記述したりすることができたり（詳細は[前述の記事](https://yakst.com/ja/posts/4322)を参照）、派生テーブルで書くよりもSQLが読みやすくなる利点があります。

※5.5以前のMySQLのようにパフォーマンスを気にしてすべて結合（JOIN）で書いたりするのと比べれば雲泥の差ですね。

# 2. 再帰CTE（SELECT）

WITH RECURSIVE句を使って、起点となるレコードに対するSELECT文と、再帰的に実行していくSELECT文をUNION ALL句でつないで表現します。

例えば、以下のような構造のテーブルがあり、データが入っているとします（説明を簡単にするため、親テーブルは省略。製品（商品）名（product_name）は本来テーブルをマスタ化して切り出すべきカラムですが、わかりやすさのためにあえて製品（商品）名をそのまま入れてあります）。

```mysql:注文詳細（order_detail）テーブル
mysql> CREATE TABLE ctetest.order_detail (order_detail_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, order_id INT UNSIGNED NOT NULL, parent_order_detail_id INT UNSIGNED, product_name VARCHAR(100), cancel_flag INT UNSIGNED NOT NULL, INDEX (order_id), INDEX (parent_order_detail_id));
Query OK, 0 rows affected (0.03 sec)

（データ挿入は省略）

mysql> SELECT * FROM ctetest.order_detail;
+-----------------+----------+------------------------+-----------------------------------------+-------------+
| order_detail_id | order_id | parent_order_detail_id | product_name                            | cancel_flag |
+-----------------+----------+------------------------+-----------------------------------------+-------------+
|               1 |        1 |                   NULL | 車両本体 Sグレード                      |           0 |
|               2 |        1 |                      1 | セーフティーパッケージ                  |           0 |
|               3 |        1 |                      2 | 衝突回避ブレーキシステム                |           0 |
|               4 |        1 |                      2 | 追加エアバッグセット                    |           0 |
|               5 |        1 |                      3 | サイドエアバッグ                        |           0 |
|               6 |        1 |                      3 | カーテンエアバッグ                      |           0 |
|               7 |        1 |                      1 | 18インチセット                          |           0 |
|               8 |        1 |                      7 | 225/40R18ラジアルタイヤ                 |           0 |
|               9 |        1 |                      7 | 18インチアルミホイール                  |           0 |
|              10 |        1 |                      1 | フロアマット                            |           0 |
|              11 |        2 |                   NULL | 車両本体 Bグレード                      |           0 |
|              12 |        2 |                     11 | サイドバイザー                          |           0 |
|              13 |        2 |                     11 | フロアマット                            |           0 |
|              14 |        3 |                   NULL | 車両本体 Xグレード                      |           0 |
|              15 |        3 |                     14 | スタイリッシュパッケージ                |           0 |
|              16 |        3 |                     15 | 18インチセット                          |           0 |
|              17 |        3 |                     16 | 225/40R18ラジアルタイヤ                 |           0 |
|              18 |        3 |                     16 | 18インチアルミホイール                  |           0 |
|              19 |        3 |                     15 | エアロセットB                           |           0 |
|              20 |        3 |                     19 | フロントアンダースポイラー              |           0 |
|              21 |        3 |                     19 | サイドステップ                          |           0 |
|              22 |        3 |                     19 | リアスポイラー                          |           0 |
+-----------------+----------+------------------------+-----------------------------------------+-------------+
22 rows in set (0.00 sec)
```

構造的にはこんな感じです。

```text:注文書詳細
order#1-+-車両本体 Sグレード
        |
        +-+-セーフティーパッケージ
        | |
        | +---衝突回避ブレーキシステム
        | |
        | +-+-追加エアバッグセット
        |   |
        |   +---サイドエアバッグ
        |   |
        |   +---カーテンエアバッグ
        |
        +-+-18インチセット
        | |
        | +---225/40R18ラジアルタイヤ
        | |
        | +---18インチアルミホイール
        |
        +---フロアマット

order#2-+-車両本体 Bグレード
        |
        +---サイドバイザー
        |
        +---フロアマット

order#3-+-車両本体 Xグレード
        |
        +-+-スタイリッシュパッケージ
          |
          +-+-18インチセット
          | |
          | +---225/40R18ラジアルタイヤ
          | |
          | +---18インチアルミホイール
          |
          +-+-エアロセットB
            |
            +---フロントアンダースポイラー
            |
            +---サイドステップ
            |
            +---リアスポイラー
```

ここで、order#1の構成要素のうち、セーフティーパッケージとそれを構成する子パーツ、孫パーツを取り出すには、以下のようにします。

```mysql:order#!のセーフティーパッケージ抽出
mysql> WITH RECURSIVE product_order AS
    -> (
    ->   SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag
    ->    FROM ctetest.order_detail
    ->     WHERE order_detail_id = 2
    ->   UNION ALL
    ->   SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag
    ->    FROM ctetest.order_detail AS child, product_order
    ->     WHERE product_order.order_detail_id = child.parent_order_detail_id
    -> )
    -> SELECT * FROM product_order;
+-----------------+------------------------+--------------------------------------+-------------+
| order_detail_id | parent_order_detail_id | product_name                         | cancel_flag |
+-----------------+------------------------+--------------------------------------+-------------+
|               2 |                      1 | セーフティーパッケージ               |           0 |
|               3 |                      2 | 衝突回避ブレーキシステム             |           0 |
|               4 |                      2 | 追加エアバッグセット                 |           0 |
|               5 |                      3 | サイドエアバッグ                     |           0 |
|               6 |                      3 | カーテンエアバッグ                   |           0 |
+-----------------+------------------------+--------------------------------------+-------------+
5 rows in set (0.00 sec)
```

無事、取り出せました。
EXPLAINの結果は以下の通りです。

```mysql:EXPLAIN(1)
mysql> EXPLAIN WITH RECURSIVE product_order AS (   SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag    FROM ctetest.order_detail     WHERE order_detail_id = 2   UNION ALL   SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag    FROM ctetest.order_detail AS child, product_order     WHERE product_order.order_detail_id = child.parent_order_detail_id ) SELECT * FROM product_order;
+----+-------------+---------------+------------+-------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
| id | select_type | table         | partitions | type  | possible_keys          | key                    | key_len | ref                           | rows | filtered | Extra                  |
+----+-------------+---------------+------------+-------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
|  1 | PRIMARY     | <derived2>    | NULL       | ALL   | NULL                   | NULL                   | NULL    | NULL                          |    5 |   100.00 | NULL                   |
|  2 | DERIVED     | order_detail  | NULL       | const | PRIMARY                | PRIMARY                | 4       | const                         |    1 |   100.00 | NULL                   |
|  3 | UNION       | product_order | NULL       | ALL   | NULL                   | NULL                   | NULL    | NULL                          |    2 |   100.00 | Recursive; Using where |
|  3 | UNION       | child         | NULL       | ref   | parent_order_detail_id | parent_order_detail_id | 5       | product_order.order_detail_id |    2 |   100.00 | NULL                   |
+----+-------------+---------------+------------+-------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
4 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: with recursive `product_order` as (/* select#2 */ select '2' AS `order_detail_id`,'1' AS `parent_order_detail_id`,'セーフティーパッケージ' AS `product_name`,'0' AS `cancel_flag` from `ctetest`.`order_detail` where 1 union all /* select#3 */ select `ctetest`.`child`.`order_detail_id` AS `order_detail_id`,`ctetest`.`child`.`parent_order_detail_id` AS `parent_order_detail_id`,`ctetest`.`child`.`product_name` AS `product_name`,`ctetest`.`child`.`cancel_flag` AS `cancel_flag` from `ctetest`.`order_detail` `child` join `product_order` where (`ctetest`.`child`.`parent_order_detail_id` = `product_order`.`order_detail_id`)) /* select#1 */ select `product_order`.`order_detail_id` AS `order_detail_id`,`product_order`.`parent_order_detail_id` AS `parent_order_detail_id`,`product_order`.`product_name` AS `product_name`,`product_order`.`cancel_flag` AS `cancel_flag` from `product_order`
1 row in set (0.00 sec)
```

Extra列に「Recursive」が出ています。

# 3. 再帰CTE（UPDATE）

再帰CTEは更新系の処理でも使うことができます。
INSERTの場合はINSERT INTOする行を取得・生成することができますが、ここではUPDATEを試してみます。

先ほどのテーブルで、order#1のセーフティーパッケージが注文キャンセルされたものとして、cancel_flagを「1」にしてみます。

```mysql:order#!のセーフティーパッケージ取消
mysql> WITH RECURSIVE product_order AS
    -> (
    ->   SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag
    ->    FROM ctetest.order_detail
    ->     WHERE order_detail_id = 2
    ->   UNION ALL
    ->   SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag
    ->    FROM ctetest.order_detail AS child, product_order
    ->     WHERE product_order.order_detail_id = child.parent_order_detail_id
    -> )
    -> UPDATE ctetest.order_detail
    ->  SET cancel_flag = 1
    ->   WHERE order_detail_id IN
    ->    (SELECT order_detail_id FROM product_order);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM ctetest.order_detail WHERE cancel_flag = 1;
+-----------------+----------+------------------------+--------------------------------------+-------------+
| order_detail_id | order_id | parent_order_detail_id | product_name                         | cancel_flag |
+-----------------+----------+------------------------+--------------------------------------+-------------+
|               2 |        1 |                      1 | セーフティーパッケージ               |           1 |
|               3 |        1 |                      2 | 衝突回避ブレーキシステム             |           1 |
|               4 |        1 |                      2 | 追加エアバッグセット                 |           1 |
|               5 |        1 |                      3 | サイドエアバッグ                     |           1 |
|               6 |        1 |                      3 | カーテンエアバッグ                   |           1 |
+-----------------+----------+------------------------+--------------------------------------+-------------+
5 rows in set (0.00 sec)
```

正しく更新されました。
こちらもEXPLAINの結果を見てみます。

```mysql:EXPLAIN(2)
mysql> EXPLAIN WITH RECURSIVE product_order AS (   SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag    FROM ctetest.order_detail     WHERE order_detail_id = 2   UNION ALL   SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag    FROM ctetest.order_detail AS child, product_order     WHERE product_order.order_detail_id = child.parent_order_detail_id ) UPDATE ctetest.order_detail  SET cancel_flag = 1   WHERE order_detail_id IN    (SELECT order_detail_id FROM product_order);
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
| id | select_type        | table         | partitions | type           | possible_keys          | key                    | key_len | ref                           | rows | filtered | Extra                  |
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
|  1 | UPDATE             | order_detail  | NULL       | index          | NULL                   | PRIMARY                | 4       | NULL                          |   22 |   100.00 | Using where            |
|  2 | DEPENDENT SUBQUERY | <derived3>    | NULL       | index_subquery | <auto_key0>            | <auto_key0>            | 5       | func                          |    2 |   100.00 | NULL                   |
|  3 | DERIVED            | order_detail  | NULL       | const          | PRIMARY                | PRIMARY                | 4       | const                         |    1 |   100.00 | NULL                   |
|  4 | UNION              | product_order | NULL       | ALL            | NULL                   | NULL                   | NULL    | NULL                          |    2 |   100.00 | Recursive; Using where |
|  4 | UNION              | child         | NULL       | ref            | parent_order_detail_id | parent_order_detail_id | 5       | product_order.order_detail_id |    2 |   100.00 | NULL                   |
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
5 rows in set (0.00 sec)
```

SELECTでは結果をそのまま取り出しているのに対し、UPDATEではWHERE～INのサブクエリで取り出しているので、結果が変わってきています。

※MySQL 5.5以前の例に倣って等結合やJOINに書き換えると、機能上の制約に引っかかって怒られます。

# 4. 再帰CTE（DELETE）

当然、DELETEで使うこともできます。

```mysql:DELETE
mysql> WITH RECURSIVE product_order AS
    -> (
    ->   SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag
    ->    FROM ctetest.order_detail
    ->     WHERE order_detail_id = 2
    ->   UNION ALL
    ->   SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag
    ->    FROM ctetest.order_detail AS child, product_order
    ->     WHERE product_order.order_detail_id = child.parent_order_detail_id
    -> )
    -> DELETE FROM ctetest.order_detail
    ->   WHERE order_detail_id IN
    ->    (SELECT order_detail_id FROM product_order);
Query OK, 5 rows affected (0.01 sec)

mysql> SELECT * FROM ctetest.order_detail;
+-----------------+----------+------------------------+-----------------------------------------+-------------+
| order_detail_id | order_id | parent_order_detail_id | product_name                            | cancel_flag |
+-----------------+----------+------------------------+-----------------------------------------+-------------+
|               1 |        1 |                   NULL | 車両本体 Sグレード                      |           0 |
|               7 |        1 |                      1 | 18インチセット                          |           0 |
|               8 |        1 |                      7 | 225/40R18ラジアルタイヤ                 |           0 |
|               9 |        1 |                      7 | 18インチアルミホイール                  |           0 |
|              10 |        1 |                      1 | フロアマット                            |           0 |
|              11 |        2 |                   NULL | 車両本体 Bグレード                      |           0 |
|              12 |        2 |                     11 | サイドバイザー                          |           0 |
|              13 |        2 |                     11 | フロアマット                            |           0 |
|              14 |        3 |                   NULL | 車両本体 Xグレード                      |           0 |
|              15 |        3 |                     14 | スタイリッシュパッケージ                |           0 |
|              16 |        3 |                     15 | 18インチセット                          |           0 |
|              17 |        3 |                     16 | 225/40R18ラジアルタイヤ                 |           0 |
|              18 |        3 |                     16 | 18インチアルミホイール                  |           0 |
|              19 |        3 |                     15 | エアロセットB                           |           0 |
|              20 |        3 |                     19 | フロントアンダースポイラー              |           0 |
|              21 |        3 |                     19 | サイドステップ                          |           0 |
|              22 |        3 |                     19 | リアスポイラー                          |           0 |
+-----------------+----------+------------------------+-----------------------------------------+-------------+
17 rows in set (0.00 sec)
```

対象行が削除されました。

なお、EXPLAINの結果はUPDATEのときと同等（違いはid:1のUPDATE→DELETEのみ）でした。

```mysql:EXPLAIN(3)
mysql> EXPLAIN WITH RECURSIVE product_order AS (   SELECT order_detail_id, parent_order_detail_id, product_name, cancel_flag    FROM ctetest.order_detail     WHERE order_detail_id = 2   UNION ALL   SELECT child.order_detail_id, child.parent_order_detail_id, child.product_name, child.cancel_flag    FROM ctetest.order_detail AS child, product_order     WHERE product_order.order_detail_id = child.parent_order_detail_id ) DELETE FROM ctetest.order_detail   WHERE order_detail_id IN    (SELECT order_detail_id FROM product_order);
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
| id | select_type        | table         | partitions | type           | possible_keys          | key                    | key_len | ref                           | rows | filtered | Extra                  |
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
|  1 | DELETE             | order_detail  | NULL       | ALL            | NULL                   | NULL                   | NULL    | NULL                          |   22 |   100.00 | Using where            |
|  2 | DEPENDENT SUBQUERY | <derived3>    | NULL       | index_subquery | <auto_key0>            | <auto_key0>            | 5       | func                          |    2 |   100.00 | NULL                   |
|  3 | DERIVED            | order_detail  | NULL       | const          | PRIMARY                | PRIMARY                | 4       | const                         |    1 |   100.00 | NULL                   |
|  4 | UNION              | product_order | NULL       | ALL            | NULL                   | NULL                   | NULL    | NULL                          |    2 |   100.00 | Recursive; Using where |
|  4 | UNION              | child         | NULL       | ref            | parent_order_detail_id | parent_order_detail_id | 5       | product_order.order_detail_id |    2 |   100.00 | NULL                   |
+----+--------------------+---------------+------------+----------------+------------------------+------------------------+---------+-------------------------------+------+----------+------------------------+
5 rows in set (0.00 sec)
```

---

**【おまけ】**
MySQL 8.0関連投稿記事へのリンクを集めました。

 * **[Qiitaに投稿したMySQL 8.0関連記事](http://hmatsu47.hatenablog.com/qiita_mysql80)**