今日はなにの日。

気になったこと勉強になったことのメモ。

トップ > MySQL > 今日は、RECURSIVEで再帰の罠にかかったの日。

今日は、RECURSIVEで再帰の罠にかかったの日。

MySQL データベース SQL

目次

とある日

RECURSIVEを使ってみたかった。

ただそれだけの理由。

RECURSIVEとは

RECURSIVEとはを知る前に、WITH句について知る必要があります。

このWITH句は便利なので多用してます。

WITH（共通テーブル式）

共通テーブル式（CTE）は、単一のステートメントのスコープ内に存在し、後でそのステートメント内で、場合によっては複数回参照できる、名前付きの一時的な結果セットです。次の説明では、CTEを使用するステートメントの記述方法について説明します。

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

FROM句でテーブルを指定するときにごちゃごちゃせずに事前に必要テーブルを指定できる。

RECURSIVE（再帰共通テーブル式）

再帰共通テーブル式は、それ自体の名前を参照するサブクエリを持つ式です。

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

結果↓

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

UNIONでしたのクエリではWITH句で指定したcteを指定しています。

このように、再帰的に呼び出すことができる。

これで何ができるかというと、木構造になっているものを検索することができるのです。

実践

OracleのデフォルトテーブルのEmployeesを使って実験したいと思います。

型など不要な制約は少し変更しています。

テーブル定義

表HR.EMPLOYEES

Employees INSERT DATA

お好きなだけデータを生成してください。

CREATE TABLE employees
   ( employee_id int
   , first_name VARCHAR(20)
   , last_name VARCHAR(25)
   NOT NULL
   , email VARCHAR(25)
   NOT NULL
   , phone_number VARCHAR(20)
   , hire_date DATE
   NOT NULL
   , job_id VARCHAR(10)
   NOT NULL
   , salary int
   , commission_pct int
   , manager_id int
   , department_id int
   ) ;
ALTER TABLE employees
         ADD ( CONSTRAINT emp_emp_id_pk
   PRIMARY KEY (employee_id)
   ) ;
  INSERT INTO employees VALUES 
   ( 100
   , 'Steven'
   , 'King'
   , 'SKING'
   , '515.123.4567'
   , str_str_to_date('17-JUN-1987', '%d-%b-%Y')
   , 'AD_PRES'
   , 24000
   , NULL
   , NULL
   , 90
   );
INSERT INTO employees VALUES 
   ( 101
   , 'Neena'
   , 'Kochhar'
   , 'NKOCHHAR'
   , '515.123.4568'
   , str_to_date('21-SEP-1989', '%d-%b-%Y')
   , 'AD_VP'
   , 17000
   , NULL
   , 100
   , 90
   );
INSERT INTO employees VALUES 
   ( 102
   , 'Lex'
   , 'De Haan'
   , 'LDEHAAN'
   , '515.123.4569'
   , str_to_date('13-JAN-1993', '%d-%b-%Y')
   , 'AD_VP'
   , 17000
   , NULL
   , 100
   , 90
   );
INSERT INTO employees VALUES 
   ( 103
   , 'Alexander'
   , 'Hunold'
   , 'AHUNOLD'
   , '590.423.4567'
   , str_to_date('03-JAN-1990', '%d-%b-%Y')
   , 'IT_PROG'
   , 9000
   , NULL
   , 102
   , 60
   );
INSERT INTO employees VALUES 
   ( 104
   , 'Bruce'
   , 'Ernst'
   , 'BERNST'
   , '590.423.4568'
   , str_to_date('21-MAY-1991', '%d-%b-%Y')
   , 'IT_PROG'
   , 6000
   , NULL
   , 103
   , 60
   );
INSERT INTO employees VALUES 
   ( 105
   , 'David'
   , 'Austin'
   , 'DAUSTIN'
   , '590.423.4569'
   , str_to_date('25-JUN-1997', '%d-%b-%Y')
   , 'IT_PROG'
   , 4800
   , NULL
   , 103
   , 60
   );
INSERT INTO employees VALUES 
   ( 106
   , 'Valli'
   , 'Pataballa'
   , 'VPATABAL'
   , '590.423.4560'
   , str_to_date('05-FEB-1998', '%d-%b-%Y')
   , 'IT_PROG'
   , 4800
   , NULL
   , 103
   , 60
   );
INSERT INTO employees VALUES 
   ( 107
   , 'Diana'
   , 'Lorentz'
   , 'DLORENTZ'
   , '590.423.5567'
   , str_to_date('07-FEB-1999', '%d-%b-%Y')
   , 'IT_PROG'
   , 4200
   , NULL
   , 103
   , 60
   );
INSERT INTO employees VALUES 
   ( 108
   , 'Nancy'
   , 'Greenberg'
   , 'NGREENBE'
   , '515.124.4569'
   , str_to_date('17-AUG-1994', '%d-%b-%Y')
   , 'FI_MGR'
   , 12000
   , NULL
   , 101
   , 100
   );
