June

(mysql) 재귀쿼리로 계층구조 데이터 표현(id - varchar)

April 16, 2021

재귀쿼리로 계층구조 데이터 표현하기(id(varchar)로)

  • id에 varchar로 계층구조를 담아서 하지 않고 integer로 설계해서 표현하는 설계방법은 여기로.
  • mysql 기준으로 설명 (오라클은 CONNECT BY PRIOR, START WITH를 사용하여 자신의 ID와 연결된 부모 ID를 찾아가 계층적으로 쿼리결과를 뽑을 수 있다.)
  • 데이터를 계층구조(정렬순서포함)로 보여주고 싶을 때 사용
  • ex) 메뉴

  • Menu

    • 축구
    • 세리에A
    • K리그
    • 프리메라리그
    • 농구
    • NBA
    • KBL
    • 야구
    • MLB
    • KBO

테이블 설계

  • (조건) id값은 VARCHAR로 설정

    • depth와 계층을 나타내기 위해서 필요
CREATE TABLE `recursion_test` (
	`id` VARCHAR(45) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`m_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`depth` INT(10) NULL DEFAULT NULL,
	`parent` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`m_order` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci'
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;

  • 🧨단점)

    • 위처럼 id를 varchar로 하게 되면?
    • id를 생성할 때 이미 계층구조를 반영한 값들로 입력해야 한다.

데이터

  • id값은 계층마다 규칙이 있어야 한다. (order의 기준이 되기 때문에)

  • Menu(Root)

    • 축구(M0)
    • 세리에A(M01)
    • K리그(M01)
    • 프리메라리그(M01)
    • 농구(M1)
    • NBA(M10)
    • KBL(M11)
    • 야구(M2)
    • MLB(M20)
    • KBO(M21)
id m_name depth parent m_oder
ROOT MENU 0 (NULL) 0
M0 축구 1 ROOT 0
M1 농구 1 ROOT 1
M2 야구 1 ROOT 2
M00 세리에A 2 M0 0
M01 K-LEAGUE 2 M0 1
M10 NBA 2 M1 0
M11 KBL 2 M1 1
M02 프리메라리그 2 M0 2
M20 MLB 2 M2 0
M21 KBO 2 M2 1

쿼리

  • WITH RECURSIVE 문 사용

    • WITH 은 가상 테이블(임시) 생성 위해
    • RECURSIVE 은 재귀구현을 위해
    • 가상의 테이블이 그 자신(가상 테이블)을 참조하여 값을 결정하기 위해

  • WITH RECURSIVE 규칙

    • RECURSIVE를 사용할 때는 서브 쿼리내에서 UNION (ALL) 이 사용되어야 한다.
    • 한 개 이상의 Non-Recursive 문장이 포함되어 있어야 한다. (첫번째 루프에서만 실행됨, anchor 역할임. 이것 미만의 계층구조로 연결되어 있는 것들 다 나옴)
    • 반복되는 Recursive문은 반드시 정지조건(where) 이 필요하다. (반복)
  • 쿼리 실행1 (메뉴(ROOT)에 해당하는 계층 구조 다 확인)
SET @var = 'ROOT';

WITH RECURSIVE t3 (id, m_name, depth, parent, m_order) AS

(
	SELECT t1.id, t1.m_name, t1.depth, t1.parent, t1.m_order
	FROM practice.recursion_test t1
	WHERE t1.parent = @var

	UNION ALL

	SELECT t2.id, t2.m_name, t2.depth, t2.parent, t2.m_order
	FROM practice.recursion_test t2
	INNER JOIN t3 ON t2.parent = t3.id
)

SELECT * FROM t3
ORDER BY t3.id, t3.`m_order`;

결과는?

id m_name depth parent m_oder
M0 축구 1 ROOT 0
M00 세리에A 2 M0 0
M01 K-LEAGUE 2 M0 1
M02 프리메라리그 2 M0 2
M1 농구 1 ROOT 1
M10 NBA 2 M1 0
M11 KBL 2 M1 1
M2 야구 1 ROOT 2
M20 MLB 2 M2 0
M21 KBO 2 M2 1
  • 쿼리 실행2 (축구(M0)에 해당하는 계층 구조 다 확인)
SET @var = 'M0';

WITH RECURSIVE t3 (id, m_name, depth, parent, m_order) AS

(
	SELECT t1.id, t1.m_name, t1.depth, t1.parent, t1.m_order
	FROM practice.recursion_test t1
	WHERE t1.parent = @var

	UNION ALL

	SELECT t2.id, t2.m_name, t2.depth, t2.parent, t2.m_order
	FROM practice.recursion_test t2
	INNER JOIN t3 ON t2.parent = t3.id
)

SELECT * FROM t3
ORDER BY t3.id, t3.`m_order`;

결과는?

id m_name depth parent m_oder
M00 세리에A 2 M0 0
M01 K-LEAGUE 2 M0 1
M02 프리메라리그 2 M0 2
Written by@[June]
Backend Developer