MySQL RECURSIVE TREE메뉴 추출

1. 테이블, 데이터 생성

CREATE TABLE tree (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  parent_id INT DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES tree(id) ON DELETE CASCADE
);

INSERT INTO tree (name, parent_id) VALUES ('Root', NULL);
INSERT INTO tree (name, parent_id) VALUES ('Child 1', 1);
INSERT INTO tree (name, parent_id) VALUES ('Child 2', 1);
INSERT INTO tree (name, parent_id) VALUES ('Grandchild 1-1', 2);
INSERT INTO tree (name, parent_id) VALUES ('Grandchild 1-2', 2);
INSERT INTO tree (name, parent_id) VALUES ('Grandchild 2-1', 3);
INSERT INTO tree (name, parent_id) VALUES ('Grandchild 2-2', 3);

 

2. 트리구조 쿼리 조회

WITH RECURSIVE tree_path(id, name, parent_id, depth, path) AS (
  SELECT id, name, parent_id, 0, CAST(id AS CHAR(200))
  FROM tree
  WHERE parent_id IS NULL
  UNION ALL
  SELECT t.id, t.name, t.parent_id, tp.depth + 1, CONCAT(tp.path, ', ', t.id)
  FROM tree t
  INNER JOIN tree_path tp ON t.parent_id = tp.id
)
SELECT id, name, parent_id, depth, path
FROM tree_path
ORDER BY path;

 

3. 결과

id  name  parent_id  depth  path
--  ----  ---------  -----  ----
1   A     NULL       0      1
2   B     1          1      1,2
4   D     2          2      1,2,4
6   F     4          3      1,2,4,6
7   G     4          3      1,2,4,7
3   C     1          1      1,3
5   E     3          2      1,3,5
8   H     5          3      1,3,5,8