mysql查询树形结构子节点_MySQL递归查询所有子节点，树形结构查询

--表结构

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 CHARSET=utf8 COMMENT='行政区域表';

--mysql 实现树结构查询

--方法一

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

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);

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

--方法二(此方法有线程安全问题)

CREATE PROCEDURE sp_getAddressChild_list(in idd varchar(36))

begin

declare lev int;

set lev=1;

drop table if exists tmp1;

CREAT