祖先树统计

DDL

CREATE TABLE organization_ancestor_id_tree (
    id               BIGINT       NOT NULL COMMENT '对应：smarthse_supervise.organization.id' PRIMARY KEY,
    ancestor_id_tree VARCHAR(100) NULL COMMENT 'id的祖先id树（最近祖先在最左，最远祖先在最右）'
) COMMENT '存放每一个：smarthse_supervise.organization.id，的祖先id树，每日03:00更新。';

PROCEDURE

DELIMITER $$
CREATE PROCEDURE generate_all_organization_ancestor_id_tree()
BEGIN
    /* 定义变量用于遍历所有监管ID */
    DECLARE done BIGINT DEFAULT FALSE;
    DECLARE sid BIGINT;
    DECLARE current_id BIGINT;
    DECLARE prev_id BIGINT;
    DECLARE cur CURSOR FOR SELECT id FROM organization;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    /* 重复执行此存储过程等价于：以最新的数据重新追溯监管id的祖先id树。 */
    TRUNCATE organization_ancestor_id_tree;
    /* 创建一个临时表来存储祖先ID */
    CREATE TEMPORARY TABLE temp_organization_ancestor_id_list (
        id BIGINT
    );
    /* 打开游标 */
    OPEN cur;
    /* 遍历游标 */
    read_loop:
    LOOP
        FETCH cur INTO sid;
        IF done THEN LEAVE read_loop; END IF;
        SET current_id = sid;
        SET prev_id = -1;
        /* 循环检索，获取每个ID的所有祖先 */
        WHILE current_id IS NOT NULL AND current_id != prev_id
            DO
                INSERT INTO temp_organization_ancestor_id_list (id) VALUES (current_id);
                SET prev_id = current_id;
                SELECT pid
                INTO current_id
                FROM organization
                WHERE id = current_id AND pid IS NOT NULL AND pid != 999999999999;
            END WHILE;
        /* 输出所有祖先ID，以逗号分隔 */
        INSERT INTO organization_ancestor_id_tree
        SELECT sid, GROUP_CONCAT(id ORDER BY id DESC SEPARATOR ',') AS ancestor
        FROM temp_organization_ancestor_id_list;
        TRUNCATE temp_organization_ancestor_id_list;
        /* 重置done值 */
        SET done = FALSE;
    END LOOP;
    /* 关闭游标 */
    CLOSE cur;
    /* 删除临时表 */
    DROP TEMPORARY TABLE IF EXISTS temp_organization_ancestor_id_list;
END$$
DELIMITER ;

CALL

CALL generate_all_organization_ancestor_id_tree();