mysql 快速构建树形测试数据

create table t_org_tree (org_id int  , parent_id int , memo varchar(64),PRIMARY KEY (`org_id`) USING BTREE,KEY `indx_org_tree_parent_id` (`parent_id`) USING BTREE);


select * from t_org_tree;



CREATE PROCEDURE InsertDataToOrgTree0(parent int)
BEGIN
  DECLARE counter INT DEFAULT parent*10;
  DECLARE maxChild INT DEFAULT parent*10+9;
  
  WHILE counter <= maxChild and  counter < 100000 DO
    SET @memo := uuid();
    INSERT INTO t_org_tree (org_id, parent_id, memo) VALUES (counter, parent, @memo);  
    COMMIT;
    call InsertDataToOrgTree0(counter);
    SET counter = counter + 1;
  END WHILE;
end;


CREATE PROCEDURE InsertDataToOrgTree()
BEGIN
  DECLARE counter INT DEFAULT 1;
  WHILE counter <= 9 DO
    SET @parent_id := 0 ; -- 随机选择一个parent_id
    SET @memo := uuid();
    INSERT INTO t_org_tree (org_id, parent_id, memo) VALUES (counter, @parent_id , @memo);  
    COMMIT;
    call InsertDataToOrgTree0(counter);
    SET counter = counter + 1;
  END WHILE;
end;

SET max_sp_recursion_depth = 1000;

delete from t_org_tree where 1=1 ;

-- 大约需要 700 秒
CALL InsertDataToOrgTree();


select * from t_org_tree ;

-- 直接查询

with recursive rs as (select * from t_org_tree where ORG_ID in(2 , 80 ,1000 ,399)
union  all 
select t.* from t_org_tree t , rs where rs.ORG_ID = t.parent_id
)	
select *  from rs limit 200;


with recursive rs as (select * from t_org_tree where ORG_ID in(2 , 80 ,1000 ,399)
union  all 
select t.* from t_org_tree t , rs where t.ORG_ID = rs.parent_id
)	
select * from rs ;