mysql实现connect by start with方式_Mysql

前言

1、mysql没有层级查询方法而 oracle通过connect by start with语法可以实现层级查询

2、mysql实现层级查询的方式很多，有使用存储过程函数嵌套调用亦有使用临时表进行层级查询

3、本文使用一种变量循环赋值方式进行，可以套用模版

实验

-- 创建测试表
-- drop table if exists `test_tree`;
create table `test_tree` (
  `id` varchar(10) default null,
  `name` varchar(10) default null,
  `pid` varchar(10) default null
) engine=innodb default charset=utf8;

-- 创建测试数据（根节点默认-1）
insert into `test_tree` values ('1', '中国', '-1');
insert into `test_tree` values ('2', '福建省', '1');
insert into `test_tree` values ('3', '海南省', '1');
insert into `test_tree` values ('4', '泉州市', '2');
insert into `test_tree` values ('5', '福州市', '2');
insert into `test_tree` values ('6', '泉港区', '4');
insert into `test_tree` values ('7', '惠安县', '4');

-- 模版 表名代替test_tree  用id替换以下id 用pid替换以下 用其他从属字段替换name
select
 name,
 id,
 pid,
 @le:= if (pid = -1 ,0,if( locate( concat('|',pid,':'),@pathlevel) > 0,substring_index( substring_index(@pathlevel,concat('|',pid,':'),-1),'|',1) +1,@le+1) ) levels,
 @pathlevel:= concat(@pathlevel,'|',id,':', @le ,'|') pathlevel,
 @pathnodes:= if( pid =-1,',root', concat_ws(',',if( locate( concat('|',pid,':'),@pathall) > 0 ,substring_index( substring_index(@pathall,concat('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths,
 @pathall:=concat(@pathall,'|',id,':', @pathnodes ,'|') pathall 
from test_tree, 
(
 select 
 @le:=0,
 @pathlevel:='', 
 @pathall:='',
 @pathnodes:=''
)  vv
order by pid,id
 
-- 结合instr(paths,'想要查所有子集的父级id')>0 验证
select
name,
id,
pid,
levels,
paths
from (
	select
	 name,
	 id,
	 pid,
	 @le:= if (pid = -1 ,0,if( locate( concat('|',pid,':'),@pathlevel) > 0,substring_index( substring_index(@pathlevel,concat('|',pid,':'),-1),'|',1) +1,@le+1) ) levels,
	 @pathlevel:= concat(@pathlevel,'|',id,':', @le ,'|') pathlevel,
	 @pathnodes:= if( pid =-1,',root', concat_ws(',',if( locate( concat('|',pid,':'),@pathall) > 0 ,substring_index( substring_index(@pathall,concat('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths,
	 @pathall:=concat(@pathall,'|',id,':', @pathnodes ,'|') pathall 
	from test_tree, 
	(
	 select 
	 @le:=0,
	 @pathlevel:='', 
	 @pathall:='',
	 @pathnodes:=''
	)  vv
	order by pid,id
) src
where instr(paths,'-1')>0
order by pid

验证结果

1、数据

2 查询中国

select
name,
id,
pid,
levels,
paths
from (
	select
	 name,
	 id,
	 pid,
	 @le:= if (pid = -1 ,0,if( locate( concat('|',pid,':'),@pathlevel) > 0,substring_index( substring_index(@pathlevel,concat('|',pid,':'),-1),'|',1) +1,@le+1) ) levels,
	 @pathlevel:= concat(@pathlevel,'|',id,':', @le ,'|') pathlevel,
	 @pathnodes:= if( pid =-1,',root', concat_ws(',',if( locate( concat('|',pid,':'),@pathall) > 0 ,substring_index( substring_index(@pathall,concat('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths,
	 @pathall:=concat(@pathall,'|',id,':', @pathnodes ,'|') pathall 
	from test_tree, 
	(
	 select 
	 @le:=0,
	 @pathlevel:='', 
	 @pathall:='',
	 @pathnodes:=''
	)  vv
	order by pid,id
) src
where instr(paths,'1')>0
order by pid

3 查询福建省

select
name,
id,
pid,
levels,
paths
from (
	select
	 name,
	 id,
	 pid,
	 @le:= if (pid = -1 ,0,if( locate( concat('|',pid,':'),@pathlevel) > 0,substring_index( substring_index(@pathlevel,concat('|',pid,':'),-1),'|',1) +1,@le+1) ) levels,
	 @pathlevel:= concat(@pathlevel,'|',id,':', @le ,'|') pathlevel,
	 @pathnodes:= if( pid =-1,',root', concat_ws(',',if( locate( concat('|',pid,':'),@pathall) > 0 ,substring_index( substring_index(@pathall,concat('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths,
	 @pathall:=concat(@pathall,'|',id,':', @pathnodes ,'|') pathall 
	from test_tree, 
	(
	 select 
	 @le:=0,
	 @pathlevel:='', 
	 @pathall:='',
	 @pathnodes:=''
	)  vv
	order by pid,id
) src
where instr(paths,'2')>0
order by pid