MySql递归查询

利用find_in_set()函数和group_concat()函数实现递归查询：

1、向下递归：

查询本部门及其所有子部门
调用方式：

SELECT dept_id
        FROM sys_dept
        WHERE FIND_IN_SET(dept_id, queryChildDeptInfo((select t.dept_id from sys_user t where t.user_id = #{user.userId})))

实例：

DROP FUNCTION IF EXISTS queryChildDeptInfo;
CREATE FUNCTION `queryChildDeptInfo`(areaId INT) RETURNS varchar(4000) CHARSET utf8mb4
BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);

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

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

2、向上递归：

查询本部门及其所有父级部门

SELECT dept_id
        FROM sys_dept
        WHERE FIND_IN_SET(dept_id, querySubDeptInfo((select t.dept_id from sys_user t where t.user_id = #{user.userId})))

实例：

CREATE  FUNCTION `querySubDeptInfo`(areaId INT) RETURNS varchar(4000) CHARSET utf8mb4
BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);

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

		SET sTemp = CONCAT(sTemp,',',sTempChd);
		SELECT parent_id INTO sTempChd FROM sys_dept WHERE dept_id = sTempChd;
		WHILE sTempChd <> 0 DO
		SET sTemp = CONCAT(sTemp,',',sTempChd);
		SELECT parent_id INTO sTempChd FROM sys_dept WHERE dept_id = sTempChd;
		END WHILE;

    RETURN sTemp;
END