BEGIN

DECLARE sTemp VARCHAR(1000);

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(ROLE_ID) INTO sTempChd FROM AUTH_ROLE where FIND_IN_SET(ROLE_PID,sTempChd)>0;

END WHILE;

SET sTemp = REPLACE(sTemp,concat(',',cast(rootId as CHAR)),'');

SET sTemp = REPLACE(sTemp,'$,','');

RETURN sTemp;

END

-- mysql递归查询，mysql中从子类ID查询所有父类（做无限分类经常用到）

SELECT T2.id, T2.channel_path 

FROM ( 

    SELECT 

        @r AS _id, 

        (SELECT @r := parent_id FROM cm_channel WHERE id = _id) AS parent_id, 

        @l := @l + 1 AS lvl 

    FROM 

        (SELECT @r := '2069ac487e2e49ddbdab78cba1e03828', @l := 0) vars, 

        cm_channel h 

    WHERE @r <> 0) T1 

JOIN cm_channel T2 

ON T1._id = T2.id 

ORDER BY T1.lvl DESC;