MariaDB에서 계층 구조 데이터 조회하기

 

 

MariaDB에서 계층 구조 형태로 쿼리를 생성하는 방법은 2가지로 구분할 수 있다.

1. "커넥트 바이너리 테이블" 방식
2. "공통 테이블 식(CTE)" 방식

커넥트 바이너리 테이블을 사용하는 방법

커넥트 엔진은 MariaDB에서 계층 구조 데이터를 처리하기 위한 기능을 제공한다.

커넥트 엔진이 설치되어 있어야 한다

-- 커넥트 엔진 로드
INSTALL SONAME 'ha_connect';

-- 계층 구조 데이터를 담고 있는 테이블 생성
CREATE TABLE hierarchy_table (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50)
) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='/path/to/hierarchy_data.csv';

-- 계층 구조 쿼리
SELECT * FROM hierarchy_table 
WHERE CONNECT_BY_ROOT(id) = id
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

 

공통 테이블 식(CTE) 

MariaDB 10.2 이상에서 공통 테이블 식(CTE)을 활용하여 계층 구조 데이터를 쿼리할 수 있다.

-- 계층 구조 데이터를 담고 있는 테이블 생성
CREATE TABLE hierarchy_table (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50)
);

-- 계층 구조 쿼리
WITH RECURSIVE cte (id, parent_id, name, level) AS (
    SELECT id, parent_id, name, 1 as level FROM hierarchy_table WHERE parent_id IS NULL
    UNION ALL
    SELECT ht.id, ht.parent_id, ht.name, cte.level + 1
    FROM hierarchy_table ht
    INNER JOIN cte ON ht.parent_id = cte.id
)
SELECT id, parent_id, name FROM cte;

 

활용) CTE 방식으로 name 앞에 레벨별 공백을 주는 쿼리

-- 계층 구조 데이터를 담고 있는 테이블 생성
CREATE TABLE hierarchy_table (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50)
);

-- 계층 구조 쿼리 (공백 추가)
WITH RECURSIVE cte (id, parent_id, name, level) AS (
    SELECT id, parent_id, name, 1 as level, CAST(name AS CHAR(200)) as formatted_name 
    FROM hierarchy_table WHERE parent_id IS NULL
    UNION ALL
    SELECT ht.id, ht.parent_id, ht.name, cte.level + 1, CONCAT(REPEAT(' ', (cte.level + 1) * 2), ht.name)
    FROM hierarchy_table ht
    INNER JOIN cte ON ht.parent_id = cte.id
)
SELECT id, parent_id, formatted_name as name FROM cte;