精华内容
下载资源
问答
  • 2022-04-07 18:19:28

    利用find_in_set()函数和group_concat()函数实现递归查询:

    1、向下递归:

    查询本部门及其所有子部门
    调用方式:

    SELECT dept_id
            FROM sys_dept
            WHERE FIND_IN_SET(dept_id, queryChildDeptInfo((select t.dept_id from sys_user t where t.user_id = #{user.userId})))
    

    实例:

    DROP FUNCTION IF EXISTS queryChildDeptInfo;
    CREATE FUNCTION `queryChildDeptInfo`(areaId INT) RETURNS varchar(4000) CHARSET utf8mb4
    BEGIN
        DECLARE sTemp VARCHAR(4000);
        DECLARE sTempChd VARCHAR(4000);
    
        SET sTemp='$';
        SET sTempChd = CAST(areaId AS CHAR);
    
        WHILE sTempChd IS NOT NULL DO
                SET sTemp= CONCAT(sTemp,',',sTempChd);
                SELECT GROUP_CONCAT(dept_id) INTO sTempChd FROM sys_dept WHERE FIND_IN_SET(parent_id,sTempChd)>0;
            END WHILE;
        RETURN sTemp;
    END
    

    2、向上递归:

    查询本部门及其所有父级部门

    SELECT dept_id
            FROM sys_dept
            WHERE FIND_IN_SET(dept_id, querySubDeptInfo((select t.dept_id from sys_user t where t.user_id = #{user.userId})))
    

    实例:

    CREATE  FUNCTION `querySubDeptInfo`(areaId INT) RETURNS varchar(4000) CHARSET utf8mb4
    BEGIN
        DECLARE sTemp VARCHAR(4000);
        DECLARE sTempChd VARCHAR(4000);
    
        SET sTemp='$';
        SET sTempChd = CAST(areaId AS CHAR);
    
    		SET sTemp = CONCAT(sTemp,',',sTempChd);
    		SELECT parent_id INTO sTempChd FROM sys_dept WHERE dept_id = sTempChd;
    		WHILE sTempChd <> 0 DO
    		SET sTemp = CONCAT(sTemp,',',sTempChd);
    		SELECT parent_id INTO sTempChd FROM sys_dept WHERE dept_id = sTempChd;
    		END WHILE;
    
        RETURN sTemp;
    END
    
    更多相关内容
  • 本程序写了两个sql存储过程,子节点查询算是照搬了,父节点查询是逆思维弄的
  • Mysql 递归查询

    2022-04-03 17:54:46
    Mysql 递归查询

    Mysql 递归查询

    对于数据库中的树形结构数据,如部门表,有时候,我们需要知道某部门的所有下属部分或者某部分的所有上级部门,这时候就需要用到数据的递归查询!

    1. oracle实现递归查询的话,就可以使用START WITH … CONNECT BY函数语法从而实现我们的效果!
    2. Mysql并没有提供类似函数,所以只能通过自定义函数实现!

    那么在Mysql中是如何实现递归操作的呢?前提了解一下相关的函数语法!

    FIND_IN_SET 函数的语法解释

    基本语法结构

    FIND_IN_SET(str,strlist)
    

    参数解释

    1. str:要查询的字符串
    2. strList:被查询的字符串,格式是:(2,3,5,6,7)

    匹配解释

    str字段匹配strList中的字符串,匹配到:1,无匹配到:0

    1.匹配到案例

    SELECT FIND_IN_SET('a','a,b,c,d,e,f,g,h,i')  RESULT FROM DUAL;
    

    匹配结果:
    在这里插入图片描述
    2.无匹配到案例

    SELECT FIND_IN_SET('aa','a,b,c,d,e,f,g,h,i')  RESULT FROM DUAL;
    

    匹配结果:
    在这里插入图片描述

    简单应用

    当前表名是:recursion ,数据如下图所示,进行简单的函数应用操作
    在这里插入图片描述需求:
    获取当前parent_id=1或2的返回值id!

    SELECT group_concat(id)  FROM recursion where FIND_IN_SET(parent_id,'1,2');
    

    匹配结果:
    在这里插入图片描述
    通过函数的简单应用,符合我们预期的期望值!

    递归函数实现

    前面我们了解了FIND_IN_SET函数的基本语法,接下来我们简单应用此函数!

    案例:
    通过父级id获取当前父级id的全部子级id!

    基础表结构

    CREATE TABLE `recursion` (
      `id` int DEFAULT NULL COMMENT '当前id',
      `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '节点名称',
      `parent_id` int DEFAULT NULL COMMENT '父级id'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    表数据

    INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (1, '数据中心平台', 0);
    INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (2, '系统信息维护', 1);
    INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (3, '用户管理', 2);
    INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (4, '系统业务监控', 1);
    INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (5, '菜单管理', 2);
    INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (6, '角色管理', 2);
    INSERT INTO `recursion`(`id`, `name`, `parent_id`) VALUES (7, '新增用户', 3);
    

    表数据逻辑

    在这里插入图片描述

    存储函数书写

    DROP FUNCTION IF EXISTS queryChildrenAreaInfo;
    
    CREATE FUNCTION queryChildrenAreaInfo(pid VARCHAR(50))
    RETURNS VARCHAR(4000)
    BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);
    DECLARE num int;
    
    SET num = 1;
    SET sTemp = '';
    SET sTempChd = pid;
     
    WHILE sTempChd IS NOT NULL DO
       IF num = 1 then 
          SET sTemp = CONCAT(sTemp,'',sTempChd);
       ELSE 
          SET sTemp = CONCAT(sTemp,',',sTempChd);
       END IF;
       set num = num +1;
       SELECT group_concat(id) INTO sTempChd FROM recursion where FIND_IN_SET(parent_id,sTempChd)>0;
    END WHILE;
    return sTemp;
    END;
    

    测试存储函数

    select queryChildrenAreaInfo('2');
    

    查看结果:
    在这里插入图片描述
    符合我们期望的递归获取当前id下的全部菜单id!

    灵活应用

    以上我们通过存储函数获取当前父级id下的全部子级id结果集,针对此结果集灵活应用!上述返回结果是:2,3,5,6,7

    FIND_IN_SET函数获取数据

    1. 具体sql
    SELECT * FROM recursion where FIND_IN_SET(id,'2,3,5,6,7');
    
    1. 匹配结果
      在这里插入图片描述

    IN获取数据

    需求:
    将结果集通过逗号分开,放在IN的结果集中查询即可实现我们的需求!

    利用help_topic表

    help_topic表就是以字符拆分,一行转多行,最终实现行转列功能!

    具体sql语句:

     SELECT
        substring_index(substring_index(('2,3,5,6,7'),',', b.help_topic_id + 1), ',', -1) result
    FROM
     mysql.help_topic b
    where
     b.help_topic_id < (LENGTH(('2,3,5,6,7')) - LENGTH(REPLACE(('2,3,5,6,7'), ',', '')) + 1);
    

    行转列结果:
    在这里插入图片描述
    将以上的sql修改一下,即可成为你自己的脚本!

    具体应用
    1. 具体sql
     SELECT * FROM recursion where id IN (
      SELECT
        substring_index(substring_index(('2,3,5,6,7'),',', b.help_topic_id + 1), ',', -1) result
    FROM
     mysql.help_topic b
    where
     b.help_topic_id < (LENGTH(('2,3,5,6,7')) - LENGTH(REPLACE(('2,3,5,6,7'), ',', '')) + 1)
     );
    
    1. 匹配结果
      在这里插入图片描述
      以上俩中方法就是对于FIND_IN_SET函数的结果集的具体应用在实际sql脚本中的俩种解决办法!

    若有不足之处,请您指正!

    展开全文
  • MySQL递归查询

    2013-07-15 15:42:47
    MySQL递归查询,oracle在迁移成mysql后难免会遇到的问题。
  • mysql 递归查询

    2021-01-28 05:59:41
    包含mysql 递归查询父节点 和子节点 包含mysql 递归查询父节点 和子节点 mysql递归查询,查父集合,查子集合 查子集合 --drop FUNCTION `getChildList` CREATE FUNCTION `getChi ... MySQL递归查询_函数语法检查_...

    1、创建表:

    DROP TABLE IF EXISTS `t_areainfo`;

    CREATE TABLE `t_areainfo` (

    `id` int(11) NOT '' AUTO_INCREMENT,

    `level` int(11) DEFAULT '',

    `name` varchar(255) DEFAULT '',

    `parentId` int(11) DEFAULT '',

    `status` int(11) DEFAULT '',

    PRIMARY KEY (`id`)

    ) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;

    2、初始数据:

    INSERT INTO `t_areainfo` VALUES ('', '', '中国', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '华北区', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '华南区', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '海淀区', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '丰台区', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '朝阳区', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区1', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区2', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区3', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区4', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区5', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区6', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区7', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区8', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区9', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区10', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区11', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区12', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区13', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区14', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区15', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区16', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区17', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区18', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区19', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区1', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区2', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区3', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区4', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区5', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区6', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区7', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区8', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区9', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区10', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区11', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区12', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区13', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区14', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区15', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区16', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区17', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区18', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', '北京XX区19', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省1', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省2', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省3', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省4', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省5', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省6', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省7', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省8', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省9', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省10', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省11', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省12', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省13', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省14', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省15', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省16', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省17', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省18', '', '');

    INSERT INTO `t_areainfo` VALUES ('', '', 'xx省19', '', '');

    3、向下递归:

    利用find_in_set()函数和group_concat()函数实现递归查询:

    DROP FUNCTION IF EXISTS queryChildrenAreaInfo;

    CREATE FUNCTION queryChildrenAreaInfo(areaId INT)

    RETURNS VARCHAR(4000)

    BEGIN

    DECLARE sTemp VARCHAR(4000);

    DECLARE sTempChd VARCHAR(4000);

    SET sTemp='$';

    SET sTempChd = CAST(areaId AS CHAR);

    WHILE sTempChd IS NOT NULL DO

    SET sTemp= CONCAT(sTemp,',',sTempChd);

    SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_areainfo WHERE FIND_IN_SET(parentId,sTempChd)>0;

    END WHILE;

    RETURN sTemp;

    END;

    4、调用方式:

    SELECT queryChildrenAreaInfo(1);

    d4a3f8a29c0c3a9a4aa41899f85a8c2f.png

    查询id为"4"下面的所有节点

    SELECT * FROM t_areainfo WHERE FIND_IN_SET(id,queryChildrenAreaInfo(4));

    e199cffa5acb15e37b778aad23c62f7b.png

    5、向上递归:

    DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;

    CREATE FUNCTION queryChildrenAreaInfo1(areaId INT)

    RETURNS VARCHAR(4000)

    BEGIN

    DECLARE sTemp VARCHAR(4000);

    DECLARE sTempChd VARCHAR(4000);

    SET sTemp='$';

    SET sTempChd = CAST(areaId AS CHAR);

    SET sTemp = CONCAT(sTemp,',',sTempChd);

    SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;

    WHILE sTempChd <> 0 DO

    SET sTemp = CONCAT(sTemp,',',sTempChd);

    SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;

    END WHILE;

    RETURN sTemp;

    END;

    6、调用方式:

    查询id为"7"的节点的所有上级节点:

    SELECT * from t_areainfo where FIND_IN_SET(id,queryChildrenAreaInfo1(7));

    ba13d71ec953ceecb41d9a407fe1d9f0.png

    包含mysql 递归查询父节点 和子节点

    包含mysql 递归查询父节点 和子节点 mysql递归查询,查父集合,查子集合 查子集合 --drop FUNCTION `getChildList` CREATE FUNCTION `getChi ...

    MySQL递归查询&lowbar;函数语法检查&lowbar;GROUP&lowbar;CONCAT组合结果集的使用

    1-前言: 在Mysql使用递归查询是很不方便的,不像Sqlserver可以直接使用声明变量,使用虚拟表等等.如:DECLARE,BEGIN ...  END   ,WHILE ,IF 等等. 在My ...

    MySQL递归查询树状表的子节点、父节点具体实现

    mysql版本(5.5.6等等)尚未支持循环递归查询,和sqlserver.oracle相比,mysql难于在树状表中层层遍历的子节点.本程序重点参考了下面的资料,写了两个sql存储过程,子节点查询算 ...

    递归的实际业务场景之MySQL 递归查询

    喜欢就点个赞呗! 源码

    MySQL递归查询

    MySQL8.0已经支持CTE递归查询,举例说明 CREATE TABLE EMP (EMPNO integer NOT NULL, ENAME ), JOB ), MGR integer, HIRE ...

    MySQL递归查询所有子节点,树形结构查询

    --表结构 CREATE TABLE `address` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code_value` varchar(32) DEFAUL ...

    MySQL递归查询树状表的子节点、父节点

    表结构和表数据就不公示了,查询的表user_role,主键是id,每条记录有parentid字段; 如下mysql查询函数即可实现根据一个节点查询所有的子节点,根据一个子节点查询所有的父节点.对于数据 ...

    mysql 递归查询 主要是对于层级关系的查询

    最近遇到了一个问题,在mysql中如何完成节点下的所有节点或节点上的所有父节点的查询?在Oracle中我们知道有一个Hierarchical Queries可以通过CONNECT BY来查询,但是,在 ...

    MySQL递归查询父子节点

    1.表结构 CREATE TABLE folder( id BIGINT(20) NOT NULL, parent_id BIGINT(20) DEFAULT NULL, PRIMARY KEY id ...

    随机推荐

    LR录制脚本IE不能打开解决方法

    运行环境:win7 64位 解决方法:1.卸载IE11 2.计算机——属性——高级系统设置——性能里的设置——数据执行保护——选择“为除下列选定程序之外的所有程序和服务启用”——添加IE浏览器和VUG ...

    js设计模式

    http://www.csdn.net/article/2011-09-02/303983 阐明JavaScript设计模式.CSDN研发频道对此文进行了整理选取部分内容,供开发者学习.参考. 内容如 ...

    CountDownLatch和CyclicBarrier的区别(转)

    在网上看到很多人对于CountDownLatch和CyclicBarrier的区别简单理解为CountDownLatch是一次性的,而CyclicBarrier在调用reset之后还可以继续使用.那如 ...

    openssl使用&plus;Demo

    1. websiteSSL(secure Socket Layer)TLS(transport Layer Security) - SSL3.0基础之上提出的安全通信标准,目前版本是1.0openss ...

    A Bug&&num;39&semi;s Life(种类并查集)(也是可以用dfs做)

    http://acm.hdu.edu.cn/showproblem.php?pid=1829   A Bug's Life Time Limit:5000MS     Memory Limit:327 ...

    VSCode插件MSSQL教程(昨天提了一下)

    推荐一个跨平台SQL IDE:https://docs.microsoft.com/zh-cn/sql/sql-operations-studio/download 什么数据库都木有(系统自带的不算) ...

    url全部信息打印

    public String findAllContract(HttpServletRequest request,String a){ String string = new StringBuilde ...

    sublime安装说明

    安装Install package https://www.cnblogs.com/lixuwu/p/5693624.html 常用配置 Perference → Settings – User,用下 ...

    split函数

    b="aa,:bb:c,c"a1,a2,a3=b.split(":")  #以:为分隔符,分成3个字符串

    kbmmw 中虚拟文件操作入门

    kbmmw 中一直有一个功能,但是基本上都没有提过,但是在实际应用中,却非常有用,这个功能就是 虚拟文件包功能,他可以把一大堆文件保存到一个文件里面,方便后台管理. kbmmw 的虚拟文件在单元kbm ...

    展开全文
  • MySQL 递归查询

    2021-03-05 20:59:11
    但是,我记得 MySQL 是没有递归查询功能的,那 MySQL 中应该怎么实现呢? 于是,就有了这篇文章。 文章主要知识点: Oracle 递归查询, start with connect by prior 用法 find_in_set 函数 concat,concat_ws...

    前言

    最近在做的业务场景涉及到了数据库的递归查询。我们公司用的 Oracle ,众所周知,Oracle 自带有递归查询的功能,所以实现起来特别简单。

    但是,我记得 MySQL 是没有递归查询功能的,那 MySQL 中应该怎么实现呢?

    于是,就有了这篇文章。

    文章主要知识点:

    • Oracle 递归查询, start with connect by prior 用法
    • find_in_set 函数
    • concat,concat_ws,group_concat 函数
    • MySQL 自定义函数
    • 手动实现 MySQL 递归查询

    Oracle 递归查询

    在 Oracle 中是通过 start with connect by prior 语法来实现递归查询的。

    按照 prior 关键字在子节点端还是父节点端,以及是否包含当前查询的节点,共分为四种情况。

    prior 在子节点端(向下递归)

    第一种情况: start with 子节点id = ' 查询节点 ' connect by prior 子节点id = 父节点id

    select * from dept start with id='1001' connet by prior id=pid;

    这里,按照条件 id='1001' 对当前节点以及它的子节点递归查询。查询结果包含自己及所有子节点。

    第二种情况: start with 父节点id= ' 查询节点 ' connect by prior 子节点id = 父节点 id

    select * from dept start with pid='1001' connect by prior id=pid;

    这里,按照条件 pid='1001' 对当前节点的所有子节点递归查询。查询结果只包含它的所有子节点,不包含自己

    其实想一想也对,因为开始条件是以父节点为根节点,且向下递归,自然不包含当前节点。

    prior 在父节点端(向上递归)

    第三种情况: start with 子节点id= ' 查询节点 ' connect by prior 父节点id = 子节点id

    select * from dept start with id='1001' connect by prior pid=id;

    这里按照条件 id='1001' ,对当前节点及其父节点递归查询。查询结果包括自己及其所有父节点。

    第四种情况: start with 父节点id= ' 查询节点 ' connect by prior 父节点id = 子节点id

    select * from dept start with pid='1001' connect by prior pid=id;

    这里按照条件 pid='1001',对当前节点的第一代子节点以及它的父节点递归查询。查询结果包括自己的第一代子节点以及所有父节点。(包括自己

    其实这种情况也好理解,因为查询开始条件是以 父节点为根节点,且向上递归,自然需要把当前父节点的第一层子节点包括在内。

    以上四种情况初看可能会让人迷惑,容易记混乱,其实不然。

    我们只需要记住 prior 的位置在子节点端,就向下递归,在父节点端就向上递归。

    • 开始条件若是子节点的话,自然包括它本身的节点。
    • 开始条件若是父节点的话,则向下递归时,自然不包括当前节点。而向上递归,需要包括当前节点及其第一代子节点。

    MySQL 递归查询

    可以看到,Oracle 实现递归查询非常的方便。但是,在 MySQL 中并没有帮我们处理,因此需要我们自己手动实现递归查询。

    为了方便,我们创建一个部门表,并插入几条可以形成递归关系的数据。

    DROP TABLE IF EXISTS `dept`;
    CREATE TABLE `dept`  (
      `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `pid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1000', '总公司', NULL);
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1001', '北京分公司', '1000');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1002', '上海分公司', '1000');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1003', '北京研发部', '1001');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1004', '北京财务部', '1001');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1005', '北京市场部', '1001');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1006', '北京研发一部', '1003');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1007', '北京研发二部', '1003');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1008', '北京研发一部一小组', '1006');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1009', '北京研发一部二小组', '1006');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1010', '北京研发二部一小组', '1007');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1011', '北京研发二部二小组', '1007');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1012', '北京市场一部', '1005');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1013', '上海研发部', '1002');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1014', '上海研发一部', '1013');
    INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1015', '上海研发二部', '1013');

    没错,刚才 Oracle 递归,就是用的这张表。

    另外,在这之前,我们需要复习一下几个 MYSQL中的函数,后续会用到。

    find_in_set 函数

    函数语法:find_in_set(str,strlist)

    str 代表要查询的字符串 , strlist 是一个以逗号分隔的字符串,如 ('a,b,c')。

    此函数用于查找 str 字符串在字符串 strlist 中的位置,返回结果为 1 ~ n 。若没有找到,则返回0。

    举个栗子:

    select FIND_IN_SET('b','a,b,c,d'); 

    结果返回 2 。因为 b 所在位置为第二个子串位置。

    此外,在对表数据进行查询时,它还有一种用法,如下:

    select * from dept where FIND_IN_SET(id,'1000,1001,1002'); 

    结果返回所有 id 在 strlist 中的记录,即 id = '1000' ,id = '1001' ,id = '1002' 三条记录。

    看到这,对于我们要解决的递归查询,不知道你有什么启发没。

    以向下递归查询所有子节点为例。我想,是不是可以找到一个包含当前节点和所有子节点的以逗号拼接的字符串 strlist,传进 find_in_set 函数。就可以查询出所有需要的递归数据了。

    那么,现在问题就转化为怎样构造这样的一个字符串 strlist 。

    这就需要用到以下字符串拼接函数了。

    concat,concat_ws,group_concat 函数

    一、字符串拼接函数中,最基本的就是 concat 了。它用于连接N个字符串,如,

    select CONCAT('M','Y','S','Q','L') from dual; 

    结果为 'MYSQL' 字符串。

    二、concat 是以逗号为默认的分隔符,而 concat_ws 则可以指定分隔符,第一个参数传入分隔符,如以下划线分隔。

    三、group_concat 函数更强大,可以分组的同时,把字段以特定分隔符拼接成字符串。

    用法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ][separator '分隔符'] )

    可以看到有可选参数,可以对将要拼接的字段值去重,也可以排序,指定分隔符。若没有指定,默认以逗号分隔。

    对于 dept 表,我们可以把表中的所有 id 以逗号拼接。(这里没有用到 group by 分组字段,则可以认为只有一组)

    MySQL 自定义函数,实现递归查询

    可以发现以上已经把字符串拼接的问题也解决了。那么,问题就变成怎样构造有递归关系的字符串了。

    我们可以自定义一个函数,通过传入根节点id,找到它的所有子节点。

    以向下递归为例。 (讲解自定义函数写法的同时,讲解递归逻辑)

    delimiter $$ 
    drop function if exists get_child_list$$ 
    create function get_child_list(in_id varchar(10)) returns varchar(1000) 
    begin 
        declare ids varchar(1000) default ''; 
        declare tempids varchar(1000); 
     
        set tempids = in_id; 
        while tempids is not null do 
            set ids = CONCAT_WS(',',ids,tempids); 
            select GROUP_CONCAT(id) into tempids from dept where FIND_IN_SET(pid,tempids)>0;  
        end while; 
        return ids; 
    end  
    $$ 
    delimiter ; 

    (1) delimiter $$ ,用于定义结束符。我们知道 MySQL 默认的结束符为分号,表明指令结束并执行。但是在函数体中,有时我们希望遇到分号不结束,因此需要暂时把结束符改为一个随意的其他值。我这里设置为 $$,意思是遇到 $$ 才结束,并执行当前语句。

    (2)drop function if exists get_child_list$$ 。若函数 get_child_list 已经存在了,则先删除它。注意这里需要用 当前自定义的结束符 $$ 来结束并执行语句。 因为,这里需要和下边的函数体单独区分开来。

    (3)create function get_child_list 创建函数。并且参数传入一个根节点的子节点id,需要注意一定要注明参数的类型和长度,如这里是 varchar(10)。returns varchar(1000) 用来定义返回值参数类型。

    (4)begin 和 end 中间包围的就是函数体。用来写具体的逻辑。

    (5)declare 用来声明变量,并且可以用 default 设置默认值。

    这里定义的 ids 即作为整个函数的返回值,是用来拼接成最终我们需要的以逗号分隔的递归串的。

    而 tempids 是为了记录下边 while 循环中临时生成的所有子节点以逗号拼接成的字符串。

    (6) set 用来给变量赋值。此处把传进来的根节点赋值给 tempids 。

    (7) while do ... end while; 循环语句,循环逻辑包含在内。注意,end while 末尾需要加上分号。

    循环体内,先用 CONCAT_WS 函数把最终结果 ids 和 临时生成的 tempids 用逗号拼接起来。

    然后以 FIND_IN_SET(pid,tempids)>0 为条件,遍历在 tempids 中的所有 pid ,寻找以此为父节点的所有子节点 id ,并且通过 GROUP_CONCAT(id) into tempids 把这些子节点 id 都用逗号拼接起来,并覆盖更新 tempids 。

    等下次循环进来时,就会再次拼接 ids ,并再次查找所有子节点的所有子节点。循环往复,一层一层的向下递归遍历子节点。直到判断 tempids 为空,说明所有子节点都已经遍历完了,就结束整个循环。

    这里,用 '1000' 来举例,即是:(参看图1的表数据关系)

    第一次循环:
      tempids=1000  ids=1000    tempids=1001,1002 (1000的所有子节点)
    第二次循环:
      tempids=1001,1002  ids=1000,1001,1002  tempids=1003,1004,1005,1013 (1001和1002的所有子节点)
    第三次循环:
      tempids=1003,1004,1005,1013 
      ids=1000,1001,1002,1003,1004,1005,1013 
      tempids=1003和1004和1005及1013的所有子节点
    ...
    最后一次循环,因找不到子节点,tempids=null,就结束循环。

    (8)return ids; 用于把 ids 作为函数返回值返回。

    (9)函数体结束以后,记得用结束符 $$ 来结束整个逻辑,并执行。

    (10)最后别忘了,把结束符重新设置为默认的结束符分号 。

    自定义函数做好之后,我们就可以用它来递归查询我们需要的数据了。如,我查询北京研发部的所有子节点。

    以上是向下递归查询所有子节点的,并且包括了当前节点,也可以修改逻辑为不包含当前节点,我就不演示了。

    手动实现递归查询(向上递归)

    相对于向下递归来说,向上递归比较简单。

    因为向下递归时,每一层递归一个父节点都对应多个子节点。

    而向上递归时,每一层递归一个子节点只对应一个父节点,关系比较单一。

    同样的,我们可以定义一个函数 get_parent_list 来获取根节点的所有父节点。

    delimiter $$ 
    drop function if exists get_parent_list$$ 
    create function get_parent_list(in_id varchar(10)) returns varchar(1000) 
    begin 
        declare ids varchar(1000); 
        declare tempid varchar(10); 
         
        set tempid = in_id; 
        while tempid is not null do 
            set ids = CONCAT_WS(',',ids,tempid); 
            select pid into tempid from dept where id=tempid; 
        end while; 
        return ids; 
    end 
    $$ 
    delimiter ; 

    查找北京研发二部一小组,以及它的递归父节点,如下:

    注意事项

    我们用到了 group_concat 函数来拼接字符串。但是,需要注意它是有长度限制的,默认为 1024 字节。可以通过 show variables like "group_concat_max_len"; 来查看。

    注意,单位是字节,不是字符。在 MySQL 中,单个字母占1个字节,而我们平时用的 utf-8下,一个汉字占3个字节。

    这个对于递归查询还是非常致命的。因为一般递归的话,关系层级都比较深,很有可能超过最大长度。(尽管一般拼接的都是数字字符串,即单字节)

    所以,我们有两种方法解决这个问题:

    1. 修改 MySQL 配置文件 my.cnf ,增加 group_concat_max_len = 102400 #你要的最大长度 。
    2. 执行以下任意一个语句。SET GLOBAL group_concat_max_len=102400; 或者 SET SESSION group_concat_max_len=102400;

    他们的区别在于,global是全局的,任意打开一个新的会话都会生效,但是注意,已经打开的当前会话并不会生效。而 session 是只会在当前会话生效,其他会话不生效。

    共同点是,它们都会在 MySQL 重启之后失效,以配置文件中的配置为准。所以,建议直接修改配置文件。102400 的长度一般也够用了。假设一个id的长度为10个字节,也能拼上一万个id了。

    除此之外,使用 group_concat 函数还有一个限制,就是不能同时使用 limit 。如,

    本来只想查5条数据来拼接,现在不生效了。

    不过,如果需要的话,可以通过子查询来实现,

    展开全文
  • mysql递归查询

    千次阅读 2021-11-28 23:25:47
    2.1. 方式一 创建自定义函数实现递归查询 注意: 2.1.1. 查询子节点的函数 查询时  包含自身  2.1.2. 查询子节点的函数 查询时  不包含自身  2.1.3.查询父节点  查询的时候  包含自身...
  • Mysql递归查询

    2022-03-18 08:12:41
    递归父级查询所有子级 select t3.subject_code,t3.psubject_code,t3.id from ( select t1.subject_code,t1.psubject_code,t1.id, if(find_in_set( psubject_code, @pcode ) > 0, @pcode := concat( @pcode, ',', ...
  • mysql递归查询.txt

    2018-10-30 09:28:26
    mysql递归查询.txt,里面写了mysql递归查询的例子,方便需要的朋友使用,可读性强,简单实用
  • MySQL递归查询 三种实现方式,方式一:使用自定义函数实现,方式二:纯SQL实现,方式三:适用于MySQL8及以上使用 WITH RECURSIVE实现
  • 对于数据库中的树形结构数据,如部门表,有时候,我们需要知道某部门的所有下属部分或者某部分的所有上级部门,这时候就需要用到mysql递归查询 最近在做项目迁移,Oracle版本的迁到Mysql版本,遇到有些oracle的...
  • mysql 递归查询 树型结构 代码逻辑
  • 主要介绍了根据递归到迭代转化的思路,利用SQL实现了MYSQL递归查询,需要的朋友可以参考下
  • 现在大家应该都知道mysql版本中已经不支持直接的递归查询了,但是我们可以通过递归到迭代转化的思路是实现树的递归查询,接下来我们就一起去看看SQL实现MYSQL递归查询的方法。创建表格CREATE TABLE `treenodes` (`id...
  • 例子,mysql递归查询代码。复制代码 代码示例:DROP TABLE IF EXISTS `treenodes`;CREATE TABLE `treenodes` (`id` int(11) NOT NULL,`nodename` varchar(20) DEFAULT NULL,`pid` int(11) DEFAULT NULL,PRIMARY KEY ...
  • MYSQL递归查询所有父节点

    千次阅读 2021-04-09 15:22:54
    MYSQL递归查询所有父节点 1、表结构: CREATETABLE`t_busi_system`( `ID`varchar(64)NOTNULLCOMMENT'标识', `PARENT_ID`varchar(64)DEFAULTNULLCOMMENT'父id', `CREATE_DATE`varchar(64)DEFAULTNULLCOMMENT'创建...
  • mysql递归查询所有父类数据

    千次阅读 2022-03-24 11:22:58
    需求:已知某个id的值,需要查询这个id关联的所有父级的数据,如查询到该id的父类数据之后,还需要查询父类数据的爷爷辈数据,依次类推,直到查询到顶级。 SELECT T2.* FROM ( SELECT @r AS _id, (SELECT @r...
  • mysql递归查询

    2021-09-27 14:28:11
    1.首先要知道 group_concat(column_name): 多条记录合成一条记录 find_in_set(column_name, strlist)在多条记录中查询特定列 2.然后知道mysql可以已定义变量@XXX,例如@pid 3.逻辑:待定-------
  • PHP MySQL递归查询

    2021-01-19 10:55:16
    要求查询类似下面的形式(大概的样子,还可以展示成其他样子),总体来说就是显示出每个父类所拥有的子类:1=>Array{2=>data,3=>data}4=>Array{5=>Array{7=>data},6=>data}代码:function ...
  • MySql递归查询,mysql中从子类ID查询所有父类(做无限分类经常用到) 由于mysql 不支持类似 Oracle with ...connect的 递归查询语法 之前一直以为类似的查询要么用存储过程要么只能用程序写递归查询. 现在发现...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 59,605
精华内容 23,842
关键字:

mysql递归查询

mysql 订阅