MySQL 8.0.3 rc をインストールするところまでは前に実施していたんですが、「再帰共通テーブル式(再帰共通表式)」を試してなかったので試してみたいと思います。
⇩ MySQL 8.0.3 rc の導入については下記記事を参考ください。
試してみたんですが、いろいろ迷走してる感が否めないので、
⇧ 上記サイト様を参考にしていただいたほうが良いかと思われます。
ここから先は、お時間ある方のみご照覧ください。
再帰共通テーブル式(再帰共通表式)
再帰共通テーブル式は、再帰共通表式とも呼ばれますが、こいつは一体何者か?
SQL99は表式の概念を拡張して、一度記述すれば済むWITH句を導入した。WITH句に記述したものを「共通表式」と呼び、そのSQL文中に限り繰り返し使用できる。WITH句の共通表式をさらに拡張したのが、再帰SQLである。
SQL99は、SQL標準規格の1つである「SQL:1999」が正式名称らしいですね。
当初は特に統一標準規格が存在しない状況で、各関係データベース管理システム (RDBMS) ベンダーごとにさまざまな拡張がなされてきた。
近年になってANSI、後にISOで言語仕様の標準化が行われており、制定された年ごとにSQL86、SQL89、SQL92、SQL:1999、SQL:2003、SQL:2006、SQL:2008、SQL:2011などの規格があるが、対応の程度はベンダーごとにバラバラである。
これは標準SQL策定に時間がかかりすぎたことにより、ビジネスの現状から早期の機能拡張が迫られたベンダーの都合と、独自構文を頻繁に利用していたユーザに対し、互換性保持を保証する必要もあったためである。
SQL規格は非常に多くの改正が行われた。
で、SQL99(「SQL:1999」) の仕様の中の、
- 共通表式(WITH句)
- 再帰クエリ
を合わせたものが、再帰共通テーブル式(再帰共通表式)、またの名を『CTE : Common Table Expressions』というとうことですかね。
ちなみに共通表式(CTE : Common Table Expressions)は、PostgreSQLの説明では、
WITHは、より大規模な問い合わせで使用される補助文を記述する方法を提供します。 これらの文は共通テーブル式またはCTEとよく呼ばれるものであり、1つの問い合わせのためだけに存在する一時テーブルを定義するものと考えられます。 WITH句内の補助文はそれぞれ SELECT、INSERT、UPDATE または DELETE を取ることができます。 そして WITH句自身は、これも SELECT、INSERT、UPDATE または DELETE を取ることができる主文に付与されます。
オプションの RECURSIVE修飾子は、WITH を、単に構文上の利便性の高めるだけでなく標準的なSQLでは不可能な機能を実現させます。 RECURSIVE を使用すれば、WITH問い合わせが行った自己の結果を参照できるようになります。
再帰的 WITH問い合わせの汎用形式は常に、非再帰的表現(non-recursiveterm)、そして UNION(または UNION ALL)、そして再帰的表現(recursive term)です。 再帰的表現だけが、その問い合わせ自身の出力への参照を含むことができます。
となっています。
Microsoftによる(Microsoft SQL Serverについてのことだと思われますが )説明では、
となっています。
⇧ 再帰クエリがサポートされたRDBMSでは、ナイーブツリーというSQLのアンチパターンを回避できるそうです。
MySQL 8.0 Lab版から、MySQLにおいてもこの再帰共通表式が利用できるようになったようです。
⇧ 上記サイト様によると、派生テーブル(FROM句のサブクエリ)を再帰的に記述できるということみたいです。
・MySQLの "WITH"句 - common-table-expression | CODE Q&A [日本語]
⇧ 上記サイト様によると、MySQL以外のRDBMSでのWITH構文( Common Table Expressionsとも呼ばれる)が導入された時期が説明されています。
サブクエリ(副問い合わせ)とは
サブクエリ(副問い合わせ)って何ぞ~?
副問い合わせとは、あるselect文の結果を別のselect文で利用することです。
サブクエリとも呼ばれます。
⇧ 上記サイト様によると、サブクエリ(副問い合わせ)には、
- where句の副問合せ
- from句の副問合せ
- select句の副問合せ
の3種類があるそうです。
とりあえずデータベースとテーブルを作成していきます。まずは、MySQLにログイン。
データベースを作成し、使用するデータベースを指定。
CREATE DATABASE [データベース名] USE [データベース名]
指定したデータベースにテーブルを作成。
-- 注文 CREATE TABLE orders( order_id int NOT NULL auto_increment, client VARCHAR(255) NOT NULL, order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, sales DECIMAL(10, 3), PRIMARY KEY(order_id) ); -- 取引先 CREATE TABLE clients( client_id int NOT NULL auto_increment, client_name VARCHAR(255) NOT NULL, PRIMARY KEY(client_id) ); -- 国 CREATE TABLE countries( country_id int NOT NULL auto_increment, country_name VARCHAR(255), PRIMARY KEY(country_id) ); -- 生産地 CREATE TABLE producing_district( district_id int NOT NULL auto_increment, district_name VARCHAR(255) NOT NULL, country_id int NOT NULL, PRIMARY KEY(district_id) ); -- 商品カテゴリー CREATE TABLE item_category( category_id int NOT NULL auto_increment, category_name VARCHAR(255) NOT NULL, PRIMARY KEY(category_id) ); -- カテゴリー詳細 CREATE TABLE category_details( category_details_id int NOT NULL auto_increment, category_details_name VARCHAR(255) NOT NULL, PRIMARY KEY(category_details_id) ); -- 商品情報 CREATE TABLE items( item_id int NOT NULL auto_increment, item_name VARCHAR(255) NOT NULL, item_price DECIMAL(10, 2) NOT NULL, item_category_id int NOT NULL, producing_district_id int NOT NULL, PRIMARY KEY(item_id) ); -- 取引詳細 CREATE TABLE order_details( id int NOT NULL auto_increment, item_id int NOT NULL, item_count int NOT NULL, item_cost DECIMAL(10, 2) NOT NULL, order_id int NOT NULL, PRIMARY KEY(id) );
作成した各テーブルにデータを挿入。
-- countriesテーブルにデータを挿入 INSERT INTO countries(country_name) VALUES('日本'); INSERT INTO countries(country_name) VALUES('中国'); INSERT INTO countries(country_name) VALUES('アメリカ'); INSERT INTO countries(country_name) VALUES('カナダ'); INSERT INTO countries(country_name) VALUES('ロシア'); INSERT INTO countries(country_name) VALUES('メキシコ'); INSERT INTO countries(country_name) VALUES('フィリピン'); INSERT INTO countries(country_name) VALUES('タイ'); -- producing_districtテーブルにデータを挿入 INSERT INTO producing_district(district_name, country_id) VALUES('北海道', 1); INSERT INTO producing_district(district_name, country_id) VALUES('青森県', 1); INSERT INTO producing_district(district_name, country_id) VALUES('秋田県', 1); INSERT INTO producing_district(district_name, country_id) VALUES('岩手県', 1); INSERT INTO producing_district(district_name, country_id) VALUES('山形県', 1); INSERT INTO producing_district(district_name, country_id) VALUES('宮城県県', 1); INSERT INTO producing_district(district_name, country_id) VALUES('新潟県', 1); INSERT INTO producing_district(district_name, country_id) VALUES('福島県', 1); INSERT INTO producing_district(district_name, country_id) VALUES('長野県', 1); -- item_categoryテーブルにデータを挿入 INSERT INTO item_category(category_name) VALUES('食料品'); INSERT INTO item_category(category_name) VALUES('雑貨'); -- category_detailsテーブルにデータを挿入 INSERT INTO category_details(category_details_name, category_id) VALUES('米', 1); INSERT INTO category_details(category_details_name, category_id) VALUES('小麦粉', 1); INSERT INTO category_details(category_details_name, category_id) VALUES('そば', 1); INSERT INTO category_details(category_details_name, category_id) VALUES('パスタ', 1); -- itemsテーブルにデータを挿入 INSERT INTO items(item_name, item_price, item_category_id, producing_district_id) VALUES('こしひかり', 49.80, 1, 7); INSERT INTO items(item_name, item_price, item_category_id, producing_district_id) VALUES('あきたこまち', 39.80, 1, 3); INSERT INTO items(item_name, item_price, item_category_id, producing_district_id) VALUES('ささにしき', 39.80, 1, 6); INSERT INTO items(item_name, item_price, item_category_id, producing_district_id) VALUES('ひとめぼれ', 38.80, 1, 5); INSERT INTO items(item_name, item_price, item_category_id, producing_district_id) VALUES('まっしぐら', 37.80, 1, 2); INSERT INTO items(item_name, item_price, item_category_id, producing_district_id) VALUES('春よ恋', 59.80, 2, 1); INSERT INTO items(item_name, item_price, item_category_id, producing_district_id) VALUES('きたほなみ', 68.80, 2, 1); INSERT INTO items(item_name, item_price, item_category_id, producing_district_id) VALUES('ゆめちから', 57.80, 2, 1); INSERT INTO items(item_name, item_price, item_category_id, producing_district_id) VALUES('戸隠そば', 37.80, 3, 9); INSERT INTO items(item_name, item_price, item_category_id, producing_district_id) VALUES('知床そば', 37.80, 3, 1); -- clientsテーブルにデータ挿入 INSERT INTO clients(client_name) VALUES('株式会社オコノミ屋'); INSERT INTO clients(client_name) VALUES('株式会社おにぎり本舗'); INSERT INTO clients(client_name) VALUES('株式会社生そば藪'); -- ordersテーブルにデータを挿入 INSERT INTO orders(client_id, order_date, sales) VALUES(1, now(), 179.400); INSERT INTO orders(client_id, order_date, sales) VALUES(2, now(), 129.400); INSERT INTO orders(client_id, order_date, sales) VALUES(3, now(), 173.880); -- order_detailsテーブルにデータを挿入 INSERT INTO order_details(item_id, item_count, item_cost, order_id) VALUES(6, 10, 59.80, 1);
INSERT INTO order_details(item_id, item_count, item_cost, order_id) VALUES(7, 10, 59.80, 1);
INSERT INTO order_details(item_id, item_count, item_cost, order_id) VALUES(8, 10, 59.80, 1);
INSERT INTO order_details(item_id, item_count, item_cost, order_id) VALUES(1, 10, 49.80, 2);
INSERT INTO order_details(item_id, item_count, item_cost, order_id) VALUES(2, 10, 39.80, 2);
INSERT INTO order_details(item_id, item_count, item_cost, order_id) VALUES(3, 10, 39.80, 2);
INSERT INTO order_details(item_id, item_count, item_cost, order_id) VALUES(9, 30, 37.80, 3);
INSERT INTO order_details(item_id, item_count, item_cost, order_id) VALUES(10, 8, 37.80, 3);
INSERT INTO order_details(item_id, item_count, item_cost, order_id) VALUES(10, 8, 37.80, 3);
1. where句の副問合せ
where句の副問合せを使用して、注文額が平均より大きい注文データを表示。
SELECT * FROM orders WHERE sales > (SELECT AVG(sales) FROM orders);
2. from句の副問合せ
from句の副問合せを使用して、注文額が平均より大きい注文データを表示。
SELECT b.order_id, b.client_id, b.order_date, b.sales FROM (SELECT AVG(sales) AS heikin FROM orders) AS a, orders AS b WHERE a.heikin < b.sales;
3. select句の副問合せ
select句の副問合せを使用して、注文額が平均より大きい注文データを表示。
SELECT client_id, order_date, sales, sales > (SELECT AVG(sales) FROM orders) AS hantei FROM orders;
「hantei」には、注文額の平均を超えていたら1,それ以外の場合は0が入るようです。
再帰共通テーブル式(再帰共通表式)を試してみる
MySQL 8.0 Lab版から、WITH句のRECURSIVEオプションを使うことで「再帰共通テーブル式(再帰共通表式)」を実装することができるようです。
さっそく試してみたところ、エラーが....。
⇩ 無限ループっぽくなってる感じですかね。
『ERROR 4536 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@ cte_max_recursion_depth to a larger value.』
・MySQL :: MySQL 8.0 Reference Manual :: 13.2.11.9 WITH Syntax (Common Table Expressions)
⇧ 関係ないとは思いますが、外部キーを設定してるっぽいですね。
もしかしたら、外部キーの設定が必要かと思ったので、設定してみます。 一旦、テーブルを削除して、作り直した後に、外部キーを追加しました。
⇧ 上記サイト様によると、制約名(constraint)を指定しないとエラーになるようです。
⇧ また、制約名(constraint)は、外部キーに名前を付けることができるようです。
ALTER TABLE order_details ADD CONSTRAINT fkey_parent_order_id FOREIGN KEY(order_id) REFERENCES orders(order_id) ON UPDATE CASCADE ON DELETE CASCADE;
INSERT文でorder_detailsテーブルにデータを追加しておきます。
そして、結果は変わらず...外部キーは関係なさそうですね。
id = 2にしたら、動いたけど...。
WITH RECURSIVE target AS ( SELECT id, item_id, item_count, item_cost, order_id FROM order_details WHERE id = 2 UNION ALL SELECT child.id, child.item_id, child.item_count, child.item_cost, child.order_id FROM order_details AS child, target WHERE target.id = child.order_id ) SELECT * FROM target;
そもそものテーブル構成が宜しくないとは思いますが、とりあえず動いたということで。今回も時間を無駄に浪費してしまった(涙)。
データベースのテーブル構成とかも勉強していきたいですね。今回はこのへんで。
番外編:外部キー制約を設定したテーブルの削除
外部キー(FOREIGN KEY)を設定していると、いろいろ大変みたいです。
⇧ 上記サイト様によると、外部キーを削除するには、CONSTRAINT で設定されている文字列を指定してあげる必要があるようです。
確認する方法
SHOW CREATE TABLE [テーブル名]\G
確認する方法は他にもあり、
・table_constraintsにて依存関係の確認 – variable.jp [データベース,パフォーマンス,運用]
⇧ 上記サイト様によると、特定のテーブルの外部キー制約のみ確認 する場合は、
SELECT * FROM information_schema.table_constraints WHERE table_schema = 'データベース名' AND table_name = 'テーブル名' AND constraint_type='FOREIGN KEY';
でいけるようです。
⇧ 上記サイト様の方法とかも試してみるも、
⇩ 一向に処理が進まず、「Ctrl + C」でクエリのプロセスを強制的に削除。
・【MySQL】外部キーの制約を無視してテーブルを削除する | ミーミルの泥泉
⇧ 上記サイト様でも、「SET FOREIGN_KEY_CHECKS = 0;」で一時的に制約を無効にすれば、外部キー制約に関係なくテーブルの削除ができるような雰囲気となっていましたが、処理が進まず。
八方ふさがり的な感じですが、
⇧ 上記サイト様で、ロックの話が出ていたので、何か関係してるかと思い、調査。
⇧ 上記サイト様によると、
外部キーはslock(共有ロック)を取得してしまうからなのです。
slockは、xlock(排他ロック)をブロックしますが、slockをブロックしません。ここが問題なのです。
つまり、別セッションによる親テーブルのロック取得を許してしまうのです。
子テーブルのinsertでロックを取得するのは、親に属さない子が存在しない事を保証するためです。
これだけ読むと仕様通りなのですが、問題なのは自動的にslockを取得する点です。
となっていますが、 今回はINSERTとかしてないので関係ないのか...。
結局、原因が分からず、翌日PCを起ち上げて試してみたところ、あっさり実行できたという...
order_detailsテーブルの外部キーの確認。
SELECT * FROM information_schema.table_constraints WHERE table_schema = 'subquery_test' AND table_name = 'order_details' AND constraint_type='FOREIGN KEY';
order_detailsテーブルの削除。
ちなみに、外部キーを作成すると、自動でINDEXも作成されているようです。外部キーを削除して、テーブルを削除せず利用していくときは、INDEXも削除したほうが良いのかもしれません。
そして、auto_incrementとかも初期化できたりするんですね。
今回、おそらく、外部キーの削除の処理が実行されなかった原因は、自動で勝手に実行される『C:¥Program Files (x86)¥MySQL¥MySQL Installer for Windows¥MySQLInstallerConsole.exe』とバッティングしてしまっていたのではないかと思われます。
・MySQL の謎なアップデータを止める方法 | Moonmile Solutions Blog
困ったときは、PCの再起動ですかね。