MySQL 8.0.3 rc で再帰共通テーブル式(再帰共通表式)を試してみる

MySQL 8.0.3 rc をインストールするところまでは前に実施していたんですが、「再帰共通テーブル式再帰共通表式)」を試してなかったので試してみたいと思います。 

⇩  MySQL 8.0.3 rc の導入については下記記事を参考ください。

ts0818.hatenablog.com

 

試してみたんですが、いろいろ迷走してる感が否めないので、

qiita.com

⇧  上記サイト様を参考にしていただいたほうが良いかと思われます。

ここから先は、お時間ある方のみご照覧ください。

 

再帰共通テーブル式再帰共通表式)

再帰共通テーブル式は、再帰共通表式とも呼ばれますが、こいつは一体何者か?

SQL99は表式の概念を拡張して、一度記述すれば済むWITH句を導入した。WITH句に記述したものを「共通表式」と呼び、そのSQL文中に限り繰り返し使用できる。WITH句の共通表式をさらに拡張したのが、再帰SQLである。

新しい業界標準「SQL99」詳細解説

SQL99は、SQL標準規格の1つである「SQL:1999」が正式名称らしいですね。

当初は特に統一標準規格が存在しない状況で、各関係データベース管理システム (RDBMS) ベンダーごとにさまざまな拡張がなされてきた。

近年になってANSI、後にISOで言語仕様の標準化が行われており、制定された年ごとにSQL86、SQL89、SQL92、SQL:1999SQL:2003SQL:2006、SQL:2008SQL:2011などの規格があるが、対応の程度はベンダーごとにバラバラである。

これは標準SQL策定に時間がかかりすぎたことにより、ビジネスの現状から早期の機能拡張が迫られたベンダーの都合と、独自構文を頻繁に利用していたユーザに対し、互換性保持を保証する必要もあったためである。

SQL規格は非常に多くの改正が行われた。

SQL - Wikipedia

で、SQL99(「SQL:1999」) の仕様の中の、

  • 共通表式(WITH句)
  • 再帰クエリ

を合わせたものが、再帰共通テーブル式再帰共通表式)、またの名を『CTE : Common Table Expressions』というとうことですかね。

ちなみに共通表式(CTE : Common Table Expressions)は、PostgreSQLの説明では、

WITHは、より大規模な問い合わせで使用される補助文を記述する方法を提供します。 これらの文は共通テーブル式またはCTEとよく呼ばれるものであり、1つの問い合わせのためだけに存在する一時テーブルを定義するものと考えられます。 WITH句内の補助文はそれぞれ SELECTINSERTUPDATE または DELETE を取ることができます。 そして WITH句自身は、これも SELECTINSERTUPDATE または DELETE を取ることができる主文に付与されます。

WITH問い合わせ(共通テーブル式) - PostgreSQL 9.3.2文書

となっていて、 再帰共通テーブル式は、

オプションの RECURSIVE修飾子は、WITH を、単に構文上の利便性の高めるだけでなく標準的なSQLでは不可能な機能を実現させます。 RECURSIVE を使用すれば、WITH問い合わせが行った自己の結果を参照できるようになります。

再帰的 WITH問い合わせの汎用形式は常に、再帰的表現(non-recursiveterm)、そして UNION(または UNION ALL)、そして再帰的表現(recursive term)です。 再帰的表現だけが、その問い合わせ自身の出力への参照を含むことができます。

WITH問い合わせ(共通テーブル式) - PostgreSQL 9.3.2文書

となっています。

Microsoftによる(Microsoft SQL Serverについてのことだと思われますが )説明では、

共通テーブル式 (CTE) には、自身を参照して再帰 CTE を作成できるという大きな利点があります。

再帰 CTE を参照するときのクエリを再帰クエリと呼びます。

共通テーブル式を使用する再帰クエリ

となっています。 

blog.amedama.jp

⇧  再帰クエリがサポートされたRDBMSでは、ナイーブツリーというSQLアンチパターンを回避できるそうです。

 

MySQL 8.0 Lab版から、MySQLにおいてもこの再帰共通表式が利用できるようになったようです。

qiita.com

⇧  上記サイト様によると、派生テーブル(FROM句のサブクエリ)を再帰的に記述できるということみたいです。

 

MySQLの "WITH"句 - common-table-expression | CODE Q&A [日本語]

 ⇧  上記サイト様によると、MySQL以外のRDBMSでのWITH構文( Common Table Expressionsとも呼ばれる)が導入された時期が説明されています。

 

サブクエリ(副問い合わせ)とは

サブクエリ(副問い合わせ)って何ぞ~? 

副問い合わせとは、あるselect文の結果を別のselect文で利用することです。
サブクエリとも呼ばれます。

SQL 副問合せとは |

⇧  上記サイト様によると、サブクエリ(副問い合わせ)には、 

  1. where句の副問合せ
  2. from句の副問合せ
  3. select句の副問合せ

の3種類があるそうです。

とりあえずデータベースとテーブルを作成していきます。まずは、MySQLにログイン。

f:id:ts0818:20180103150332j:plain

データベースを作成し、使用するデータベースを指定。 

CREATE DATABASE [データベース名]
USE [データベース名]

f:id:ts0818:20180103160804j:plain

指定したデータベースにテーブルを作成。 

-- 注文
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)
);

f:id:ts0818:20180103194623j:plain

f:id:ts0818:20180103173741j:plain

f:id:ts0818:20180103190353j:plain

f:id:ts0818:20180103173827j:plain

作成した各テーブルにデータを挿入。

