[MariaDB] ORACLE의 START WITH 절 MariaDB에서 사용하기

부서별 계층 관계나 품목 등 여러 비즈니스 로직에 활용 할 수 있는 계층형 쿼리.

 

ORACLE 의 계층형 쿼리  START WITH ... CONNECT BY 구문은 MariaDB에서는 지원되지 않으므로 대신에 CTE라는 재귀 쿼리를 사용해야 한다.

 

우선 MariaDB는 10.2 버전 이상에서 WITH RECURSIVE를 지원한다. (해당 글에서의 방법은 10.2 이상 버전에서만 가능)

 

 

우선 계층형 쿼리란 부모, 자식간의 수직관계를 트리 구조 형태로 보여주는 쿼리이다. 

START WITH ... CONNECT BY 형태로 사용하며,

START WITH에 트리 구조의 최상위 행을 지정, CONNECT BY 에 부모, 자식의 관계를 지정한다. 

CONNECT BY PRIOR 자식 컬럼 = 부모 컬럼 -> 자식 컬럼이 부모 컬럼을 찾아간다. 부모 -> 자식 순방향 전개

CONNECT BY PRIOR 부모 컬럼 = 자식 컬럼 -> 부모 컬럼이 자식 컬럼을 찾아간다. 자식 -> 부모 역방향 전개

ORDER SIBLINGS : 계층형 쿼리에서 정렬을 수행한다.

 

 

ORACLE 예제

SELECT * FROM TEST START WITH id = 1 CONNECT BY PRIOR id = parent;

 

위 쿼리는 TEST 테이블 중, id가 1인 것을 최상위 행으로 지정하여, id가 parent로 설정되어 있는 컬럼을 따라 간다는 것.

즉, 최초에 id가 1인 행을 찾은 뒤, parent 컬럼이 id인 (1인) 컬럼을 찾는다.

 

 

 

MariaDB 방식으로 변환

WITH RECURSIVE TESTTABLE AS ( SELECT id, name, parent FROM TEST WHERE id=1)
UNION ALL SELECT c.id, c.name, c.parent FROM TEST c JOIN TESTTABLE cc ON c.parent = cc.id

 

 

쿼리가 길어졌다. 위 쿼리는 TEST 테이블에서 id가 1인 행을 기준으로 id가 parent와 동일한 쿼리를 찾는다.

 

 

ORACLE 에서 사용하는 START WITH 문을 Maria DB에서 WITH RECURSIVE 방식으로 바꿔 사용 시 문제점이 있다.

WITH RECURSIVE 방식으로 변경하면 계층 구조의 정렬이 안되어 직접 정렬 로직을 넣어줘야한다.

 

 

임의로 경로(path) 나 레벨(level) 값을 추가하여 ORDER BY 절에서 정렬해주어야한다.

path로 루트부터 자식까지의 경로를 문자열로 만들어 정렬 기준으로 사용, level로 현재 노드의 깊이를 숫자로 저장한다. 

 

 

WITH RECURSIVE TESTTABLE AS 
( 
	SELECT id, name, parent, CAST(id AS CHAR) AS path, 0 AS level
	FROM TEST WHERE id = 1
	UNION ALL 
	SELECT c.id, c.name, c.parent, CONCAT(ct.path, '-', c.id) AS path, ct.level + 1 AS level 
	FROM TEST c JOIN TEST ct ON c.parent = ct.id 
) 
SELECT * FROM TESTTABLE ORDER BY path;

 

 

 

계층 순서를 보장하고 싶다면 ORDER BY path, 레벨 기반으로 구성하고 싶다면 ORDER BY level, name 으로 활용 가능하다.