[mariadb] 계층형 테이블 및 쿼리

오라클의 start with connect by 같은 구문이 없다. mariadb 10 버전 부터 with recursive 절이 가능한데

원하는 구조를 잘 못만들겠네 아.. 귀찮아 ㅋㅋ

일단 아래 원문에 있는게 제일 나아 보여서 네이밍 관련해서만 수정을 했다.

계층구조를 보여주려고 서브쿼리 와 함수내부에서 loop 를 돌게 되어있는데...

차라리 Category 테이블에 컬럼을 하나 추가하는게 더 낫지않을까 고민해본다.

mysql 답변형 게시판 테이블 구조처럼 관련글들에 대한 ref 컬럼을 만들면

계층구조 결과를 가져오기에는 복잡한 쿼리가 필요하지 않을것 같다.

지금은 mariadb 가 계속 버전업 되면서 CTE(Common Table Expression) 가 많이 적용되는듯 하다.

오라클, PostgreSQL, MSSQL 처럼 계층형 테이블을 쉽게 표현할 수 있는때가 되면 다시 고민해보자.;;

원문 - https://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

1. 카테고리 테이블 생성

DROP TABLE IF EXISTS `Category` ;

CREATE TABLE IF NOT EXISTS `Category` (
  `idx` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '고유값',
  `parentIdx` INT UNSIGNED NOT NULL COMMENT '부모고유값',
  `title` VARCHAR(100) NOT NULL COMMENT '제목',
  PRIMARY KEY (`idx`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = '카테고리 테이블';

2. 데이터 입력

insert into Category set parentIdx=0, title='root';
insert into Category set parentIdx=1, title='음악';
insert into Category set parentIdx=2, title='발라드';
insert into Category set parentIdx=2, title='댄스';
insert into Category set parentIdx=2, title='랩/힙합';
insert into Category set parentIdx=2, title='R&B/소울';
insert into Category set parentIdx=2, title='인디음악';
insert into Category set parentIdx=2, title='록/메탈';
insert into Category set parentIdx=2, title='트로트';
insert into Category set parentIdx=2, title='포크/블루스';
insert into Category set parentIdx=1, title='미디어';

3. 계층형 쿼리 함수 생성

DELIMITER //
CREATE FUNCTION hierarchy_connect_by_parentIdx_eq_prior_idx(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _idx INT;
        DECLARE _parentIdx INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @idx = NULL;

        SET _parentIdx = @idx;
        SET _idx = -1;

        IF @idx IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(idx)
                INTO    @idx
                FROM    Category
                WHERE   parentIdx = _parentIdx
                        AND idx > _idx;
                IF @idx IS NOT NULL OR _parentIdx = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @idx;
                END IF;
                SET @level := @level - 1;
                SELECT  idx, parentIdx
                INTO    _idx, _parentIdx
                FROM    Category
                WHERE   idx = _parentIdx;
        END LOOP;       
END
//
DELIMITER ;

4. 쿼리 실행

SELECT  CONCAT(REPEAT('    ', level - 1), hi.title) AS treeitem, parentIdx, level
FROM    (
        SELECT  hierarchy_connect_by_parentIdx_eq_prior_idx(idx) AS idx, @level AS level
        FROM    (
                SELECT  @start_with := 0,
                        @idx := @start_with,
                        @level := 0
                ) vars, Category
        WHERE   @idx IS NOT NULL
        ) ho
JOIN    Category hi
ON      hi.idx = ho.idx

5. 쿼리 결과

+--------------------------+-----------+-------+
| treeitem                 | parentIdx | level |
+--------------------------+-----------+-------+
| root                     |         0 |     1 |
|     음악                 |         1 |     2 |
|         발라드           |         2 |     3 |
|         댄스             |         2 |     3 |
|         랩/힙합          |         2 |     3 |
|         R&B/소울         |         2 |     3 |
|         인디음악         |         2 |     3 |
|         록/메탈          |         2 |     3 |
|         트로트           |         2 |     3 |
|         포크/블루스      |         2 |     3 |
|     미디어               |         1 |     2 |
+--------------------------+-----------+-------+