1
/ | \
2 3 4
/ | \ \ |
5 6 7 8 9 mysql 或java 实现1. 递归深度查询(向下查询) 只查询想要的子节点数。不如3层。5层,10层2。 递归深度(向上查询) 比如:9的父节点有哪些 只要上面3个,或者5个。
/ | \
2 3 4
/ | \ \ |
5 6 7 8 9 mysql 或java 实现1. 递归深度查询(向下查询) 只查询想要的子节点数。不如3层。5层,10层2。 递归深度(向上查询) 比如:9的父节点有哪些 只要上面3个,或者5个。
0 book1 null 1
2 book1 2 1
3 book1 2 2
MySQL中进行树状所有子节点的查询
CREATE FUNCTION `getChildLst`(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(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
执行后:
错误SQL 查询:CREATE FUNCTION `getChildLst` (
rootId INT
) RETURNS varchar( 1000 ) BEGIN DECLARE sTemp VARCHAR( 1000 ) ;MySQL 返回:文档
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
mysql> delimiter //
mysql>
mysql> CREATE FUNCTION `getChildLst`(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(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;
-> END WHILE;
-> RETURN sTemp;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter ;===============================================================
delimiter //
我设置为十报这个错
Recursive limit 10 (as set by the max_sp_recursion_depth variable) was exceeded for routine createChildLst