MySQL8.0から Recursive CTE(Common Table Expressions) を利用できるようになりました。ここでは、再帰クエリ の動作確認をします。多階層カテゴリを持つデータ構造から、特定カテゴリの子孫カテゴリをまとめて取得します。
目次
前準備
検証用テーブル作成
下記クエリを実行してテーブルを作成します。
CREATE TABLE `categories` (
`id` int(10) unsigned NOT NULL,
`name` varchar(45) NOT NULL,
`parent_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;検証用レコード挿入
下記カテゴリ構造となるようにレコードを挿入します。
├── 暮らし
│ ├── 料理
│ │ ├── 和食
│ │ │ ├── 肉じゃが
│ │ │ ├── 味噌汁
│ │ │ └── 天ぷら
│ │ ├── 中華
│ │ ├── イタリアン
│ │ └── フレンチ
│ ├── ペット
│ └── ファッション
└── ビジネス
├── 経済学
├── マーケティング
└── 経営下記クエリを実行します。
INSERT INTO `categories`
(`id`, `name`, `parent_id`)
VALUES
(1, "暮らし", NULL),
(2, "料理", 1),
(3, "和食", 2),
(4, "肉じゃか", 3),
(5, "味噌汁", 3),
(6, "天ぷら", 3),
(7, "中華", 2),
(8, "イタリアン", 2),
(9, "フレンチ", 2),
(10, "ペット", 1),
(11, "ファッション", 1),
(12, "ビジネス", NULL),
(13, "経済学", 12),
(14, "マーケティング", 12),
(15, "経営", 12);mysql> SELECT * FROM `categories`;
+----+-----------------------+-----------+
| id | name | parent_id |
+----+-----------------------+-----------+
| 1 | 暮らし | NULL |
| 2 | 料理 | 1 |
| 3 | 和食 | 2 |
| 4 | 肉じゃか | 3 |
| 5 | 味噌汁 | 3 |
| 6 | 天ぷら | 3 |
| 7 | 中華 | 2 |
| 8 | イタリアン | 2 |
| 9 | フレンチ | 2 |
| 10 | ペット | 1 |
| 11 | ファッション | 1 |
| 12 | ビジネス | NULL |
| 13 | 経済学 | 12 |
| 14 | マーケティング | 12 |
| 15 | 経営 | 12 |
+----+-----------------------+-----------+
15 rows in set (0.00 sec)再帰クエリで子孫をまとめて取得
Recursive CTEの書き方
再帰クエリは以下のように作成します。
WITH RECURSIVE cte AS (
SELECT ... -- 最初のクエリ
UNION ALL
SELECT ... -- ここの部分が再帰クエリになります
)
SELECT * FROM cte;例を示します。
WITH RECURSIVE `cte` AS (
SELECT 1 as `id`
UNION ALL
SELECT `cte`.`id` + 1 as `id`
FROM `cte`
WHERE `cte`.`id` < 5
)
SELECT * FROM `cte`;mysql> WITH RECURSIVE `cte` AS (
-> SELECT 1 as `id`
-> UNION ALL
-> SELECT `cte`.`id` + 1 as `id`
-> FROM `cte`
-> WHERE `cte`.`id` < 5
-> )
-> SELECT * FROM `cte`;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)子孫カテゴリを取得
暮らしカテゴリ自身 と その子孫カテゴリ を取得するクエリです。
SET @root_name = '暮らし';
WITH RECURSIVE `cte` AS (
SELECT `id`, `name`, `parent_id`
FROM `categories`
WHERE `name` = @root_name
UNION ALL
SELECT `child_categories`.`id`,
`child_categories`.`name`,
`child_categories`.`parent_id`
FROM `categories` AS `child_categories`, `cte`
WHERE `cte`.`id` = `child_categories`.`parent_id`
)
SELECT * FROM `cte`;実行します。
mysql> SET @root_name = '暮らし';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> WITH RECURSIVE `cte` AS (
-> SELECT `id`, `name`, `parent_id`
-> FROM `categories`
-> WHERE `name` = @root_name
-> UNION ALL
-> SELECT `child_categories`.`id`,
-> `child_categories`.`name`,
-> `child_categories`.`parent_id`
-> FROM `categories` AS `child_categories`, `cte`
-> WHERE `cte`.`id` = `child_categories`.`parent_id`
-> )
-> SELECT * FROM `cte`;
+------+--------------------+-----------+
| id | name | parent_id |
+------+--------------------+-----------+
| 1 | 暮らし | NULL |
| 2 | 料理 | 1 |
| 10 | ペット | 1 |
| 11 | ファッション | 1 |
| 3 | 和食 | 2 |
| 7 | 中華 | 2 |
| 8 | イタリアン | 2 |
| 9 | フレンチ | 2 |
| 4 | 肉じゃか | 3 |
| 5 | 味噌汁 | 3 |
| 6 | 天ぷら | 3 |
+------+--------------------+-----------+
11 rows in set (0.01 sec)今度は、料理カテゴリ自身 と その子孫カテゴリ を取得します。
mysql> SET @root_name = '料理';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> WITH RECURSIVE `cte` AS (
-> SELECT `id`, `name`, `parent_id`
-> FROM `categories`
-> WHERE `name` = @root_name
-> UNION ALL
-> SELECT `child_categories`.`id`,
-> `child_categories`.`name`,
-> `child_categories`.`parent_id`
-> FROM `categories` AS `child_categories`, `cte`
-> WHERE `cte`.`id` = `child_categories`.`parent_id`
-> )
-> SELECT * FROM `cte`;
+------+-----------------+-----------+
| id | name | parent_id |
+------+-----------------+-----------+
| 2 | 料理 | 1 |
| 3 | 和食 | 2 |
| 7 | 中華 | 2 |
| 8 | イタリアン | 2 |
| 9 | フレンチ | 2 |
| 4 | 肉じゃか | 3 |
| 5 | 味噌汁 | 3 |
| 6 | 天ぷら | 3 |
+------+-----------------+-----------+
8 rows in set (0.00 sec)
