MySQL 再帰クエリで階層構造のデータを一度に取得

準備

テーブル定義

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, "Fashion", NULL),
(2, "Mens", 1),
(3, "Ladies", 1),
(4, "Tops", 2),
(5, "Shirts", 4),
(6, "Food", NULL),
(7, "Water", 6),
(8, "Meet", 6),
(9, "Sweets", 6)
;

データの取得

構文

with recursive R as (
  select anchor_data -- 取得1行目ここが起点となる
  union [all]
  select recursive_part　-- 取得2行目以降、ここが再帰となる
  from R, ...
)
select ... -- 実行結果の取得

実行

ついでに1行目のクエリからの階層深度も取得できる形に。

WITH recursive child(depth, id, parent, name) AS(
    SELECT
        0,
        id,
        parent_id,
        name
    FROM
        categories
    WHERE
        categories.id = 1
    UNION ALL
    SELECT
        child.depth + 1,
        categories.id,
        categories.parent_id,
        categories.name
    FROM
        categories,
        child
    WHERE
        categories.parent_id = child.id
)
SELECT
    depth,
    id,
    parent,
    name
FROM
    child
ORDER BY
    depth
;

結果

+-------+------+--------+---------+
| depth | id   | parent | name    |
+-------+------+--------+---------+
|     0 |    1 |   NULL | Fashion |
|     1 |    3 |      1 | Ladies  |
|     1 |    2 |      1 | Mens    |
|     2 |    4 |      2 | Tops    |
|     3 |    5 |      4 | Shirts  |
+-------+------+--------+---------+