mysql树形结构递归查询

oracle支持树形递归查询start with ... connect by ，但mysql不支持，不过可以通过创建函数来实现递归查询。

创建函数

DELIMITER //
DROP FUNCTION IF EXISTS f_get_suborg;
CREATE FUNCTION f_get_suborg(parentId VARCHAR(5000))
RETURNS VARCHAR(5000)
BEGIN
    DECLARE oTemp VARCHAR(5000);
    DECLARE oTempChild VARCHAR(5000);
    SET oTemp = '';
    SET oTempChild =parentId;
    WHILE oTempChild is not null DO
        IF oTemp != '' THEN
            SET oTemp = concat(oTemp,',',oTempChild);
        ELSE
            SET oTemp = oTempChild;
        END IF;
        SELECT group_concat(org_ID) INTO oTempChild FROM dbname.base_organizational where parentId<>org_ID and FIND_IN_SET(parent_id,oTempChild)>0;
    END WHILE;
RETURN oTemp;
END//
DELIMITER ;

--备注：表名和字段名记得替换

赋权：

GRANT EXECUTE ON FUNCTION `dbname`.f_get_suborg TO 'user_name'@'%';

执行：

SELECT * FROM dbname.base_organizational WHERE FIND_IN_SET(parent_id,dbname.f_get_suborg('1'));