通过父级id获取到其下所有子级（无穷级）——Mysql函数实现

【需求】某用户只能查看其自己信息及其下级信息，涉及通过该用户所在部门获取其下所有部门（多层）id集合。

步骤一：对数据库进行设置：

set global log_bin_trust_function_creators=TRUE;

　可以在Navicate点击工具栏的【工具】-> 【命令行界面】执行以上语句；也可以在服务器上进入数据库执行。

步骤二：执行以下sql语句，生成sql方法。

（1）需求一，获取父级及子级id：

CREATE FUNCTION `getChildList`(`rootId` VARCHAR(32)) RETURNS varchar(4000) CHARSET utf8
BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);
        DECLARE count INT DEFAULT 0;

    SET sTemp = '';-- 返回值
    SET sTempChd =cast(rootId as CHAR); -- 父id
        
        -- 判断父id是否存在
      SET count=(SELECT count(1) from sys_dept WHERE Id=rootId);
        IF count>0 THEN
            -- 遍历拼接所有子节点id
            WHILE sTempChd is not null DO
                SET sTemp = concat(sTemp,',',sTempChd);
                SELECT group_concat(Id) INTO sTempChd FROM sys_dept where FIND_IN_SET(Parent_Id,sTempChd)>0;
            END WHILE;
        -- 剔除拼接后多余的第一个逗号
        SET sTemp = (SELECT TRIM(LEADING ',' FROM sTemp));
        END IF;
    RETURN sTemp;
END

（2）需求二，仅获取所有子级id：

CREATE FUNCTION `getChildList`(`rootId` VARCHAR(32)) RETURNS varchar(4000) CHARSET utf8
BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);
        DECLARE count INT DEFAULT 0;

    SET sTemp = '';-- 返回值
    SET sTempChd =cast(rootId as CHAR); -- 父id
        
        -- 判断父id是否存在
      SET count=(SELECT count(1) from sys_dept WHERE Id=rootId);
        IF count>0 THEN
            -- 遍历拼接所有子节点id
            WHILE sTempChd is not null DO
                SET sTemp = concat(sTemp,',',sTempChd);
                SELECT group_concat(Id) INTO sTempChd FROM sys_dept where FIND_IN_SET(Parent_Id,sTempChd)>0;
            END WHILE;
        -- 剔除拼接后父级id及第一个多余的逗号
        SET sTemp = (select substring(sTemp, LENGTH(SUBSTRING_INDEX(sTemp,",",2))+2));
        END IF;
    RETURN sTemp;
END

测试：

解释：为什么需要步骤一的配置？

如果不配置的话，该函数在生成的时候会报错。

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)　

报错原因：

这是我们开启了bin-log, 我们就必须指定我们的函数是否是

1 DETERMINISTIC 不确定的

2 NO SQL 没有SQl语句，当然也不会修改数据

3 READS SQL DATA 只是读取数据，当然也不会修改数据

4 MODIFIES SQL DATA 要修改数据

5 CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。