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 | +-------+------+--------+---------+