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自动生成序列函数，每次想批量生成测试数据时，都得专门去写个函数，有点麻烦。