1. 특정 노드의 모든 후손 항목 조회 (자정 함수 사용) 기본 테이블 sys_center에서 centerid는 고유 식별자, parentcentid는 부모 항목을 가리키는 필드입니다. 예를 들어 입력값으로 1을 주면, 그 하위에 위치한 2, 3, 4, 5, 6와 같은 모든 자식 항목을 반환합니다.

DROP FUNCTION IF EXISTS getDescendantCenters;
DELIMITER $$

CREATE FUNCTION getDescendantCenters(node_id VARCHAR(31))
RETURNS VARCHAR(4000)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE result_ids VARCHAR(4000) DEFAULT '';
    DECLARE current_ids VARCHAR(4000) DEFAULT node_id;
    DECLARE temp_ids VARCHAR(4000);

    WHILE current_ids IS NOT NULL DO
        SELECT GROUP_CONCAT(a.centerid) INTO temp_ids
        FROM sys_center a
        WHERE FIND_IN_SET(a.parentcentid, current_ids) > 0;

        IF temp_ids IS NOT NULL THEN
            SET result_ids = CONCAT(result_ids, ',', temp_ids);
            SET current_ids = temp_ids;
        ELSE
            SET current_ids = NULL;
        END IF;
    END WHILE;

    -- 결과 앞부분의 쉼표 제거
    RETURN SUBSTRING(result_ids, 2);
END$$

DELIMITER ;

2. 후손 항목의 ID와 이름을 함께 조회하는 저장 프로시저 이 프로시저는 특정 중심 노드의 모든 하위 항목의 centerid와 centername을 함께 반환합니다. 이전 버전에서 이름 정보가 누락된 문제는 GROUP_CONCAT의 조건 처리에 기인했습니다. 아래 수정된 코드는 정확한 동작을 보장합니다.

DELIMITER $$

CREATE PROCEDURE GetChildrenWithNames(IN input_center_id VARCHAR(31))
BEGIN
    DECLARE all_ids VARCHAR(4000) DEFAULT '';
    DECLARE all_names VARCHAR(4000) DEFAULT '';
    DECLARE current_ids VARCHAR(4000) DEFAULT input_center_id;
    DECLARE child_ids VARCHAR(4000);
    DECLARE child_names VARCHAR(4000);

    WHILE current_ids IS NOT NULL DO
        SELECT GROUP_CONCAT(a.centerid), GROUP_CONCAT(a.centername)
        INTO child_ids, child_names
        FROM sys_center a
        WHERE FIND_IN_SET(a.parentcentid, current_ids) > 0;

        IF child_ids IS NOT NULL THEN
            SET all_ids = CONCAT(all_ids, ',', child_ids);
            SET all_names = CONCAT(all_names, ',', child_names);
            SET current_ids = child_ids;
        ELSE
            SET current_ids = NULL;
        END IF;
    END WHILE;

    -- 처음에 붙은 쉼표 제거
    SET all_ids = SUBSTRING(all_ids, 2);
    SET all_names = SUBSTRING(all_names, 2);

    -- 최종 결과 출력
    SELECT all_ids AS center_ids, all_names AS center_names;
END$$

DELIMITER ;

주의 사항 및 성능 최적화

• GROUP_CONCAT는 기본적으로 최대 길이가 1024바이트이며, 초과 시 데이터가 잘립니다.
• 대량 데이터 처리 시 group_concat_max_len 설정을 늘려야 합니다.
• 설정 방법:
• Windows: my.ini 파일에 추가: group_concat_max_len = 102400
• Linux: /etc/my.cnf 파일에 추가, 실행 시 관리자 권한 필요

실제 적용 시에는 서비스 재시작을 피하기 위해 SET GLOBAL group_concat_max_len = 102400; 방식으로 동적으로 설정하는 것이 좋습니다. GROUP_CONCAT 사용 시 LIMIT 문이 무시될 수 있으므로 주의해야 합니다.

결론적으로, 대규모 트리 구조 데이터를 효율적으로 탐색하려면 함수/프로시저 설계뿐 아니라 서버 설정도 적절히 조정해야 합니다.