INSERT INTO employees VALUES 
   ( 109
   , 'Daniel'
   , 'Faviet'
   , 'DFAVIET'
   , '515.124.4169'
   , str_to_date('16-AUG-1994', '%d-%b-%Y')
   , 'FI_ACCOUNT'
   , 9000
   , NULL
   , 108
   , 100
   );
INSERT INTO employees VALUES 
   ( 110
   , 'John'
   , 'Chen'
   , 'JCHEN'
   , '515.124.4269'
   , str_to_date('28-SEP-1997', '%d-%b-%Y')
   , 'FI_ACCOUNT'
   , 8200
   , NULL
   , 108
   , 100
   );

mysql> show create table employees\G;
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `employee_id` int NOT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) NOT NULL,
  `email` varchar(25) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` varchar(10) NOT NULL,
  `salary` int DEFAULT NULL,
  `commission_pct` int DEFAULT NULL,
  `manager_id` int DEFAULT NULL,
  `department_id` int DEFAULT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

再帰共通テーブル

RECURSIVEを使って何をするかというと、特定のemployee_idを指定してそれに付随する部下を取得するクエリを作成します。　

employee_idが自分のID

そして、manager_idが上司のIDとなっている。

先程の再帰共通テーブルの例に基づいて、UNIONの上側では基底となるemployee_idを指定する。

下側では、cteを再帰に呼び出してWHERE句で比較する。

WITH RECURSIVE cte AS (
  SELECT employee_id, first_name, manager_id
  FROM employees 
  WHERE employee_id = 100
UNION ALL
  SELECT t1.employee_id, t1.first_name, t1.manager_id 
  FROM employees as t1, cte
  WHERE cte.employee_id = t1.employee_id
)
SELECT * FROM cte;

SQLエラー [3636] 発生

先程のクエリを実行するとエラー発生した。

SQLエラー [3636] [HY000]: Recursive query aborted after 2 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

だがしかし、発生理由は把握済みである。

詳しくは、下記の記事をご覧ください。

yoku0825.blogspot.com

簡潔に内容を述べると、再帰呼び出しに制限が存在する。

それを、超えてしまったのが原因。

ってことで、MAX値を更新。

 set @@session.cte_max_recursion_depth = 10000000000;
SELECT @@session.cte_max_recursion_depth;

@@session.cte_max_recursion_depth|
---------------------------------|
                       4294967295|

比較IDミス

そして再度実行。

すると、永遠と終わらない。

変だと思い、SQLを見直したら・・・。

WITH RECURSIVE cte AS (
  SELECT employee_id, first_name, manager_id
  FROM employees 
  WHERE employee_id = 108
UNION ALL
  SELECT t1.employee_id, t1.first_name, t1.manager_id 
  FROM employees as t1, cte
  WHERE cte.employee_id = t1.employee_id
)
SELECT * FROM cte;

上司をするはずの下側SELECT句のWHERE句にt1.employee_idを指定していた。

つまり、ずっと同じIDを呼び出しては比較してを繰り返していると思われる。

なので、cte_max_recursion_depthの上限に引っかかりエラーが発生した。

SQL修正

比較IDをmanager_idに修正した。

WITH RECURSIVE cte AS (
  SELECT employee_id, first_name, manager_id
  FROM employees 
  WHERE employee_id = 108
UNION ALL
  SELECT t1.employee_id, t1.first_name, t1.manager_id 
  FROM employees as t1, cte
  WHERE cte.employee_id = t1.manager_id
)
SELECT * FROM cte;

結果↓

+-------------+------------+------------+
| employee_id | first_name | manager_id |
+-------------+------------+------------+
|         108 | Nancy      |        101 |
|         109 | Daniel     |        108 |
|         110 | John       |        108 |
+-------------+------------+------------+
3 rows in set (0.00 sec)

これで意図した部下の社員IDを取得できた。

〆

便利な世の中になりましたね。

再帰呼び出しでよくやりがちなミスを犯してしまった。

5分ぐらい待ったが終わらないのでおかしいと思った。

今度からは気をつけようと思った。

他にも使ったことのない機能などを試していきMySQLに詳しくなりたいと思う今日このごろ。

参考記事

http://www.matsubarasystems.com/mysql/mysql-to_char

https://docs.oracle.com/cd/E96517_01/comsc/HR-sample-schema-table-descriptions.html#GUID-D3AB4410-FEA7-49A4-A0E4-9E75B2ED40DA

https://download.oracle.com/oll/tutorials/DBXETutorial/html/module2/les02_load_data_sql.htm

https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-fibonacci-series

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

https://yoku0825.blogspot.com/2018/04/mysql-80ctewith-recursive1000.html