-- 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);

f:id:ts0818:20180105191522j:plain

 

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;

f:id:ts0818:20180105191311j:plain

 

3. select句の副問合せ

select句の副問合せを使用して、注文額が平均より大きい注文データを表示。

SELECT client_id, 
order_date, 
sales,
sales > (SELECT AVG(sales) FROM orders) AS hantei
FROM orders;

「hantei」には、注文額の平均を超えていたら1,それ以外の場合は0が入るようです。

f:id:ts0818:20180105191326j:plain

 

再帰共通テーブル式再帰共通表式)を試してみる

MySQL 8.0 Lab版から、WITH句のRECURSIVEオプションを使うことで「再帰共通テーブル式再帰共通表式)」を実装することができるようです。

さっそく試してみたところ、エラーが....。 

f:id:ts0818:20180106104709j:plain

⇩  無限ループっぽくなってる感じですかね。

『ERROR 4536 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@ cte_max_recursion_depth to a larger value.』

MySQL 8.0: Common Table Expressions - Percona

 

MySQL :: MySQL 8.0 Reference Manual :: 13.2.11.9 WITH Syntax (Common Table Expressions)

⇧  関係ないとは思いますが、外部キーを設定してるっぽいですね。

もしかしたら、外部キーの設定が必要かと思ったので、設定してみます。 一旦、テーブルを削除して、作り直した後に、外部キーを追加しました。

kengotakimoto.com

⇧  上記サイト様によると、制約名(constraint)を指定しないとエラーになるようです。

qiita.com

⇧  また、制約名(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;

 f:id:ts0818:20180107171617j:plain

INSERT文でorder_detailsテーブルにデータを追加しておきます。 

そして、結果は変わらず...外部キーは関係なさそうですね。

f:id:ts0818:20180107183638j:plain

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;

f:id:ts0818:20180107183811j:plain

そもそものテーブル構成が宜しくないとは思いますが、とりあえず動いたということで。今回も時間を無駄に浪費してしまった(涙)。

データベースのテーブル構成とかも勉強していきたいですね。今回はこのへんで。

 

 

 

番外編:外部キー制約を設定したテーブルの削除

外部キー(FOREIGN KEY)を設定していると、いろいろ大変みたいです。

taka-say.hateblo.jp

⇧  上記サイト様によると、外部キーを削除するには、CONSTRAINT で設定されている文字列を指定してあげる必要があるようです。

確認する方法

SHOW CREATE TABLE [テーブル名]\G

f:id:ts0818:20180106221129j:plain

確認する方法は他にもあり、 

table_constraintsにて依存関係の確認 – variable.jp [データベース,パフォーマンス,運用]

⇧  上記サイト様によると、特定のテーブルの外部キー制約のみ確認 する場合は、

SELECT * FROM information_schema.table_constraints 
WHERE table_schema = 'データベース名' 
AND table_name = 'テーブル名' 
AND constraint_type='FOREIGN KEY';

でいけるようです。

www.mysqltutorial.org

⇧  上記サイト様の方法とかも試してみるも、

⇩  一向に処理が進まず、「Ctrl + C」でクエリのプロセスを強制的に削除。

f:id:ts0818:20180107001235j:plain

【MySQL】外部キーの制約を無視してテーブルを削除する | ミーミルの泥泉

⇧  上記サイト様でも、「SET FOREIGN_KEY_CHECKS = 0;」で一時的に制約を無効にすれば、外部キー制約に関係なくテーブルの削除ができるような雰囲気となっていましたが、処理が進まず。

 

八方ふさがり的な感じですが、

www.slideshare.net

⇧  上記サイト様で、ロックの話が出ていたので、何か関係してるかと思い、調査。 

www.tree-tips.com

⇧  上記サイト様によると、

外部キーはslock(共有ロック)を取得してしまうからなのです。

slockは、xlock(排他ロック)をブロックしますが、slockをブロックしません。ここが問題なのです。

つまり、別セッションによる親テーブルのロック取得を許してしまうのです。

子テーブルのinsertでロックを取得するのは、親に属さない子が存在しない事を保証するためです。

これだけ読むと仕様通りなのですが、問題なのは自動的にslockを取得する点です。

tree-tips: MySQLの外部キーとデッドロック | MySQL

となっていますが、 今回はINSERTとかしてないので関係ないのか...。

 

結局、原因が分からず、翌日PCを起ち上げて試してみたところ、あっさり実行できたという...

f:id:ts0818:20180107131058j:plain

order_detailsテーブルの外部キーの確認。

SELECT * FROM information_schema.table_constraints 
WHERE table_schema = 'subquery_test' 
AND table_name = 'order_details' 
AND constraint_type='FOREIGN KEY';

f:id:ts0818:20180107131735j:plain

order_detailsテーブルの削除。

f:id:ts0818:20180107132234j:plain

ちなみに、外部キーを作成すると、自動でINDEXも作成されているようです。外部キーを削除して、テーブルを削除せず利用していくときは、INDEXも削除したほうが良いのかもしれません。

somenotes.seesaa.net

そして、auto_incrementとかも初期化できたりするんですね。

dotnsf.blog.jp

 

 

今回、おそらく、外部キーの削除の処理が実行されなかった原因は、自動で勝手に実行される『C:¥Program Files (x86MySQL¥MySQL Installer for Windows¥MySQLInstallerConsole.exe』とバッティングしてしまっていたのではないかと思われます。

MySQL の謎なアップデータを止める方法 | Moonmile Solutions Blog

 

困ったときは、PCの再起動ですかね。