MySQL 实现递归函数查询

利用find_in_set()和group_concat()实现向下递归查询

CREATE FUNCTION queryChildrenMenuInfo(menuId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(menuId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_menus WHERE FIND_IN_SET(parentId,sTempChd)>0;
END WHILE;
RETURN sTemp;
END

获取所有id为1的自身和下级:

SELECT queryChildrenMenuInfo(1);


利用find_in_set()和group_concat()实现向上递归查询

CREATE FUNCTION queryChildrenMenuInfo1(menuId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(16000);
DECLARE sTempChd VARCHAR(16000);

SET sTemp='$';
SET sTempChd = CAST(menuId AS CHAR);
SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT parentId INTO sTempChd FROM t_menus WHERE id = sTempChd;
WHILE sTempChd <> 0 DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT parentId INTO sTempChd FROM t_menus WHERE id = sTempChd;
END WHILE;
RETURN sTemp;
END

获取所有id为10的自身和上级:

SELECT queryChildrenMenuInfo1(10);

转载请说明出处:第六感博客 原文链接:

标签: MySql

相关阅读:

mysql 8 修改root密码

mysql 为了导出Excel后不显示科学计数 数据列前加单引号 Excel中会显示成字符串