MySQL8.0 の共通テーブル式（CTE）を使ってみよう

2017年7月27日2017年9月22日

この記事は最終更新から7年以上経過しています。内容が古くなっている可能性があります。

Changes in MySQL 8.0.1 (2017-04-10, Development Milestone)

今回の記事では、MySQL8.0 から追加された「共通テーブル式」（以下、CTE）を手軽に試す方法をご紹介したいと思います。

※ MySQL8.0をセットアップする手順については以下の公式マニュアルを参照してください
Chapter 2 Installing and Upgrading MySQL

CTE とは？

・ CTEは、いわゆる「再帰クエリ」と呼ばれるものです
・ WITH句が使えるようになりました
　 → 他のRDMS（PostgreSQL, Oracle など）では実装済
・階層構造のクエリが非常に簡単に書けるようになりました
　 → 従来はサブクエリを使って、入れ子構造にしなければいけませんでした

上記の説明のみだと、いまいちピンとこないかもしれません…。
そこで、テストデータを使って実際にCTEを使ってみましょう。

使用するテーブル

以下のような Family テーブルを使用します。
ごくごく一般的な家族を表したデータですね。

id	last_name	first_name	parent_id
1	織田	信秀	NULL
2	織田	信長	1
3	織田	信忠	2
4	織田	信雄	2
5	徳川	秀忠	8
6	松平	信康	8
7	NULL	亀姫	8
8	徳川	家康	NULL
9	織田	秀雄	4
10	NULL	五徳姫	2

図にすると、以下のようになります。

※ 手元の環境にデータを用意する場合は、以下のクエリを実行して下さい

mysql> CREATE TABLE Family (id int primary key, last_name varchar(10), first_name varchar(10), parent_id int) DEFAULT CHARSET = utf8;
mysql> INSERT INTO Family VALUES (1, "織田", "信秀", NULL), (2, "織田", "信長", 1), (3, "織田", "信忠", 2), (4, "織田", "信雄", 2), (5, "徳川", "秀忠", 8) , (6, "松平", "信康", 8), (7, NULL, "亀姫", 8), (8, "徳川", "家康", NULL), (9, "織田", "秀雄", 4), (10, NULL, "五徳姫", 2);

mysql> CREATE TABLE Family (id int primary key, last_name varchar(10), first_name varchar(10), parent_id int) DEFAULT CHARSET = utf8;

mysql> INSERT INTO Family VALUES (1, "織田", "信秀", NULL), (2, "織田", "信長", 1), (3, "織田", "信忠", 2), (4, "織田", "信雄", 2), (5, "徳川", "秀忠", 8) , (6, "松平", "信康", 8), (7, NULL, "亀姫", 8), (8, "徳川", "家康", NULL), (9, "織田", "秀雄", 4), (10, NULL, "五徳姫", 2);

Q. 織田家・徳川家のみを抽出して下さい

さて、とあるお客様から、以下のような要望が届きました。

「Family テーブルから「織田家」「徳川家」の人物の “id” と “名前” を抽出して下さい」

さあ、あなたならどのようなSQLを書きますか？

嗚呼、悩ましき戦国時代の風習

真っ先に思い浮かぶのは以下のようなクエリかと思います。

mysql> SELECT id, last_name, first_name FROM Family WHERE last_name = "織田";
mysql> SELECT id, last_name, first_name FROM Family WHERE last_name = "徳川";

1 2	mysql> SELECT id, last_name, first_name FROM Family WHERE last_name = "織田"; mysql> SELECT id, last_name, first_name FROM Family WHERE last_name = "徳川";

しかし、もう一度テーブルをよく見てみると、以下のような特徴に気付くと思います。

・「女性」には “姓”（last_name）が存在しない … ex. 亀姫、五徳姫
・同じ家系でも、”姓” が異なる人物が存在する … ex. 家康と信康

※ どうして上記のような家庭事情になっているかは、是非調べてみてください！

A. CTEを使いましょう！

