查子集合
--drop FUNCTION `getChildList`
CREATE FUNCTION `getChildList`(rootId varchar())
RETURNS varchar()
BEGIN
DECLARE str varchar();
DECLARE cid varchar();
SET str = '$';
SET cid = rootId;
WHILE cid is not null DO
SET str = concat(str, ',', cid);
SELECT group_concat(id) INTO cid FROM treeNodes where FIND_IN_SET(parentid, cid) > ;
END WHILE;
RETURN str;
END
select getParentList('001001001');
select * from sbkfwh where FIND_IN_SET(id,getChildList('001001001'))
对上面的改进:
set global log_bin_trust_function_creators = 1;
CREATE FUNCTION `getChildList`(rootId varchar(100),which_level INTEGER)
RETURNS varchar(2000)
BEGIN
DECLARE str varchar(2000); 这里可能容纳不下,可以打点。
DECLARE c