Mysql recursive query (재귀쿼리)

---

1. 소개

• Mysql 은 오라클과 달리 재귀쿼리를 지원하는 것이 없다. 그렇기 때문에 function 이나 procedure 를 사용해야 한다.
• 아래 코드의 핵심은 임시테이블을 생성해서 값을 임시로 저장해서 활용하고 최종적으로 results 에 값을 쌓는 것을 목표로 한다.
• 임시테이블을 사용하기에 성능 이슈가 있기에 수차례 반복적으로 호출할 시에는 성능이슈를 고려해야한다.
• 임시테이블은 한 세션에서만 사용된다. 즉, 멀티쓰레드 환경에서 사용할 수 있다. (서로 다른 세션으로 접속한다면)

---

2. Code

2.1 Code Description

1. 입력받은 id 를 조건으로 parent_id 를 가져온다.
2. 가져온 parent_id 를 임시테이블에 저장
3. 임시테이블에 저장된 부모의 parent_id 를 가져옴.
4. 위 1~3번을 반복 (while 등 사용)
5. 아래 코드는 동작하지 않을 것이다. (전체적인 틀 이해를 위해 집어넣음)

2.2 Code Sample

drop procedure if exists getParents;

DELIMITER $$
CREATE PROCEDURE getParents (in_id varchar(100))
BEGIN
    DECLARE rowCount int;

    DROP TEMPORARY TABLE IF EXISTS results; 
    DROP TEMPORARY TABLE IF EXISTS temp2;
    DROP TEMPORARY TABLE IF EXISTS temp1;

    CREATE TEMPORARY TABLE results 
    (
        id varchar(100) NOT NULL,
        parent_id varchar(100) NOT NULL
    ); 

    CREATE TEMPORARY TABLE temp1 
    (
        id varchar(100) NOT NULL,
        parent_id varchar(100) NOT NULL
    );

    CREATE TEMPORARY TABLE temp2 
    (
        id varchar(100) NOT NULL,
        parent_id varchar(100) NOT NULL
    );

    # 입력받은 ID 의 부모 ID 를 temp1 에 저장
    INSERT INTO temp1
        select id, parent_id 
        from REL_TABLE
        WHERE  and id = (select parent_id from REL_TABLE where id = in_id);

    WHILE (select count(*) from temp1) DO

        # 여러 작업이 존재. 한 작업은 선행 작업이 ROOT 고, 다른 작업은 선행 작업이 더 있을 경우 처리
        select count(*) INTO rowCount from
        (
            select * from temp1 A
            where not exists 
            (
                select * 
                from REL_TABLE B 
                where A.parent_id = B.id
            ) 
        ) C ;

        if rowCount > 0 THEN
            insert into results (id, parent_id)
            select id, 'empty'
            from 
            (
                select * from temp1 A
                where not exists 
                (
                    select * 
                    from REL_TABLE B 
                    where A.parent_id = B.id
                )   
            ) C; 
        END IF;

        INSERT INTO temp2  
            select id, parent_id
            from REL_TABLE A
            where exists (select * from temp1 B where B.parent_id = A.id);

        select count(*) INTO rowCount from temp2;

        if rowCount = 0 THEN
            delete from temp1;
        ELSE
            insert into results 
            select id, parent_id
            from temp2;

            delete from temp1;

            INSERT INTO temp1
            select parent_id, id
            from temp2;

            delete from temp2;
        END IF;    
    END WHILE;


    SELECT * FROM results;


    DROP TEMPORARY TABLE IF EXISTS results;
    DROP TEMPORARY TABLE IF EXISTS temp1;
    DROP TEMPORARY TABLE IF EXISTS temp2;

END $$
DELIMITER ;

call getParents(id)