Oracle和Mysql遞迴

oracle對於樹形查詢可以使用start with ... connect by

select * from menu start with id='130000' connect by id = prior parent_id;

mysql隨沒有自帶的語法支援，不過可以通過建立函式來實現遞迴查詢

SELECT id AS ID,pid AS 父ID ,levels AS 父到子之間級數, paths AS 父到子路徑 FROM (
     SELECT id,pid,
     @le:= IF (pid = 0 ,0,  
         IF( LOCATE( CONCAT('|',pid,':'),@pathlevel)   > 0  ,      
                  SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1
        ,@le+1) ) levels
     , @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel
      , @pathnodes:= IF( pid =0,',0', 
           CONCAT_WS(',',
           IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0  , 
               SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1)
              ,@pathnodes ) ,pid  ) )paths
    ,@pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall 
        FROM  treenodes, 
    (SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv
    ORDER BY  pid,id
    ) src
ORDER BY id

參考：

http://www.cnblogs.com/dukou/p/4691543.html

https://blog.csdn.net/guiguren/article/details/77574326