재귀테이블의 개념
재귀테이블이란 한 테이블 안에 칼럼이 다른 칼럼을 참조하는 형식을 갖춘 테이블을 말한다. 대부분의 재귀테이블은 계층구조와 밀접한 연관이 있다. 부서 위에 상위부서가 존재하는 회사 조직도 관리에 굉장히 많이 쓰이는 테이블 구조며 생각보다 형식이 단순해 이해하기 어렵지 않다.
재귀테이블의 목적
계층을 이루는 엔티티(테이블)수가 너무 많아지거나 가변적인 상황일 때 재귀테이블의 진면목이 발휘된다. 다음과 같은 테이블 구조를 생각해보자.
한개의 테이블로 존재하는 직급과 달리 소속은 계급화/고도화가 되어있어서 테이블이 무려 세 개가 존재하게 된다. 이런 테이블 구조는 한눈에 알아보기는 쉬우나 다음과 같은 단점을 필연적으로 가지게 된다.
첫째. 계층 단계가 늘어나면 테이블을 또 추가해야 된다.
둘째. 조직이 개편되어 중간 계층이 없어진다면 FK설정으로 데이터 손실이 우려되며 데이터 관리가 모호해진다.
셋째. 확장성이 떨어진다.
테이블구조를 재귀화 시킨다면 위와 같은 문제점이 손쉽게 해소된다.
그림과 같이 상위부서 아이디가 아이디를 참조하고있다.
ERD만 보고 이해가 안 간다면 직접 데이터를 넣어보면 이해가 쉬워진다.
| 아이디 | 소속 명 | 상위 소속 아이디 |
|---|---|---|
| 1 | Intregato regist LABS | {null} |
| 2 | 1실 | 1 |
| 3 | 2실 | 1 |
| 5 | 제품 개발팀 | 2 |
| 6 | UX개발팀 | 2 |
| 7 | UI개발팀 | 2 |
| 8 | 유통개발팀 | 3 |
위와 같이 상위소속아이디를 통해 간결하게 계급화된 데이터를 관리 할 수 있게된다.
재귀 테이블 SELECT문
WITH RECURSIVE CTE(id, name, parent_id) AS(
SELECT id, name, parent_id
FROM teams
WHERE id=1
UNION ALL
SELECT t.id, t.name, t.parent_id
FROM teams t
INNER JOIN CTE c ON t.parent_id = c.id
)
SELECT id, name, parent_id FROM CTE;MYSQL8 버전 이후에는 WITH CTE구문을 통해 재귀명령을 수행할 수 있다.
첫 번째 select문에서 가장 상위계급에 속하는 id=1 의 컬럼이 선택되며
이는 CTE로 존재하게 된다.
두 번째 select문에서 id=1을 부모아이디로 가지고 있는 컬럼들이 선택된다.
(id = 2, 3)
재귀를 통해 다음 계급 컬럼들이 CTE가 되며 id = 2, 3의 컬럼들을 부모아이디로 가지고 있는 하위 컬럼들이 선택된다.
재귀 반복을 통해 마지막 데이터까지 추출이 가능하게 된다.
