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));