无限级的过程

上下级会员关系通过id和pid确定。

例如：a>b>c>d>e>f>g...

就是a的下级是b，b的下级是c，c的下级是d....(有点类似传*销模式？无视掉)

需求，通过用户g查出所有的上级，则要查到f.e.d.c.b.a这些用户id

方可给这些上级发送通知。

我们最重要的是取出上级id然后就好办多了。

首先说明几个值，user_id 为用户表中的用户id，parent_id为表中用户的上级id。

示例一条基本的查询父级idSQL。

SELECT parent_id FROM cx_user WHERE user_id = 10;##查询user_id为10的上级id

不说废话了，直接进入中心。

打开MySQL开始写存储过程。

DROP PROCEDURE IF EXISTS `getpid`;CREATE DEFINER = `root`@`localhost` PROCEDURE `getpid`(IN `id` int)BEGINSET @pid:=id;#id为查询的参数 即当前用户idSET @pidarr:=','; #查询到的上级数组字符串recursion:WHILE @pid!=0 DO  SELECT parent_id INTO @pid from cx_users WHERE user_id = @pid;#这里是你查询的SQL语句 @pid是动态id      IF @pid=0 THEN #到0即结束         LEAVE recursion;      END IF;      set @substr = concat(",",@pid);      set @substr = concat(@substr,',');      IF LOCATE(@substr ,@pidarr)>0 THEN #防止出现上下级关系混乱         LEAVE recursion;      END IF;  set @pidarr = concat(@pidarr,@pid);  set @pidarr = concat(@pidarr,',');	END WHILE recursion;SELECT @pidarr AS pidarr;END;

存储过程执行结果，拿图吓唬吓唬你。

PHP端demo

$sql = "CALL getpid(104);";$pidstr = mysql_query($sql);//换成你项目中执行MySQL的函数即可 然后取出 pidarr的值到$pidstr$pidstr = rtrim($pidstr,',');//剔除最右侧多余英文逗号$pidstr = ltrim($pidstr,',');//剔除最左侧多余英文逗号$pidarr = explode(',',$pidstr);//到数组//以下随意 #ncSJI$@CO3N#@329090*(@@()$u)@j(@(h(@jn9NCD9823N892

#################### 增加指定层级查询

首先增加一个字符串查找函数

DROP FUNCTION IF EXISTS `substr_count`;CREATE DEFINER = `root`@`%` FUNCTION `substr_count`(`pstring` varchar(255),`substring` varchar(255)) RETURNS int(11)BEGIN SELECT  (LENGTH(pstring) - LENGTH(REPLACE(pstring,substring, ""))) / LENGTH(substring) AS "count" into @ret ;	RETURN @ret;END;

修改之前的getpid存储过程

BEGINSET @pid:=id;SET @pidarr:=',';/* this return pid */recursion:WHILE @pid!=0 DO  SELECT parent_id INTO @pid from cx_users WHERE user_id = @pid;			IF @pid=0 THEN #到0即结束				LEAVE recursion;			END IF;				set @substr = concat(",",@pid);				set @substr = concat(@substr,',');			IF LOCATE(@substr,@pidarr)>0 THEN #防止出现上下级关系混乱         LEAVE recursion;      END IF;  set @pidarr = concat(@pidarr,@pid);  set @pidarr = concat(@pidarr,',');	#LEVEL 增加的层级判断	IF substr_count(@pidarr,',')>=level+1 THEN            set @pid=0;            LEAVE recursion;	END IF;END WHILE recursion;SELECT @pidarr AS pidarr;END

调用SQL

CALL getpid(104,2);//获取用户id为104的上级 层级2级  即获取两层上级

补充：

在某些MySQL版本中，在某些表的设计中，使用如上过程，出现类似以下报错。

Result consisted of more than one row

面对这个可以增加一个临时交换ID或者是将SQL语句更改下，防止出现返回多个结果行。

SELECT parent_id INTO @pid from cx_users WHERE user_id = @pid LIMIT 1;

今天群遇到一个网友，使用以上过程，就是查询不出来数据，结果是NULL，研究很久才发现他的表项设计中出现允许为NULL的字段，在过程执行中，直接跳过pid=0的判断，进入concat(",",@pid);的语句，而此时的@pid 是null ，致使合并后的字符串也出现了NULL，更影响了结果@pidarr的数据。

在此，一般只需要将这种关系表设计的更标准些，如NOT NULL DEFAULT '0',或者在过程中增加额外的判断NULL、''(EmptyString)的条件让他跳出WHILE也可以得到解决。