mysql树形结构查询

一、查询下级渠道user_id

CREATE DEFINER=`root`@`%` FUNCTION `getChildList`(`rootId` int) RETURNS mediumtext CHARSET utf8
BEGIN 
        DECLARE sTemp mediumtext; 
        DECLARE sTempChd mediumtext; 
 
        SET sTemp = '^'; 
        SET sTempChd =cast(rootId as CHAR); 
 
        WHILE sTempChd is not null DO 
            SET sTemp = concat(sTemp,',',sTempChd); 
            SELECT group_concat(id) INTO sTempChd FROM c_users where FIND_IN_SET(ownerid,sTempChd)>0; 
        END WHILE; 
        RETURN sTemp; 
END

二、查询上级渠道user_id，此处上级根节点id为1，在循环条件中根据实际情况修改

CREATE DEFINER=`root`@`%` FUNCTION `getParentList`(`rootId` int) RETURNS mediumtext CHARSET utf8
BEGIN 
        DECLARE sTemp mediumtext; 
        DECLARE sTempChd mediumtext; 
 
        SET sTemp = '^'; 
        SET sTempChd =cast(rootId as CHAR); 
 
        WHILE sTempChd != 1 DO 
            SET sTemp = concat(sTemp,',',sTempChd); 
            SELECT ownerid INTO sTempChd FROM c_users where id=sTempChd; 
        END WHILE; 
        RETURN sTemp; 
END

三、在php中如何调用定义好的数据库函数

/**
     * 根据传入根渠道id查询所有子渠道id
     * @param int $rootId 根渠道ID,非必填,空为当前登录账号的用户id
     * @return String 子渠道逗号分隔
     */
    function getChildList($rootId = null){
        if(empty($rootId)){
            $rootId = sp_get_current_admin_id();  //当前登录账号的用户id
        }
        $Model = new \Think\Model();
        $result = $Model->query("select getChildList($rootId) AS 'id'");
        $resultStr = substr($result[0]['id'],2);
        return $resultStr;
    }

查询子渠道方法过于频繁可以添加一个session缓存降低数据库的请求时间，也可以使用缓存数据库来做缓存

/**
     * 根据传入渠道id获取子渠道id,Session缓存
     * @param int $agent_id 根渠道id
     * @return String 子渠道id逗号拼接
     */
    function getChildStrCache($agent_id) {
        if (empty($_SESSION['agent'][$agent_id])) {
            $agentarr = $this->getChildList($agent_id);
            $_SESSION['agent'][$agent_id] = $agentarr;
            return $agentarr;
        }
        return $_SESSION['agent'][$agent_id];
    }