Recursive CTEで多階層カテゴリのレコードを取得

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)

参考

わくわくBank.
フリーランスのエンジニアとして活動してます。ここでは、ソフトウェア開発で必要とされる技術、用語、概念を整理しています。