分析mysql的递归查询子节点函数

一般大家都喜欢在mysql中写递归函数查询子节点如下:

 

CREATE DEFINER = `mysql_xxx`@`%` FUNCTION `NewProc`(pid VARCHAR(40), holdPid INT)
 RETURNS varchar(20000)
BEGIN
    #根据指定ID获取下级模块所有节点信息, pid 指定ID， holdPid是否保留父节点ID， 0 不保留 1 保留
    DECLARE stemp VARCHAR(20000) DEFAULT '';
    DECLARE stempchd VARCHAR(20000) DEFAULT pid;
    DECLARE icount INT DEFAULT 0;
		SET SESSION group_concat_max_len = 4294967295;
    WHILE stempchd IS NOT NULL DO
        IF icount > 0 THEN
                SET stemp = CONCAT(stemp,',');
        END IF;
        SET stemp = CONCAT(stemp, stempchd);
        SELECT GROUP_CONCAT(module_id) INTO stempchd FROM xxx_module WHERE FIND_IN_SET(parent_module_id, stempchd) > 0;
        SET icount = icount + 1;
    END WHILE;

    IF holdPid = 0 THEN
      SET stemp = SUBSTR(stemp, LENGTH(pid)+2);
    END IF;
    RETURN stemp;
    END;

分析该段代码,有这几个知识点,CONCAT函数, GROUP_CONCAT函数, SUBSTR函数,FIND_IN_SET函数

 

CONCAT相当于java中的stringBuffer一样追加字符串,如CONCAT('aa','bb') = 'aabb'

GROUP_CONCAT就是当查出的结果集按照逗号隔开的一个函数 = 'aa,bb,cc'

它的坑就是默认长度是1024如果长度过长需要设置他的值 : SET SESSION group_concat_max_len = 4294967295; or  SET GLOBAL group_concat_max_len = 4294967295;

在不同操作系统上它的值都不一样,这里就用32位的4294967295长度就够用了

32 位	4	4294967295
64 位	4	18446744073709551615

SUBSTR函数又有坑了,语法为SUBSTR(string, start, length),它的其实位置start不是从0开始的,是从1开始的。

FIND_IN_SET函数类似于java中的contains,函数语法为: 例如 FIND_IN_SET('aaa','aaa,ccc') 返回值如果存在的话则大于0,它是以逗号隔开的然后完全匹配不是like一样模糊匹配。

有了这些函数知识点的脑补,我们就能把这段函数翻译为java类似于以下的代码:

 

String pid = "-1";
        String stemp = "";
        String stempchd = pid;
        int icount = 0;
        while (stempchd != null && stempchd.length() > 0) {
            if (icount > 0) {
                stemp = stemp + ",";
            }
            stemp = stemp + stempchd;
            // SELECT GROUP_CONCAT(module_id) INTO stempchd FROM cap_bm_module WHERE FIND_IN_SET(parent_module_id,stempchd) > 0;
            // 通过FIND_IN_SET和GROUP_CONCAT函数查出当前数据集中谁的parent_module_id是stempchd,
            // 然后查出子节点结果集平铺为一行.也就是查出所有下一级子节点,不断循环递归往下查只能没有为止.
            stempchd = "";// 调用mysql函数
            icount = icount + 1;
        }
        // 根据标识来排除第一个父节点的逻辑省略
        return stemp;