mysql树形查询详解

使用的核心函数 FIND_IN_SET()

sql

create table `dept` (
    `code` varchar (255),
    `name` varchar (255),
    `parent_code` varchar (255)
); 
insert into `dept` (`code`, `name`, `parent_code`) values('C105','总公司',null);
insert into `dept` (`code`, `name`, `parent_code`) values('C105001','一分公司','C105');
insert into `dept` (`code`, `name`, `parent_code`) values('C105002','二分公司','C105');
insert into `dept` (`code`, `name`, `parent_code`) values('C105003','三分公司','C105');
insert into `dept` (`code`, `name`, `parent_code`) values('C105001001','规划处','C105001');
insert into `dept` (`code`, `name`, `parent_code`) values('C105001002','信息部','C105001');
insert into `dept` (`code`, `name`, `parent_code`) values('C105001002020','技术中心','C105001002');
insert into `dept` (`code`, `name`, `parent_code`) values('C105002090','塔里木','C105002');
insert into `dept` (`code`, `name`, `parent_code`) values('C105003008','长庆处','C105003'); 

查询sql

SELECT * FROM dept WHERE FIND_IN_SET(code, getchildrenlist('C105'));

创建函数

CREATE DEFINER=`root`@`localhost` FUNCTION `getChildrenList`(pcode varchar(255)) RETURNS varchar(1000) CHARSET utf8
begin
declare re varchar(1000);
declare parent varchar(1000);
 
set re = '$';
set parent =pcode ;
 
while parent is not null do
set re = concat(re,',',parent); -- 包含自身
select group_concat(code) into parent from `dept` where find_in_set(parent_code,parent)>0;
end while;
return re;
end

核心代码

select group_concat(code) into parent from `dept` where find_in_set(parent_code,parent)>0;

group_concat 去重
where find_in_set(parent_code,parent)>0递归