mysql實現(xiàn)connect by start with方式

更新時間：2024年12月19日 16:16:52 作者：Mars'Ares

本文介紹了MySQL中實現(xiàn)層級查詢的幾種方法,并分享了一種使用變量循環(huán)賦值的方式,通過實驗驗證了該方法的有效性

前言

1、mysql沒有層級查詢方法而 oracle通過connect by start with語法可以實現(xiàn)層級查詢

2、mysql實現(xiàn)層級查詢的方式很多，有使用存儲過程函數(shù)嵌套調用亦有使用臨時表進行層級查詢

3、本文使用一種變量循環(huán)賦值方式進行，可以套用模版

實驗

-- 創(chuàng)建測試表
-- 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;

-- 創(chuàng)建測試數(shù)據（根節(jié)點默認-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', '泉港區(qū)', '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、數(shù)據

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