oracle中可以使用start with ...... connect by ......来进行递归检索查询。
在mysql中暂无相关函数，可以通过自定义函数方式来解决;
函数创建：
进入mysql command line client
mysql> delimiter $$mysql>mysql> create function `gettreenodes`(rootid int) -> returns varchar(1000) -> 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(table_pk) into stempchd from your_tree_table where find_in_set(pid,stempchd)>0; -> end while; -> return stemp; -> end -> $$query ok, 0 rows affected (0.00 sec)mysql>
函数调用： select * from your_tree_table where find_in_set(table_pk, gettreenodes(rootid));