mysql connect by prior的实现方式

项目背景

在去O的路上，总会遇到点困难，这不mysql5.7不支持oracle中的 connect by prior，需要单独写函数或存储过程来实现，网上有很多相关资料，这里了单独记录一下。

数据库版本

mysql5.7.29

测试表

CREATE TABLE treenodes ( id int(11) NOT NULL, nodename varchar(20) DEFAULT NULL, pid int(11) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB

生成测试数据

create function gen_data(start_num int,end_num int) returns int begin DECLARE v_num int; while start_num<= end_num do insert into treenodes values(start_num,concat(‘aa’,start_num),round(rand()*start_num)); set start_num=start_num+1; end while;

return 1;

end

批量生成1万条数据 select gen_data(1,10000)

实现方式

create function getChildList(rootid int) returns varchar(1000) begin DECLARE v_child_list varchar(10000); DECLARE v_child varchar(1000); set v_child_list='$'; set v_child=cast(rootid as char); while v_child is not null do set v_child_list=concat(v_child_list,',',v_child); select group_concat(id) into v_child from treenodes where find_in_set(pid,v_child)>0; end while; return v_child_list; end

测试1 select getChildList(1125) $,1125,1805,5770

select * from treenodes where find_in_Set(pid, '$,1125,1805,5770')

测试1分两次执行，1秒以内就出结果

测试2 select * from treenodes where find_in_Set(pid, getChildList(1125))

测试2长达100秒不出结果

总结

1、通过函数实现时，getChildList返回值超限制会被截断，即返回的子节点数据超了变量定义的长度

select getChildList(5) >Data truncated for column 'getChildList(5)' at row 1

2、通过 find_in_Set(pid, getChildList(1125))这方式咱获取父节点或子节点的信息，数据量大了过后，长时间不出结果（查询时间超100秒）

3、mysql里没有postgresql generate_series自动生成序列函数，每次想批量生成测试数据时，都得专门去写个函数，有点麻烦。