mysql 实现 connect by start with

一 前言

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