スマートスタイル TECH BLOG

データベース&クラウド技術情報

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

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

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

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

目次

  • CTE とは?
  • 使用するテーブル
  • Q. 織田家・徳川家のみを抽出して下さい
  • 嗚呼、悩ましき戦国時代の風習
  • A. CTEを使いましょう!
  • それ以外の方法は?

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

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

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

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

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

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

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

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

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

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

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

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

A. CTEを使いましょう!

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

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

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

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

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

それ以外の方法は?

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. 世代が深くなれば深くなるほど実行するSELECT文が多くなり、マージも大変
     (子、孫、ひ孫…)

  2. 第2世代(孫レコード)の抽出ができない

  3. テーブル定義の変更、および経路情報のINSERTが必要となる

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


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

Return Top