mysql树形结构根据父ID查询所有子节点

如果创建函数报错，设置下属性 仅当前连接有效

set global log_bin_trust_function_creators=TRUE;

注意sTemp的长度，如果是数字VARCHAR(1000)够用，如果是uuid则弄成了大字段text 树形菜单不会太多记录

DROP FUNCTION IF EXISTS getMenuChildList;
CREATE FUNCTION `getMenuChildList`(rootId CHAR(32))
    RETURNS text
BEGIN
    DECLARE sTemp text;
    DECLARE sTempChd VARCHAR(1000);


    SET sTemp = '$';
    SET sTempChd =cast(rootId as CHAR);


    WHILE sTempChd is not null DO
    SET sTemp = concat(sTemp,',',sTempChd);
    SELECT group_concat(menu_code) INTO sTempChd FROM  sys_menu where FIND_IN_SET(parent_code,sTempChd)>0;
    END WHILE;
    RETURN sTemp;
END

select * from  sys_menu where FIND_IN_SET(menu_code ,getMenuChildList('0001222'));