MySQL8の再帰メソッドの使い方を教える

MySQL8の再帰メソッドの使い方を教える

以前、カスタム関数を使用して MySQL でツリー構造を再帰的にクエリする方法についての記事を書きました。MySQL 8.0 以降、再帰クエリ構文がようやくサポートされるようになりました。

熱膨張係数

まず、CTEとは何かを理解しましょう。正式名称はCommon Table Expressionsです。

と
 cte1 AS (テーブル1からa、bを選択)
 cte2 AS (テーブル2からc、dを選択)
cte1からb、dを選択し、cte2に結合します。
ここで cte1.a = cte2.c;

cte1、cte2は定義したCTEであり、現在のクエリで参照できます。

CTE は派生テーブルに似た一時的な結果セットであることがわかります。両者の違いについてはここでは詳しく説明しません。MySQL 開発ドキュメントを参照してください: https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-examples

再帰クエリ

まず、再帰クエリの構文を見てみましょう。

再帰的なcte_name AS
(
  SELECT ... -- 初期行セットを返す
  UNION ALL / UNION DISTINCT
  SELECT ... -- 追加の行セットを返す
)
cteから*を選択します。
  • 最終的な結果セットが、必要な「再帰ツリー構造」である CTE を定義します。RECURSIVE は、現在の CTE が再帰的であることを意味します。
  • 最初のSELECTは「初期結果セット」です
  • 2番目のSELECTは再帰部分であり、「最初の結果セット/最後の再帰によって返された結果セット」を使用して「新しい結果セット」を照会して取得します。
  • 再帰結果セットが null を返すと、クエリは終了します。
  • 最後に、UNION ALL は上記の手順ですべての結果セットを結合し (UNION DISTINCT は重複を削除します)、SELECT * FROM cte; を通じてすべての結果セットを取得します。

再帰部分には以下を含めることはできません。

  • SUM() などの集計関数
  • グループ化
  • 注文する
  • 制限
  • 明確な

上記の説明は少し抽象的かもしれませんので、例を通してゆっくり理解していきましょう。

WITH RECURSIVE cte (n) AS -- ここで定義されたnは結果セットの列名と同等であり、次のクエリでも定義できます(
 選択1
 ユニオンオール
 n + 1 をテーブルから選択する (n < 5)
)
cteから*を選択します。


 - 結果
+------+
| いいえ |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+

  • 初期結果セットはn = 1です
  • 次に、再帰部分を見てみましょう。CTE が初めて実行されると、結果セットは n = 1 になります。n < 5 という条件が満たされていないことがわかったので、n + 1 が返されます。
  • 再帰部分は2回目に実行され、CTE結果セットはn = 2になります。条件が満たされなくなるまで再帰が行われます。
  • 最後に結果セットをマージします


最後に、ツリー構造の例を見てみましょう。

テーブル `c_tree` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `cname` varchar(255) COLLATE utf8mb4_unicode_ci デフォルト NULL,
 `parent_id` int(11) デフォルト NULL,
 主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 デフォルト CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql> c_tree から * を選択します。
+----+---------+-----------+
| id | cname | 親 ID |
+----+---------+-----------+
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 1-1 | 1 |
| 5 | 1-2 | 1 |
| 6 | 2-1 | 2 |
| 7 | 2-2 | 2 |
| 8 | 3-1 | 3 |
| 9 | 3-1-1 | 8 |
| 10 | 3-1-2 | 8 |
| 11 | 3-1-1-1 | 9 |
| 12 | 3-2 | 3 |
+----+---------+-----------+
マイSQL> 
RECURSIVE tree_cte として
(
  parent_id = 3 の c_tree から * を選択
  ユニオンオール
  c_tree t から t.* を選択し、tree_cte tcte を t.parent_id = tcte.id で内部結合します。
)
tree_cte から * を選択します。
+----+---------+-----------+
| id | cname | 親 ID |
+----+---------+-----------+
| 8 | 3-1 | 3 |
| 12 | 3-2 | 3 |
| 9 | 3-1-1 | 8 |
| 10 | 3-1-2 | 8 |
| 11 | 3-1-1-1 | 9 |
+----+---------+-----------+
  • 初期結果セット R0 = select * from c_tree where parent_id = 3
  • 再帰部分では、R0とc_treeの最初の内部結合がR1を取得するために使用されます。
  • R1はc_treeと内部結合されてR2となる。
  • ...
  • すべての結果セットR0 + ... + Riをマージします

