MySQL递归查询的3种实现方式实例

网络 数据库 75 0

当前第2页 返回上一页

1.创建函数的时候，可能会报错。This function has none of DETERMINISTIC 在MySQL安装根目录个人配置文件my.ini中添加一行：log_bin_trust_function_creators=1    然后重启MySQL服务。

2.创建函数的时候用到了系统函数GROUP_CONCAT()，该函数默认长度是1024，在配置文件my.ini 中修改默认长度，添加一行语句：group_concat_max_len=102400    保存后重启MySQL服务。查询sql SELECT @@global.group_concat_max_len;

3.这种方式实现之后，调用简单，但是效率较慢，而且由于字段 、函数、 长度的的限制，数据量大的时候可能查询不全。适合小数据量下使用。

2.1.1. 查询子节点的函数 查询时  包含自身

创建函数： 

-- 查询子节点的函数  查询时包含自身
CREATE DEFINER=`root`@`localhost` FUNCTION `queryChildren_1_sys_region`(areaId VARCHAR(15)) RETURNS varchar(20000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(20000);
DECLARE sTempChd VARCHAR(20000);
 
SET sTemp='$';
SET sTempChd = areaId;
 
WHILE sTempChd IS NOT NULL DO
 
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(code) INTO sTempChd FROM sys_region WHERE FIND_IN_SET(parent_code,sTempChd)>0;
 
END WHILE;
RETURN sTemp;
END

查询示例：

-- 子节点 含自己
SELECT * from sys_region where FIND_IN_SET(code,queryChildren_1_sys_region('370000000000'));

 2.1.2. 查询子节点的函数 查询时  不包含自身

创建函数： 

-- 查询子节点的函数 查询时  不包含自身
CREATE DEFINER=`root`@`localhost` FUNCTION `queryChildren_sys_region`(areaId VARCHAR(15)) RETURNS varchar(21840) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(21840);
DECLARE sTempChd VARCHAR(21840);
 
SET sTemp='$';
SET sTempChd = areaId;
 
WHILE sTempChd IS NOT NULL DO
 
if sTempChd != areaId then 
	SET sTemp= CONCAT(sTemp,',',sTempChd);
end if;
						
SELECT GROUP_CONCAT(code) INTO sTempChd FROM sys_region WHERE FIND_IN_SET(parent_code,sTempChd)>0;
 
END WHILE;
RETURN sTemp;
END

查询示例：

SELECT * from  sys_region where FIND_IN_SET(code,queryChildren_sys_region('370000000000'));

 2.1.3.查询父节点  查询的时候  包含自身

创建函数： 

-- 查询父节点  查询的时候 包含自身
 
CREATE DEFINER=`root`@`localhost` FUNCTION `queryParent_1_sys_region`(areaId VARCHAR(15)) RETURNS varchar(21840) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(21840);
DECLARE sTempChd VARCHAR(21840);
 
SET sTemp='$';
SET sTempChd = areaId;
 
-- SET sTemp = CONCAT(sTemp,',',sTempChd);
-- SELECT IFNULL(parent_code,'') INTO sTempChd FROM tgyi.sys_region  WHERE code = sTempChd;
 
WHILE (sTempChd <> '' ) DO
 
SET sTemp = CONCAT(sTemp,',',sTempChd);
select ifnull((SELECT parent_code FROM tgyi.sys_region  WHERE code = sTempChd),'')  INTO sTempChd ;
 
END WHILE;
 
RETURN sTemp;
END

查询示例：

SELECT * from sys_region where FIND_IN_SET(code,queryParent_1_sys_region('370171401000'));

 2.1.4.查询父节点  查询的时候  不包含自身

创建函数： 

-- 查询父节点   查询的时候  不包含自身
CREATE DEFINER=`root`@`localhost` FUNCTION `queryParent_sys_region`(areaId VARCHAR(15)) RETURNS varchar(21840) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(21840);
DECLARE sTempChd VARCHAR(21840);
 
SET sTemp='$';
SET sTempChd = areaId;
 
-- SET sTemp = CONCAT(sTemp,',',sTempChd);
-- SELECT IFNULL(parent_code,'') INTO sTempChd FROM tgyi.sys_region  WHERE code = sTempChd;
 
WHILE (sTempChd <> '' ) DO
 
if sTempChd != areaId then 
	SET sTemp = CONCAT(sTemp,',',sTempChd);
end if;
 
	
select ifnull((SELECT parent_code FROM tgyi.sys_region  WHERE code = sTempChd),'')  INTO sTempChd ;
 
END WHILE;
 
RETURN sTemp;
END

查询示例：

SELECT * from sys_region where FIND_IN_SET(code,queryParent_sys_region('370171401000'));

 2.2. 方式二  单纯使用sql  不创建函数  实现递归

注意：

写法比较复杂，但是适合MySQL各版本，比较灵活。

2.2.1.查询子节点  含自己

查询SQL脚本及效果截图：

 
-- 单纯使用SQL递归 查询子节点  含自己
SELECT T2.level_, T3.* 
  FROM( 
	SELECT @codes as _ids, 
			( SELECT @codes := GROUP_CONCAT(code) 
				FROM sys_region 
			   WHERE FIND_IN_SET(parent_code, @codes) 
			) as T1, 
		   @l := @l+1 as level_ 
	  FROM sys_region, 
		  (SELECT @codes :='370000000000', @l := 0 ) T4
	 WHERE @codes IS NOT NULL 
	   ) T2, sys_region T3 
  WHERE FIND_IN_SET(T3.code, T2._ids) 
  ORDER BY level_, code
	;

2.2.2.查询子节点  不含自己

查询SQL脚本及效果截图：

-- 单纯使用SQL递归 查询子节点  不含自己
SELECT T2.level_, T3.* 
  FROM( 
	SELECT @codes as _ids, 
			( SELECT @codes := GROUP_CONCAT(code) 
				FROM sys_region 
			   WHERE FIND_IN_SET(parent_code, @codes) 
			) as T1, 
		   @l := @l+1 as level_ 
	  FROM sys_region, 
		  (SELECT @codes :='370000000000', @l := -1 ) T4 
	 WHERE @codes IS NOT NULL 
	   ) T2, sys_region T3 
  WHERE FIND_IN_SET(T3.code, T2._ids) 
	  and code !='370000000000'
  ORDER BY level_, code
	;

2.2.3.查询父节点  含自己

查询SQL脚本及效果截图：

-- 单纯使用SQL递归 查询父节点  含自己
SELECT T2.level_, T3.* 
  FROM( 
	SELECT @code as _code, 
			( SELECT @code := parent_code 
				FROM sys_region 
			   WHERE code = @code 
			) as T1, 
		   @l := @l+1 as level_ 
	  FROM sys_region, 
		  (SELECT @code := '370171401000', @l := 0 ) T4 
	 WHERE @code is not null
	) T2, sys_region T3
 WHERE T2._code = T3.code 
 ORDER BY level_
 ;

2.2.4.查询父节点  不含自己

查询SQL脚本及效果截图：

 -- 单纯使用SQL递归 查询父节点  不含自己
SELECT T2.level_, T3.* 
  FROM( 
	SELECT @code as _code, 
			( SELECT @code := parent_code 
				FROM sys_region 
			   WHERE code = @code 
			) as T1, 
		   @l := @l+1 as level_ 
	  FROM sys_region, 
		  (SELECT @code := '370171401000', @l := -1 ) T4 
	 WHERE @code is not null
	) T2, sys_region T3
 WHERE T2._code = T3.code 
   and T2._code != '370171401000' 
 ORDER BY level_
 ;

 2.3. 方式三   MySQL 8.0 版本以上 使用 WITH RECURSIVE 实现递归 注意：

写法比较简单，也比较灵活，但是只适用于MySQL8.0及以上版本，这种写法其实和  PostgreSQL 的写法是一样的。

2.3.1.查询子节点  含自己

查询SQL脚本及效果截图：

 -- 查询子节点  含自己
 WITH RECURSIVE recursion (id, name, short_name, code, parent_code, level, flag) AS
(
  SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag  
	  from sys_region T1
	 where T1.code='370000000000'
  UNION ALL
	
  SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag 
    from sys_region T2, recursion T3
	 WHERE T2.parent_code=T3.code
)
SELECT T.id, T.name, T.short_name, T.code, T.parent_code, T.level, T.flag  
  FROM recursion T
	;

2.3.2.查询子节点  不含自己

查询SQL脚本及效果截图：

 -- 查询子节点   不含自己
 WITH RECURSIVE recursion (id, name, short_name, code, parent_code, level, flag) AS
(
  SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag  
	  from sys_region T1
	 where T1.code='370000000000'
  UNION ALL
	
  SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag 
    from sys_region T2, recursion T3
	 WHERE T2.parent_code=T3.code
)
SELECT T.id, T.name, T.short_name, T.code, T.parent_code, T.level, T.flag  
  FROM recursion T
 where T.code!='370000000000'
;

2.3.3.查询父节点  含自己

查询SQL脚本及效果截图：

 -- 查询父节点  含自己
 WITH RECURSIVE recursion (id, name, short_name, code, parent_code, level, flag) AS
(
  SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag  
	  from sys_region T1
	 where T1.code='370171401000'
  UNION ALL
	
  SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag 
    from sys_region T2, recursion T3
	 WHERE T2.code=T3.parent_code
)
SELECT T.id, T.name, T.short_name, T.code, T.parent_code, T.level, T.flag  
  FROM recursion T
	;

2.3.4.查询父节点  不含自己

查询SQL脚本及效果截图：

 WITH RECURSIVE recursion (id, name, short_name, code, parent_code, level, flag) AS
(
  SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag  
	  from sys_region T1
	 where T1.code='370171401000'
  UNION ALL
	
  SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag 
    from sys_region T2, recursion T3
	 WHERE T2.code=T3.parent_code
)
SELECT T.id, T.name, T.short_name, T.code, T.parent_code, T.level, T.flag  
  FROM recursion T
	where T.code!='370171401000'
	;

总结

到此这篇关于MySQL递归查询的3种实现方式的文章就介绍到这了,更多相关MySQL递归查询内容请搜索


标签：SQL

返回前面的内容

相关阅读 >>

mysql数据库简介与基本操作

sqlserver将查询结果转换为xml和json

sql复制表结构和数据的实现方法

sql优化有哪些方法

sql语句示例

mysql优化sql语句的技巧

sql server 数据库分区分表（水平分表）详细步骤

mysql 的 21 个规范、优化最佳实践！

sql server高并发生成唯一订单号

sql语言中，删除一个表的命令是什么？

更多相关阅读请进入《sql》频道 >>


数据库系统概念 第6版
书籍

数据库系统概念 第6版

机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。



标签：

打赏

取消

感谢您的支持，我会继续努力的!

扫码支持
扫码打赏，您说多少就多少

打开支付宝扫一扫，即可进行扫码打赏哦

分享从这里开始，精彩与您同在

相关推荐

评论

管理员已关闭评论功能...

    暂无评论...