MySQL
MySQL8.0

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

More than 1 year has passed since last update.

先にウィンドウ関数(Window Functions)を取り上げたのでMySQL 8.0でのサポート順とは逆になりますが(CTEは8.0.1)、いまさらながらCTE(Common Table Expressions)を試してみました。

0. CTEとは

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

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

1. 非再帰CTE(テストは省略)

前述のYakst翻訳記事(オリジナルはMySQL Server Blogの記事)以外に(日本語で)あまり触れられていないような気がしますが、理由はやはり「派生テーブルでほぼ同じ内容が書けるから目新しさがない」からでしょうね。
というわけで…ここでも省略します(笑)。

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

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

2. 再帰CTE(SELECT)

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

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

注文詳細(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)

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

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

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

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

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

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の結果は以下の通りです。

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」にしてみます。

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の結果を見てみます。

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で使うこともできます。

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のみ)でした。

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関連投稿記事へのリンクを集めました。