mysql8 共通テーブル式 CTE 使用例の分析

この記事では、例を使用して、MySQL 8 の共通テーブル式 (CTE) の使用方法を説明します。ご参考までに、詳細は以下の通りです。

共通テーブル式 CTE は、現在のステートメントを範囲とする名前付き一時結果セットです。

簡単に言えば、再利用可能なサブクエリと考えることができます。もちろん、サブクエリとは少し異なります。CTE は他の CTE を参照できますが、サブクエリは他のサブクエリを参照できません。

1. cteの構文形式:

with_clause:
 [再帰的]
  cte_name [(col_name [, col_name] ...)] AS (サブクエリ)
  [, cte_name [(col_name [, col_name] ...)] AS (サブクエリ)] ...

2. CTEを作成するためにwithステートメントをどこで使用できますか

1. 選択、更新、削除ステートメントの始まり

... を選択して...
... アップデートあり ...
... を削除 ...

2. サブクエリまたは派生テーブルサブクエリの先頭

SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...

3. SELECTの直後、SELECT文を含む文の前

挿入... 選択...
... を ... に置き換えて ... を選択
テーブルを作成... WITH ... SELECT ...
ビューを作成...... を選択...
カーソルを宣言します... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

3. テーブルを構築してデータを準備しましょう

テーブル `menu` を作成 (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) デフォルト '' コメント 'name',
 `url` varchar(255) デフォルト '' COMMENT 'url アドレス',
 `pid` int(11) デフォルト '0' コメント '親ID',
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

ポイントデータを挿入:

`menu` (`id`, `name`, `url`, `pid`) VALUES ('1', 'バックグラウンド管理', '/manage', '0') に INSERT INTO します。
`menu` (`id`, `name`, `url`, `pid`) VALUES ('2', 'ユーザー管理', '/manage/user', '1') に INSERT INTO します。
`menu` (`id`, `name`, `url`, `pid`) VALUES ('3', 'Article Management', '/manage/article', '1') に INSERT INTO します。
`menu` (`id`, `name`, `url`, `pid`) VALUES ('4', 'ユーザーの追加', '/manage/user/add', '2') に INSERT INTO します。
`menu` (`id`, `name`, `url`, `pid`) VALUES ('5', 'ユーザーリスト', '/manage/user/list', '2') に INSERT INTO します。
`menu` (`id`, `name`, `url`, `pid`) に VALUES ('6', '記事の追加', '/manage/article/add', '3') を挿入します。
`menu` (`id`, `name`, `url`, `pid`) VALUES ('7', 'Article List', '/manage/article/list', '3') に INSERT INTO します。

4. 非再帰CTE

ここでは、サブクエリを通じて各メニューに対応する直属の上位名を照会します。

m.* を選択し、(id = m.pid のメニューから名前を選択) を pname として、メニューから m として選択します。

ここではcteを使用して上記の関数を完成させます

cte は (
 メニューから*を選択
） 
m.* を選択し、(cte.id = m.pid の cte から cte.name を選択) を pname としてメニューから m として選択します。

上記の例はあまり良いものではなく、CTE の使用方法を示すために使用されているだけです。 CTE は再利用可能な結果セットであることを知っておくだけで十分です。

一部のサブクエリと比較すると、非再帰 CTE は一度だけクエリされて再利用されるため、CTE の方が効率的です。

CTE は他の CTE の結果を参照できます。たとえば、次のステートメントでは、CTE2 は CTE1 の結果を参照します。

cte1 を (
 メニューから*を選択
）、cte2 として（
 メニューから m.*、cte1.name を pname として選択し、m を左にして cte1 を m.pid = cte1.id に結合します。 
）
cte2 から * を選択します。

5. 再帰CTE

再帰 CTE は、サブクエリが自身を参照する特殊な CTE であり、with 句は with recursive で始まる必要があります。

CTE 再帰サブクエリは、union [all] または union distinctive で区切られたシードクエリと再帰クエリの 2 つの部分で構成されます。

シードクエリは、データの初期サブセットを作成するために 1 回実行されます。

再帰クエリは、完全な結果セットが取得されるまで、データのサブセットを返すために繰り返し実行されます。反復によって新しい行が生成されなくなると、再帰は停止します。

再帰的 cte(n) は (
 1を選択
 すべて結合
 cteからn + 1を選択します（n < 10）。
）
cte から * を選択します。

上記のステートメントは、各行に 1 ～ 10 の数字を表示する 10 行を再帰的に表示します。

再帰プロセスは次のとおりです。

1. まず、select 1 を実行して結果 1 を取得すると、n の現在の値は 1 になります。

2. 次に、select n + 1 from cte where n < 10 を実行します。n の現在の値は 1 であるため、where 条件が満たされ、新しい行が生成され、select n + 1 は結果 2 を取得します。n の現在の値は 2 です。

3. select n + 1 from cte where n < 10 の実行を続けます。n の現在の値は 2 なので、where 条件が満たされ、新しい行が生成され、select n + 1 は結果 3 を取得します。n の現在の値は 3 です。

4. 再帰を続ける

5. n が 10 の場合、where 条件が満たされず、新しい行を生成できず、再帰が停止します。

階層関係を持つ一部のデータについては、再帰 CTE を使用して適切に処理できます。

たとえば、各メニューから最上位メニューまでのパスを照会したいとします。

再帰的CTEとして（
 pid = 0 のメニューから、id、name、cast('0' as char(255)) をパスとして選択します。
 すべて結合
 menu.id、menu.name、concat(cte.path、','、cte.id) をメニューからパスとして選択し、menu.pid = cte.id で cte を内部結合します。
）
cte から * を選択します。

再帰プロセスは次のとおりです。

1. まず、pid = 0 のすべてのメニューデータをクエリし、パスを '0' に設定します。この時点で、cte の結果セットは、pid = 0 のすべてのメニューデータになります。

2. menu.pid = cte.id に対して menu inner join cte を実行します。このとき、メニューテーブルは cte (手順 1 で取得した結果セット) と内部結合され、親が最上位メニューであるデータを取得します。

3. menu.pid = cte.id に対して menu inner join cte を引き続き実行します。このとき、テーブル menu は cte (手順 2 で取得した結果セット) と内部結合され、親がメニューの親である最上位メニューのデータを取得します。

4. 再帰を続ける

5. 行が返されなくなると再帰は停止します。

指定されたメニューのすべての親メニューを照会する

再帰的CTEとして（
 メニューから id、name、pid を選択します (id = 7)
 すべて結合
 メニューから menu.id、menu.name、menu.pid を選択し、cte.pid = menu.id で cte を内部結合します。
）
cte から * を選択します。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアドプロシージャスキル」、および「MySQL データベースロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: