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

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データベース設計に役立つことを願っています。

以下もご興味があるかもしれません:
  • MySQL 8 の新機能: 自動増分主キーの永続性に関する詳細な説明
  • MySQL 8.0 以降の一般的なコマンドの詳細な説明
  • MySQL 8.0 の新機能: アトミック DDL ステートメントのサポート
  • MySQL 8.0 DDLアトミック機能と実装原則
  • Mysql8.0はソート問題を解決するためにウィンドウ関数を使用する
  • MySQL 8.0.18コマンドの詳細な説明
  • MySQL 8.0.18はデータベースにユーザーを追加し、権限を付与します
  • MySql8.0以降のバージョンでROOTパスワードを正しく変更する方法
  • SQL における CTE (共通テーブル式) の概要
  • SQL学習3日目 - SQL再帰クエリCTE(共通式)の使い方
  • SQL 学習 2 日目 - SQL DML と CTE の概要
  • SQL Serverは共通テーブル式(CTE)を使用して無制限のツリー構築を実装します。

<<:  Ubuntu Linuxシステムをインストールするときにハードディスクをパーティション分割する最も合理的な方法の詳細な説明

>>:  Ubuntu 20.04 aptの国内ソースを変更する方法

推薦する

Nginx でファイル ホットリンク保護サービスを構築する方法を学ぶ例

序文多くのサイトが、ポイントやゴールドコインなど、情報のダウンロードに料金を請求していることは誰もが...

Jenkinsを使用してプロジェクトを別のホストにデプロイするプロセス

環境ホスト名IPアドレス仕えるジェンキンス192.168.216.200トムキャット、ジェンキンスサ...

レスポンシブなカードホバー効果を実現するための HTML+CSS

目次成し遂げる:要約:言うことはあまりありませんが、まずは効果を見てみましょう。 カードホバー、レス...

Dockerデーモンのセキュリティ設定項目の詳細な説明

目次1. テスト環境1.1 CentOS 7をインストールする1.2 Docker CE 19.03...

MySQL 8.0.26 のインストールと簡易チュートリアル (インターネット上で最も完全)

目次1. MySQLをダウンロードする1.1 ダウンロード1.2 インストール1. MySQLをダウ...

MySQL はどのようにしてマスターとスレーブの一貫性を確保するのでしょうか?

目次MySQLマスタースレーブの基本原理3つのbinlog形式の比較混合形式のバイナリログが存在する...

Dockeにredisをインストールする方法

1. redisイメージを検索する docker 検索 redis 2. Redisイメージをダウン...

hrefパラメータ転送における中国語の文字化けについて

パラメータを渡すために href が必要で、パラメータが中国語の場合、文字化けした文字が表示されます...

Angular CDK を使用してサービスポップアップトーストコンポーネント機能を実装する

目次1. 環境設備2. ToastコンポーネントとToastServiceを作成する2.1 Toas...

Web データ ストレージ: Cookie、UserData、SessionStorage、WebSqlDatabase

クッキーこれはクライアント ブラウザの状態を保存するための標準的な方法です。Cookie はブラウザ...

CentOS に MySQL をインストールしてリモート アクセスを設定する方法

1. MySQLリポジトリソースをダウンロードする$ wget http://repo.mysql....

Vue プラグイン エラー: このページで Vue.js が検出されました。問題は解決しました

Vue プラグインがエラーを報告しました: このページで Vue.js が検出されましたVueプラグ...

Dockerコンテナを更新、パッケージ化、Alibaba Cloudにアップロードする方法

今回は、実行中のコンテナをイメージにパッケージ化して Alibaba Cloud にアップロードし、...

Linux環境でタイムゾーンを設定できない問題を解決

Linuxでタイムゾーンを変更する場合、常に変更することはできませんAsia/Shanghai に変...

Dockerイメージ作成の完全なプロセス

目次序文作成手順CentOSベースイメージを作成するコンテナを作成してカスタマイズするカスタムコンテ...