戦国時代の風習に直面し、頭を抱えている貴方に知ってほしいのが「CTE」です。
CTEを使えば、parent_id を利用した階層構造で簡単にレコードを取得することができます。

CTEを使用する場合は、最上位の親レコードを明示的に指定します。
今回のテーブルを見れば、徳川家の中で最も古い人物が「徳川家康」であることはすぐに分かるかと思います。

そのため、「徳川家」のみを抽出するSQLは以下のようになります。

mysql> WITH RECURSIVE Tokugawa AS 
 (
  SELECT id, last_name, first_name FROM Family
    WHERE last_name= "徳川"  AND first_name="家康"
UNION ALL
  SELECT child.id, child.last_name, child.first_name 
  FROM Family as child, Tokugawa 
    WHERE Tokugawa.id= child.parent_id
 )
 SELECT * FROM Tokugawa;

mysql> WITH RECURSIVE Tokugawa AS

(

SELECT id, last_name, first_name FROM Family

WHERE last_name= "徳川" AND first_name="家康"

UNION ALL

SELECT child.id, child.last_name, child.first_name

FROM Family as child, Tokugawa

WHERE Tokugawa.id= child.parent_id

)

SELECT * FROM Tokugawa;

一方、信長の父親である「織田信秀」のことを知っている人は余り多くないかもしれません。
しかし、「最上位の親レコード」＝「parent_id を持たない」と解釈すれば以下のようなSQLでも問題ありません。

mysql> WITH RECURSIVE Oda AS 
(
  SELECT id, last_name, first_name FROM Family 
    WHERE last_name= "織田" AND parent_id IS NULL
UNION ALL
  SELECT child.id, child.last_name, child.first_name 
  FROM Family as child, Oda 
    WHERE Oda.id= child.parent_id
)
SELECT * FROM Oda;

mysql> WITH RECURSIVE Oda AS

(

SELECT id, last_name, first_name FROM Family

WHERE last_name= "織田" AND parent_id IS NULL

UNION ALL

SELECT child.id, child.last_name, child.first_name

FROM Family as child, Oda

WHERE Oda.id= child.parent_id

)

SELECT * FROM Oda;

このように、CTEを使えば歴史に詳しくない人でも、簡単に戦国大名の家系図を取得ができるのです！

それ以外の方法は？

ただし、CTEは MySQL8.0 で実装予定なので、現行のMySQL5.7以前では使用できません。その場合、上記の課題はどう解決すればいいのでしょうか？

主な回答としては以下の3つがあります。

1. レコードを1件ずつ取得して、アプリ側でマージする

SELECT id, last_name, first_name FROM Family
   WHERE last_name= "徳川“ AND parent_id IS NULL;

1 2	SELECT id, last_name, first_name FROM Family WHERE last_name= "徳川“ AND parent_id IS NULL;

まずは、最上位の親レコードをSELECTします。この時、親の “id” が分かります。

SELECT id, last_name, first_name FROM Family
   WHERE parent_id = 8;

1 2	SELECT id, last_name, first_name FROM Family WHERE parent_id = 8;

次に親レコードの “id” を parent_idカラムに持つ子レコードを抽出します。これによって第1世代までの子が全て取得できます。

全ての子レコードが抽出できたら、その結果をアプリ側でマージします。これで、「徳川家」の全レコードが抽出できます。

2. 外部結合（LEFT JOIN）を利用して、id と parent_id を紐づける

idカラムと parent_idカラムを LEFT JOIN することで、親・子のレコードを一度に取得します。

SELECT * FROM Family tbl_1
LEFT JOIN Family tbl_2
  ON tbl_2.parent_id = tbl_1.id
WHERE tbl_1.last_name= "織田" AND tbl_1.parent_id IS NULL;

SELECT * FROM Family tbl_1

LEFT JOIN Family tbl_2

ON tbl_2.parent_id = tbl_1.id

WHERE tbl_1.last_name= "織田" AND tbl_1.parent_id IS NULL;

