mysql 级联查询

 

1.  增加级联相关函数  
    

   delimiter /
   
   DROP FUNCTION IF EXISTS `bootzs`.`getChild`/
   
   CREATE FUNCTION `getChild`(rootId varchar(36))
   
     RETURNS VARCHAR(1000)
   
     BEGIN
   
       DECLARE ptemp VARCHAR(1000);
   
       DECLARE ctemp VARCHAR(1000);
   
       SET ptemp = '#';
   
       SET ctemp = rootId;
   
       WHILE ctemp IS NOT NULL DO
   
         SET ptemp = concat(ptemp, ',', ctemp);
   
         SELECT group_concat(menu_id)
   
         INTO ctemp
   
         FROM sys_menu
   
         WHERE FIND_IN_SET(parent_id, ctemp) > 0;
   
       END WHILE;
   
       RETURN ptemp;
   
     END;

2.  执行sql相关查询
    

   SELECT * FROM sys_menu WHERE FIND_IN_SET( menu_id ,getChild('250'));

3.  列表返回结果