无限级的过程
上下级会员关系通过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)
BEGIN
SET @pid:=id;#id为查询的参数 即当前用户id
SET @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存储过程
BEGIN
SET @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也可以得到解决。