3. 経路を記録したカラム（pathカラム）を利用する

親レコードから、自分のレコードに至るまでの経路（各レコードのID）を列挙したpathカラムがあれば、SELECT文で今回の要件を満たすことが出来ます。

今回のFamilyテーブルであれば、pathカラムは以下のようになります。

+----+-----------+------------+-----------+---------+
| id | last_name | first_name | parent_id | path    |
+----+-----------+------------+-----------+---------+
|  1 | 織田      | 信秀       |      NULL | 1/      |
|  2 | 織田      | 信長       |         1 | 1/2     |
|  3 | 織田      | 信忠       |         2 | 1/2/3   |
|  4 | 織田      | 信雄       |         2 | 1/2/4   |
|  5 | 徳川      | 秀忠       |         8 | 8/5     |
|  6 | 松平      | 信康       |         8 | 8/6     |
|  7 | NULL      | 亀姫       |         8 | 8/7     |
|  8 | 徳川      | 家康       |      NULL | 8/      |
|  9 | 織田      | 秀雄       |         4 | 1/2/4/9 |
| 10 | NULL      | 五徳姫     |         2 | 1/2/10  |
+----+-----------+------------+-----------+---------+

+----+-----------+------------+-----------+---------+

+----+-----------+------------+-----------+---------+

| 1 | 織田 | 信秀 | NULL | 1/ |

| 2 | 織田 | 信長 | 1 | 1/2 |

| 3 | 織田 | 信忠 | 2 | 1/2/3 |

| 4 | 織田 | 信雄 | 2 | 1/2/4 |

| 5 | 徳川 | 秀忠 | 8 | 8/5 |

| 6 | 松平 | 信康 | 8 | 8/6 |

| 7 | NULL | 亀姫 | 8 | 8/7 |

| 8 | 徳川 | 家康 | NULL | 8/ |

| 9 | 織田 | 秀雄 | 4 | 1/2/4/9 |

| 10 | NULL | 五徳姫 | 2 | 1/2/10 |

+----+-----------+------------+-----------+---------+

最上位の親レコードのID（織田家の場合は “1”）が分かれば、pathカラムに対する前方一致のLIKE検索を行うことで、「織田家」のレコードが全て抽出できます。

mysql> SELECT id, last_name, first_name FROM Family where path LIKE '1/%';

1	mysql> SELECT id, last_name, first_name FROM Family where path LIKE '1/%';

ただし、上記3つの方法には以下の欠点が存在します。

世代が深くなれば深くなるほど実行するSELECT文が多くなり、マージも大変
　（子、孫、ひ孫…）
第2世代（孫レコード）の抽出ができない
テーブル定義の変更、および経路情報のINSERTが必要となる

そのため、MySQL5.7 以前ではそもそも「階層構造のテーブル」を使用しない方が良いかもしれません。

関連サービス
>>MySQL保守サポート

よかったらシェアしてね！

URLをコピーしました！

MySQL8.0 の共通テーブル式（CTE）を使ってみよう

目次

CTE とは？

使用するテーブル

Q. 織田家・徳川家のみを抽出して下さい

嗚呼、悩ましき戦国時代の風習

A. CTEを使いましょう！

それ以外の方法は？

1. レコードを1件ずつ取得して、アプリ側でマージする

2. 外部結合（LEFT JOIN）を利用して、id と parent_id を紐づける

3. 経路を記録したカラム（pathカラム）を利用する

スマートスタイルTECHブログについて

MySQL8.0 の共通テーブル式（CTE）を使ってみよう

目次

CTE とは？

使用するテーブル

Q. 織田家・徳川家のみを抽出して下さい

嗚呼、悩ましき戦国時代の風習

A. CTEを使いましょう！

それ以外の方法は？

1. レコードを1件ずつ取得して、アプリ側でマージする

2. 外部結合（LEFT JOIN）を利用して、id と parent_id を紐づける

3. 経路を記録したカラム（pathカラム）を利用する

スマートスタイルTECHブログについて

関連記事