一、查詢下級渠道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，此處上級根節(jié)點id為1，在循環(huán)條件中根據(jù)實際情況修改

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中如何調(diào)用定義好的數(shù)據(jù)庫函數(shù)

/**
     * 根據(jù)傳入根渠道id查詢所有子渠道id
     * @param int $rootId 根渠道ID,非必填,空為當(dāng)前登錄賬號的用戶id
     * @return String 子渠道逗號分隔
     */
    function getChildList($rootId = null){
        if(empty($rootId)){
            $rootId = sp_get_current_admin_id();  //當(dāng)前登錄賬號的用戶id
        }
        $Model = new \Think\Model();
        $result = $Model->query("select getChildList($rootId) AS 'id'");
        $resultStr = substr($result[0]['id'],2);
        return $resultStr;
    }

查詢子渠道方法過于頻繁可以添加一個session緩存降低數(shù)據(jù)庫的請求時間，也可以使用緩存數(shù)據(jù)庫來做緩存

/**
     * 根據(jù)傳入渠道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];
    }