詳細情報

https://dev.mysql.com/doc/refman/8.0/en/with.html

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

以下もご興味があるかもしれません:
  • MySQLはカスタム関数を使用して親IDまたは子IDを再帰的に照会します
  • Mysqlツリー再帰クエリの実装方法
  • 無制限レベルの分類データ操作例を実現するための MySQL への PHP 再帰書き込み
  • 再帰を使用してツリー構造のすべての子ノードを削除します (Java と MySQL によって実装)
  • MySQLの再帰問題
  • 再帰なしの PHP + MySQL 無限分類の例 (非再帰)
  • MySQL 再帰クエリ ツリー テーブル子ノード、親ノードの特定の実装

<<:  Windows Server 2019 IIS10.0+PHP(FastCGI)+MySQL 環境構築チュートリアル

>>:  Vue Nativeを使用したモバイルアプリケーションの構築プロセスの完全な記録

推薦する

サーバー同時実行数の推定式と計算方法

最近、サーバーのストレステストを再度行う必要が出てきました。ここでは、最近学んだ見積もりスキームと見...

mysql8.0.11データディレクトリ移行の実装

mysql のデフォルトのストレージ ディレクトリは/var/lib/mysql/です。以下は、デフ...

外部キー制約を持つテーブルデータを削除する MySQL メソッドの紹介

MySQLでテーブルやデータを削除する場合、 [エラー] 1451 - 親行を削除または更新できませ...

Vueはシンプルな画像切り替え効果を実装します

この記事では、Vueの具体的なコード例を参考までに紹介します。具体的な内容は以下のとおりです。 コー...

Mysql系SQLクエリ文の書き順と実行順を詳しく解説

目次1. 完全なSQLクエリステートメントの記述順序2. 完全なSQL文の実行順序3. select...

CSS スタイルの競合を解決するいくつかの方法 (要約)

1. セレクターを調整するコンビネータを使用すると、セレクターの説明をより正確に記述できます (C...

MySQLのさまざまなロックに関する詳細な理解

目次ロックの概要ロックの分類データベース操作の粒度データ操作の種類MySQL ロックさまざまなストレ...

JavaScript が Xiaomi のカルーセル効果を模倣

この記事は、透明度を変えてカルーセルにするXiaomiカルーセルを真似て書いたものです。初心者なので...

Nginx の書き換え正規マッチング書き換え方法の例

Nginx の書き換え機能は、リダイレクトと同様に、URL アドレスを一時的または永続的に新しい場所...

MySQL でスロークエリを有効にする方法の例

序文スロー クエリ ログは、MySQL で非常に重要な機能です。MySQL のスロー クエリ ログ機...

img 画像タグに alt 属性を付与する必要がありますか?

img 画像タグに alt 属性を追加しますか?画像 img タグの alt 属性を見落とすことはよ...

MySQL 外部キー制約の例の説明

MySQL の外部キー制約は、2 つのテーブル間のリンクを確立するために使用されます。 1 つのテー...

画像内のrarファイルを隠す方法

このロゴを .rar ファイルとしてローカルに保存し、解凍して効果を確認することができます。よりシン...

docker を使用して Redis マスター/スレーブを構築する方法

1. Docker環境を構築する1. Dockerfileを作成する Centos:latest か...

MySQL挿入パフォーマンスを最適化する方法の例

MySQL パフォーマンスの最適化MySQL パフォーマンスの最適化とは、リソースを合理的に配置し、...