当前位置: 澳门新濠3559 > 数据库 > 正文

mysql难于在树状表中层层遍历的子节点,varchar(

时间:2019-10-12 05:02来源:数据库
数据库表简介:货物表 简单介绍:mysql5.0.94版本,该版本以致较高等的版本(5.5、6之类)尚未协助循环递归查询,和sqlserver、oracle比较,mysql难于在树状表中难得遍历的子节点。本程序

数据库表简介:货物表

简单介绍:mysql5.0.94版本,该版本以致较高等的版本(5.5、6之类)尚未协助循环递归查询,和sqlserver、oracle比较,mysql难于在树状表中难得遍历的子节点。本程序注重参照他事他说加以考察了上面包车型客车素材,写了五个sql存储进程,子节点查询算是照搬了,父节点查询是逆思维弄的。

--表结构

澳门新濠3559 1

表结构和表数据就不公示了,查询的表user_role,主键是id,每条记下有parentid字段(对应该记录的父节点,当然,多少个父节点自然会有二个之上的子节点嘛)

CREATE TABLE `address` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code_value` varchar(32) DEFAULT NULL COMMENT '区域编码', `name` varchar(128) DEFAULT NULL COMMENT '区域名称', `remark` varchar(128) DEFAULT NULL COMMENT '说明', `pid` varchar(32) DEFAULT NULL COMMENT 'pid是code_value', PRIMARY KEY (`id`), KEY `ix_name` (`name`,`code_value`,`pid`) ) ENGINE=InnoDB AUTO_INCREMENT=1033 DEFAULT CHATucsonSET=utf8 COMMENT='行政区域表';

`id` int(11)    '货物id,独一标记',
`name` varchar(255)   '物品名称',
`level` int(11)   '物品连串品级,礼品包为最高端1,种类为2级,详细物品为3级',
`parentId` int(11)  '唯有3级详细物品有上边id',
`childIds` varchar(255)    '独有1级礼品包级有隐含全数3级货物id的字符串,id之间用","隔开分离',

复制代码 代码如下:

 

标题叙述:利用sql语句简化代码,让Java代码能够直接通过jdbc查询获得下述集结

CREATE FUNCTION `getChildList`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sChildList VARCHAR(1000);
DECLARE sChildTemp VARCHAR(1000);
SET sChildTemp =cast(rootId as CHAR);
WHILE sChildTemp is not null DO
IF (sChildList is not null) THEN
SET sChildList = concat(sChildList,',',sChildTemp);
ELSE
SET sChildList = concat(sChildTemp);
END IF;
SELECT group_concat(id) INTO sChildTemp FROM user_role where FIND_IN_SET(parentid,sChildTemp)>0;
END WHILE;
RETURN sChildList;
END;
/*获得子节点*/
/*调用: 1、select getChildList(0) id; 2、select * 5From user_role where FIND_IN_SET(id, getChildList(2));*/

--mysql 达成树结构查询 --方法一

  1、根据输入的1级的id查询其蕴涵的全体3级货品音讯

CREATE FUNCTION `getParentList`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sParentList varchar(1000);
DECLARE sParentTemp varchar(1000);
mysql难于在树状表中层层遍历的子节点,varchar(255)   '物品名称'。SET sParentTemp =cast(rootId as CHAR);
WHILE sParentTemp is not null DO
IF (sParentList is not null) THEN
SET sParentList = concat(sParentTemp,',',sParentList);
ELSE
SET sParentList = concat(sParentTemp);
END IF;
SELECT group_concat(parentid) INTO sParentTemp FROM user_role where FIND_IN_SET(id,sParentTemp)>0;
END WHILE;
RETURN sParentList;
END;
/*获取父节点*/
/*调用: 1、select getParentList(6) id; 2、select * From user_role where FIND_IN_SET(id, getParentList(2));*/

CREATE PROCEDURE sp_showChildLst(IN rootId varchar(20)) BEGIN CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst (sno int primary key auto_increment,code_value VARCHAR(20),depth int); DELETE FROM tmpLst; CALL sp_createChildLst(rootId,0); select tmpLst.*,address.* from tmpLst,address where tmpLst.code_value=address.code_value order by tmpLst.sno; END

  2、依照输入的1级的id查询其含有的持有3级货品对应的2级货物种类消息

弄完了,pm说不用弄存款和储蓄结构,在java里边多查几回啊。。。存款和储蓄结构有许多亮点,包涵加速查询速度、提升安全性等等,然而会加大数据库负荷,相当多篇章提出结合使用,个人也感觉少用点会好些。

 

>>>>>>>>>>>>>>>>>>>>>>>>solution>>>>>>>>>>>>>>>>>>>>>>>>>>>

你也许感兴趣的稿子:

  • MySQL达成树状全体子节点查询的方法
  • Java递归遍历树形结构
  • 应用递归删除树形结构的全体子节点(java和mysql完毕)

CREATE PROCEDURE sp_createChildLst(IN rootId varchar(20),IN nDepth INT) BEGIN DECLARE done INT DEFAULT 0; DECLARE b VARCHAR(20); DECLARE cur1 CURSOR FOR SELECT code_value FROM address where pid=rootId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; insert into tmpLst values (null,rootId,nDepth);

标题1:因为in(childIds)使用针对字符串无法遍历childIds中颇负id,又要幸免在Java代码中先获得childIds,再split(",")之后循环获取对应good对象。玄妙运用childIds字段中id用","隔离的特征,全数应用FIND_IN_SET(id,str)函数。

SET @@max_sp_recursion_depth = 10; OPEN cur1; FETCH cur1 INTO b; WHILE done=0 DO CALL sp_createChildLst(b,nDepth 1); FETCH cur1 INTO b; END WHILE; CLOSE cur1; END

  select g.* from good g where FIND_IN_SET(g.id,(select childIds from good where id =1));    #万一输入1级id为1

--方法二(此方法有线程安全主题材料)

查询结果:重回1级下全数3级货品集结

CREATE PROCEDURE sp_getAddressChild_list(in idd varchar(36)) begin declare lev int; set lev=1; drop table if exists tmp1; CREATE TABLE tmp1(code_value VARCHAR(36),`name` varchar(50),pid varchar(36) ,levv INT); INSERT tmp1 SELECT code_value,`name`,pid,1 FROM address WHERE pid=idd; while row_count()>0 do set lev=lev 1; INSERT tmp1 SELECT t.code_value,t.`name`,t.pid,lev from address t join tmp1 a on t.pid=a.code_value AND levv=lev-1; end while ; INSERT tmp1 SELECT code_value,`name`,pid,0 FROM address WHERE code_value=idd; SELECT * FROM tmp1; end

澳门新濠3559 2

--方法三

>>>>>>>>>>>>>>>>>>>>>>>>solution>>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE FUNCTION fn_getAddress_ChildList_test(rootId INT) RETURNS varchar(1000) CHARSET utf8 #rootId为您要询问的节点 BEGIN

主题材料2:首先使用难题一种所查音讯获取具有的parentId

#声称七个有时变量 DECLARE temp VARCHARubicon(1000); DECLARE tempChd VARCHASportage(1000); SET temp = '$'; SET tempChd=CAST(rootId AS CHACR-V);#把rootId强制转变为字符

  select distinct g.parentId from good g where FIND_IN_SET(g.id,(select childIds from good where id =1))

WHILE tempChd is not null DO SET temp = CONCAT(temp,',',tempChd);#循环把富有节点连接成字符串。 SELECT GROUP_CONCAT(code_value) INTO tempChd FROM address where FIND_IN_SET(pid,tempChd)>0; END WHILE; RETURN temp; END

澳门新濠3559 3

--方法四

  既然parentId都早就查出来了,按以后格局先拿走具备parentId群集,再遍历查询出全数该1级childIds中的3级物品对应的2级货物种类消息。化解办法使用GROUP_CONCAT(id)将所查parentId拼接成"3,4,5"这样的字符串。

CREATE PROCEDURE sp_findAddressChild(iid varchar(50),layer bigint(20)) BEGIN /*创办接受询问的有时表 */ create temporary table if not exists tmp_table(id varchar(50),code_value varchar(50),name varchar(50),pid VARCHAR(50)) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*最高允许递归数*/ SET @@max_sp_recursion_depth = 10 ; call sp_iterativeAddress(iid,layer);/*中央数据采摘*/ select * from tmp_table ;/* 展现 */ drop temporary table if exists tmp_table ;/*删除有的时候表*/ END

先看看GROUP_CONCAT的用法:

CREATE PROCEDURE sp_iterativeAddress(iid varchar(50),layer bigint(20)) BEGIN DECLARE t_id INT; declare t_codeValue varchar(50) default iid ; declare t_name varchar(50) character set utf8; declare t_pid varchar(50) character set utf8; /* 游标定义 */ declare cur1 CURSOR FOR select id,code_value,`name`,pid from address where pid=iid ; declare CONTINUE HANDLER FOR SQLSTATE '02000' SET t_codeValue = null; /* 允许递归深度 */ if layer>0 then OPEN cur1 ; FETCH cur1 INTO t_id,t_codeValue,t_name,t_pid ; WHILE ( t_codeValue is not null ) DO /* 主题数据征采 */ insert into tmp_table values(t_id,t_codeValue,t_name,t_pid); call sp_iterativeAddress(t_codeValue,layer-1); FETCH cur1 INTO t_id,t_codeValue,t_name,t_pid ; END WHILE; end if; END

select GROUP_CONCAT(L2ids.parentId) from
  (select distinct g.parentId from good g where FIND_IN_SET(g.id,(select childIds from good where id =1))) L2ids

--方法五 SQL实现

澳门新濠3559 4

SELECT `name`,code_value AS code_value,pid AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路线 FROM ( SELECT `name`,code_value,pid, @le:= IF (pid = 0 ,0, IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0 , SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) 1 ,@le 1) ) levels , @pathlevel:= CONCAT(@pathlevel,'|',code_value,':', @le ,'|') pathlevel , @pathnodes:= IF( pid =0,',0', CONCAT_WS(',', IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 , SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1) ,@pathnodes ) ,pid ) )paths ,@pathall:=CONCAT(@pathall,'|',code_value,':', @pathnodes ,'|') pathall FROM address, (SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv ORDER BY pid,code_value ) src ORDER BY pid

 再选择难点第11中学的化解办法查询全体2级货物音信:

--方法6  存款和储蓄进度(SQL完成)

澳门新濠3559,select g.* from good g where FIND_IN_SET(g.id,
  (select GROUP_CONCAT(L2ids.parentId) from
    (select distinct g.parentId from good g where FIND_IN_SET(g.id,(select childIds from good where id =1))) L2ids))

create procedure query_all_add_children(in inPid varchar(50)) begin select id,code_value,name,remark,pid,p2id,p3id,p4id,p5id from( select a1.id,a1.code_value,a1.name,a1.remark, a1.pid,a2.pid p2id,a3.pid p3id,a4.pid p4id,a5.pid p5id from address a1 left join address a2 on(a1.pid=a2.code_value) left join address a3 on(a2.pid=a3.code_value) left join address a4 on(a3.pid=a4.code_value) left join address a5 on(a4.pid=a5.code_value) ) al where (pid=inPid or p2id=inPid or p3id=inPid or p4id=inPid or p5id=inPid ); end

澳门新濠3559 5

 

 

私家的部分领略:笔者是用的方法一:抽出全部节点利用MySql函数截取所须要的字符串,然后在SQL中字段IN(调用此格局)来打开查询,那样成效相比高,方法6效用也较高,其余格局都有成效难题。 

总括:在平日的表格带参数查询的函数中,能够先想想如何使用sql查询出所预期的结果,幸免大面积的施用Java代码进行巡回遍历。嗯,真香~

 

 

编辑:数据库 本文来源:mysql难于在树状表中层层遍历的子节点,varchar(

关键词: 澳门新濠3559