精华内容
下载资源
问答
  • MySQL递归查询 三种实现方式,方式一:使用自定义函数实现,方式二:纯SQL实现,方式三:适用于MySQL8及以上使用 WITH RECURSIVE实现
  • MySql实现递归查询

    2020-09-18 13:46:31
    据我了解,Oracle实现递归查询非常的方便,但mysql不行,需要自定义函数来完成。 创建表(Dept) DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_...

    写在前面

    众所周知,java中递归查询,需要和数据库进行多次交互,不论是向上查询还是向下查询,所以不如进行一次交互就完成查询。据我了解,Oracle实现递归查询非常的方便,但mysql不行,需要自定义函数来完成。

    创建表(Dept)

    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');

    创建完成如下图

    首先熟悉一下,mysql  find_in_set 函数。

    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'); 

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

    select * from dept where FIND_IN_SET(id,'1000,1001,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  则可以指定分隔符,第一个参数传入分隔符,如以下划线分隔。

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

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

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

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

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

    select group_concat(id) from dept; 

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

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

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

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

    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  
    

     

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

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

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

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

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

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

    (5) 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 为空,说明所有子节点都已经遍历完了,就结束整个循环。

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

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

    向上递归

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

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

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

    同样的,我们可以定义一个函数 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 

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

    注意事项

    我们用到了 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条数据来拼接,现在不生效了。

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

     

    转载于:IT牧场

    展开全文
  • MySQL实现递归查询

    千次阅读 2019-08-13 14:52:32
    前几日有客户咨询关于mysql实现递归查询的方法,当时简单了解了一下,觉得递归查询逻辑层面一种特殊查询方式。但是后来才发现这是一种很常见的查询需求,例如某些评论楼层的折叠显示、各类流程图等用递归查询都能...

    概述

    前几日有客户咨询关于mysql实现递归查询的方法,当时简单了解了一下,觉得递归查询逻辑层面一种特殊查询方式。但是后来才发现这是一种很常见的查询需求,例如某些评论楼层的折叠显示、各类流程图等用递归查询都能实现。但是MySQL本身而言是没有实现递归查询功能,但是可以通过一些特殊的方法来实现此功能,本文就简单测试一些MySQL实现递归查询的方法

    测试环境

    测试环境是RDS for MySQL 5.7,测试的表的主要逻辑就是 省份–城市–市区 ,为了实现这个逻辑,先要准备好表与数据,如下

    测试表

    CREATE TABLE `recursion_test` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `parent_id` int(11) NOT NULL,
     `name` varchar(32) NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB
    DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
    COMMENT='递归测试表';
    

    主键递增,ID与父ID,然后就是名字。表的逻辑需要通过递归查询才能实现

    插入测试数据

    插入数据如下

    INSERT INTO recursion_test VALUES(1,1,'浙江省');
    INSERT INTO recursion_test VALUES(2,2,'江苏省');
    INSERT INTO recursion_test VALUES(3,3,'安徽省');
    
    INSERT INTO recursion_test VALUES(4,1,'杭州市');
    INSERT INTO recursion_test VALUES(5,1,'宁波市');
    INSERT INTO recursion_test VALUES(6,1,'金华市');
    
    
    INSERT INTO recursion_test VALUES(7,2,'南京市');
    INSERT INTO recursion_test VALUES(8,2,'苏州市');
    INSERT INTO recursion_test VALUES(9,2,'徐州市');
    
    
    INSERT INTO recursion_test VALUES(10,3,'合肥市');
    INSERT INTO recursion_test VALUES(11,3,'芜湖市');
    INSERT INTO recursion_test VALUES(12,3,'池州市');
    
    
    INSERT INTO recursion_test VALUES(13,4,'西湖区');
    INSERT INTO recursion_test VALUES(14,4,'滨江区');
    INSERT INTO recursion_test VALUES(15,4,'余杭区');
    
    INSERT INTO recursion_test VALUES(16,5,'海曙区');
    INSERT INTO recursion_test VALUES(17,5,'江北区');
    INSERT INTO recursion_test VALUES(18,5,'镇海区');
    
    INSERT INTO recursion_test VALUES(19,6,'婺城区');
    INSERT INTO recursion_test VALUES(20,6,'金东区');
    INSERT INTO recursion_test VALUES(21,6,'永康市');
    
    INSERT INTO recursion_test VALUES(22,7,'玄武区');
    INSERT INTO recursion_test VALUES(23,7,'秦淮区');
    INSERT INTO recursion_test VALUES(24,7,'建邺区');
    
    INSERT INTO recursion_test VALUES(25,8,'沧浪区');
    INSERT INTO recursion_test VALUES(26,8,'平江区');
    INSERT INTO recursion_test VALUES(27,8,'虎丘区');
    
    INSERT INTO recursion_test VALUES(28,9,'云龙区');
    INSERT INTO recursion_test VALUES(29,9,'鼓楼区');
    INSERT INTO recursion_test VALUES(30,9,'泉山区');
    
    INSERT INTO recursion_test VALUES(31,10,'蜀山区');
    INSERT INTO recursion_test VALUES(32,10,'庐阳区');
    INSERT INTO recursion_test VALUES(33,10,'瑶海区');
    
    INSERT INTO recursion_test VALUES(34,11,'镜湖区');
    INSERT INTO recursion_test VALUES(35,11,'鸠江区');
    INSERT INTO recursion_test VALUES(36,11,'弋江区');
    
    INSERT INTO recursion_test VALUES(37,12,'贵池区');
    INSERT INTO recursion_test VALUES(38,12,'九华山区');
    INSERT INTO recursion_test VALUES(39,12,'青阳');
    

    OK,准备好 无趣的数据,开始试着做做递归查询

    开始测试

    使用表连接

    若确定所需查询树的最大深度。则可以直接使用left join来实现,每有一级递归就做一次join。例如ID=父_ID,选取相应的字段就可以按照递归顺序查询出来
    我们这个表的逻辑递归层只有三层,理论上只要做两次表连接即可查询,如下,查询

    mysql>SELECT t1.name as '省份',t2.name as '城市',t3.name as '市区'
    FROM recursion_test t1
    LEFT JOIN recursion_test t2 ON t1.id = t2.parent_id
    LEFT JOIN recursion_test t3 ON t2.id = t3.parent_id
    WHERE t1.id = '1' and t2.id <> 1;
    +----------------+----------------+----------------+
    | 省份 | 城市 | 市区 |
    +----------------+----------------+----------------+
    | 浙江省 | 杭州市 | 西湖区 |
    | 浙江省 | 杭州市 | 滨江区 |
    | 浙江省 | 杭州市 | 余杭区 |
    | 浙江省 | 宁波市 | 海曙区 |
    | 浙江省 | 宁波市 | 江北区 |
    | 浙江省 | 宁波市 | 镇海区 |
    | 浙江省 | 金华市 | 婺城区 |
    | 浙江省 | 金华市 | 金东区 |
    | 浙江省 | 金华市 | 永康市 |
    +----------------+----------------+----------------+
    

    临时表+存储过程

    第一个存储过程负责将每个节点的数据写到临时表中,递归查询到最底层的节点

    CREATE PROCEDURE `findtestList`() 
     COMMENT '递归查询' 
    BEGIN
      DECLARE v_test VARCHAR(20) DEFAULT '';
      DECLARE done INTEGER DEFAULT 0;
        -- 查询结果放入游标中
      DECLARE C_test CURSOR FOR SELECT d.id
                               FROM recursion_test d
                               WHERE d.parent_id = testId;
      DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
      SET @@max_sp_recursion_depth = 10;
        
        -- 传入的组织id写入临时表
      INSERT INTO tmp_test VALUES (testId);
      OPEN C_test;
      FETCH C_test INTO v_test;
      WHILE (done=0)
      DO
            -- 递归调用,查找下级
        CALL findtestList(v_test);
        FETCH C_test INTO v_test;
      END WHILE;
      CLOSE C_test;
    END 
    

    第二个存储过程是负责创建和删除临时表,并且调用第一个存储过程进行表的数据的输出

    CREATE DEFINER=`root`@`%` PROCEDURE `recursion_testList`(
      IN testid VARCHAR(20)
    )
        DETERMINISTIC
        COMMENT '临时表'
    BEGIN
     DROP TEMPORARY TABLE IF EXISTS tmp_test;
        -- 创建临时表
        CREATE TEMPORARY TABLE tmp_test(testid VARCHAR(20));
        -- 清空临时表数据
        DELETE FROM tmp_test;
        -- 发起调用
        CALL findtestList(testId);
        -- 从临时表查询结果
        select * from recursion_test where id in (SELECT * FROM tmp_test ORDER BY testid);
    END
    

    做几次测试查询

    mysql>call recursion_testList(2)
    +--------------+---------------------+----------------+
    | id | parent_id | name |
    +--------------+---------------------+----------------+
    | 2 | 0 | 江苏省 |
    | 7 | 2 | 南京市 |
    | 22 | 7 | 玄武区 |
    | 23 | 7 | 秦淮区 |
    | 24 | 7 | 建邺区 |
    | 8 | 2 | 苏州市 |
    | 25 | 8 | 沧浪区 |
    | 26 | 8 | 平江区 |
    | 27 | 8 | 虎丘区 |
    | 9 | 2 | 徐州市 |
    | 28 | 9 | 云龙区 |
    | 29 | 9 | 鼓楼区 |
    | 30 | 9 | 泉山区 |
    +--------------+---------------------+----------------+
    
    
    mysql>call recursion_testList(8)
    +--------------+---------------------+----------------+
    | id | parent_id | name |
    +--------------+---------------------+----------------+
    | 8 | 2 | 苏州市 |
    | 25 | 8 | 沧浪区 |
    | 26 | 8 | 平江区 |
    | 27 | 8 | 虎丘区 |
    +--------------+---------------------+----------------+
    

    使用函数

    使用自定义函数也可以实现递归查询,个人觉得自定义函数实现递归查询最好的方法,灵活多变。

    自上而下实现查询,比如查询一个城市,显示这个城市下所有的区域等

    CREATE DEFINER=`root`@`%` FUNCTION `findtest_down`(rootId INT) RETURNS varchar(4000) CHARSET utf8
        DETERMINISTIC
    BEGIN
      DECLARE sTemp VARCHAR(4000);
      DECLARE sTempChd VARCHAR(4000);
      SET sTemp = '$';
      SET sTempChd = CAST(rootId as CHAR);
      WHILE sTempChd is not null DO
        SET sTemp = CONCAT(sTemp,',',sTempChd);
        SELECT GROUP_CONCAT(id) INTO sTempChd FROM recursion_test
            WHERE FIND_IN_SET(parent_id,sTempChd)>0;
      END WHILE;
      RETURN sTemp;
    END
    

    其中CONCAT和FIND_IN_SET函数的作用是:

    • GROUP_CONCAT(expr)
      该函数会从expr中连接所有非NULL的字符串。如果没有非 NULL 的字符串,那么它就会返回NULL。
      注意事项:GROUP_CONCAT查询结果默认最大长度限制为1024,该值是系统变量group_concat_max_len的默认值,可以通过SET [GLOBAL | SESSION] group_concat_max_len = val;更改该值。

    • FIND_IN_SET(str,strlist)
      该函数返回一个1~N的值表示str在strlist中的位置。
      该函数结合WHERE使用对结果集进行过过滤(查找str包含在strlist结果集里面的记录)

    可以直接查询某个省下的所有城市和时区

    mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(1));
    +--------------+---------------------+----------------+
    | id | parent_id | name |
    +--------------+---------------------+----------------+
    | 1 | 0 | 浙江省 |
    | 4 | 1 | 杭州市 |
    | 5 | 1 | 宁波市 |
    | 6 | 1 | 金华市 |
    | 13 | 4 | 西湖区 |
    | 14 | 4 | 滨江区 |
    | 15 | 4 | 余杭区 |
    | 16 | 5 | 海曙区 |
    | 17 | 5 | 江北区 |
    | 18 | 5 | 镇海区 |
    | 19 | 6 | 婺城区 |
    | 20 | 6 | 金东区 |
    | 21 | 6 | 永康市 |
    +--------------+---------------------+----------------+
    

    查询一下ID为6 金华市和ID为7南京的下属市区

    mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(6));
    +--------------+---------------------+----------------+
    | id | parent_id | name |
    +--------------+---------------------+----------------+
    | 6 | 1 | 金华市 |
    | 19 | 6 | 婺城区 |
    | 20 | 6 | 金东区 |
    | 21 | 6 | 永康市 |
    +--------------+---------------------+----------------+
    返回行数:[4],耗时:9 ms.
    mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_down(7));
    +--------------+---------------------+----------------+
    | id | parent_id | name |
    +--------------+---------------------+----------------+
    | 7 | 2 | 南京市 |
    | 22 | 7 | 玄武区 |
    | 23 | 7 | 秦淮区 |
    | 24 | 7 | 建邺区 |
    +--------------+---------------------+----------------+
    

    自下而上,查询一个地点的所属城市和地区
    创建函数

    CREATE DEFINER=`root`@`%` FUNCTION `findtest_up`(rootId INT) RETURNS varchar(4000) CHARSET utf8
        DETERMINISTIC
    BEGIN
      DECLARE sTemp VARCHAR(4000);
      DECLARE sTempChd VARCHAR(4000);
      
      SET sTemp = '$';
      SET sTempChd = CAST(rootId as CHAR);
      SET sTemp = CONCAT(sTemp,',',sTempChd);
      
      SELECT parent_id INTO sTempChd FROM recursion_test WHERE id = sTempChd;
      WHILE sTempChd <> 0 DO
        SET sTemp = CONCAT(sTemp,',',sTempChd);
        SELECT parent_id INTO sTempChd FROM recursion_test WHERE id = sTempChd;
      END WHILE;
      RETURN sTemp;
    END
    
    

    试着做几次查询

    mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_up(39));
    +--------------+---------------------+----------------+
    | id | parent_id | name |
    +--------------+---------------------+----------------+
    | 3 | 0 | 安徽省 |
    | 12 | 3 | 池州市 |
    | 39 | 12 | 青阳 |
    +--------------+---------------------+----------------+
    
    mysql>SELECT * FROM recursion_test WHERE FIND_IN_SET(id,findtest_up(30));
    +--------------+---------------------+----------------+
    | id | parent_id | name |
    +--------------+---------------------+----------------+
    | 2 | 0 | 江苏省 |
    | 9 | 2 | 徐州市 |
    | 30 | 9 | 泉山区 |
    +--------------+---------------------+----------------+
    
    展开全文
  • mysql实现递归查询的方法:首先创建表,并初始化数据;然后向下递归,利用【find_in_set()】函数和【group_concat()】函数实现递归查询。 本教程操作环境:windows7系统、mysql8.0.22版,该方法适用于所有品牌电脑。...

    mysql实现递归查询的方法:首先创建表,并初始化数据;然后向下递归,利用【find_in_set()】函数和【group_concat()】函数实现递归查询。



    本教程操作环境:windows7系统、mysql8.0.22版,该方法适用于所有品牌电脑。

    免费学习推荐:mysql教程(视频)

    mysql实现递归查询的方法:

    1、创建表

    1

    2

    3

    4

    5

    6

    7

    8

    9

    DROP TABLE IF EXISTS `t_areainfo`;

    CREATE TABLE `t_areainfo` (

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

     `level` int(11) DEFAULT '0',

     `name` varchar(255) DEFAULT '0',

     `parentId` int(11) DEFAULT '0',

     `status` int(11) DEFAULT '0',

     PRIMARY KEY (`id`)

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

    2、初始数据

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    24

    25

    26

    27

    28

    29

    30

    31

    32

    33

    34

    35

    36

    37

    38

    39

    40

    41

    42

    43

    44

    45

    46

    47

    48

    49

    50

    51

    52

    53

    54

    55

    56

    57

    58

    59

    60

    61

    62

    63

    64

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    3、向下递归

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



    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    DROP FUNCTION IF EXISTS queryChildrenAreaInfo;

    DELIMITER ;;

    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

    ;;

    DELIMITER ;

    4、调用方式

    1

    SELECT queryChildrenAreaInfo(1);



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

    1

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

    5、向上递归

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;

    DELIMITER;;

    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

    ;;

    DELIMITER ;

    6、调用方式

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

    1

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



    相关免费推荐:编程视频课程

    展开全文
  • 主要介绍了根据递归到迭代转化的思路,利用SQL实现MYSQL递归查询,需要的朋友可以参考下
  • MySQL 如何实现递归查询

    万次阅读 多人点赞 2020-09-09 11:38:00
    点击上方IT牧场,选择置顶或者星标技术干货每日送达!前言最近在做的业务场景涉及到了数据库的递归查询。我们公司用的 Oracle ,众所周知,Oracle 自带有递归查询的功能,所以...

    点击上方 IT牧场 ,选择 置顶或者星标

    技术干货每日送达!


    前言

    最近在做的业务场景涉及到了数据库的递归查询。我们公司用的 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 递归,就是用的这张表。

    图1

    另外,在这之前,我们需要复习一下几个 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条数据来拼接,现在不生效了。

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

    若本文对你有用,欢迎关注我,给我点赞吧 ~

    干货分享

    最近将个人学习笔记整理成册,使用PDF分享。关注我,回复如下代码,即可获得百度盘地址,无套路领取!

    •001:《Java并发与高并发解决方案》学习笔记;•002:《深入JVM内核——原理、诊断与优化》学习笔记;•003:《Java面试宝典》•004:《Docker开源书》•005:《Kubernetes开源书》•006:《DDD速成(领域驱动设计速成)》•007:全部•008:加技术群讨论

    近期热文

    LinkedBlockingQueue vs ConcurrentLinkedQueue解读Java 8 中为并发而生的 ConcurrentHashMapRedis性能监控指标汇总最全的DevOps工具集合,再也不怕选型了!微服务架构下,解决数据库跨库查询的一些思路聊聊大厂面试官必问的 MySQL 锁机制

    关注我

    喜欢就点个"在看"呗^_^

    展开全文
  • 前言 对于数据库中的树形结构数据,如...oracle实现递归查询的话,就可以使用start with … connect by connect by递归查询基本语法是: select 1 from 表格 start with … connect by prior id = pId start with
  • 主要介绍了MySQL通过自定义函数实现递归查询父级ID或者子级ID,本文给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
  • 递归查询用于查询树形结构的列表,比如行政区列表。包括向下递归查询:根据父级查询子级;...MySQL递归查询 首先创建一张表 DROP TABLE IF EXISTS `tb_base_region`; CREATE TABLE `tb_base_region` ( `PKID` ...
  • mysql实现递归查询的方法:首先创建表,并初始化数据;然后向下递归,利用【find_in_set()】函数和【group_concat()】函数实现递归查询。 本教程操作环境:windows7系统、mysql8.0.22版,该方法适用于所有品牌电脑。...
  • 本程序写了两个sql存储过程,子节点查询算是照搬了,父节点查询是逆思维弄的
  • mysql实现递归查询sql及实际案例

    千次阅读 2019-07-02 13:46:29
    一、递归查询某个部门的所有子部门 : SELECT * FROM (SELECT id, parent_id, dept_code, dept_name FROM tb_dept WHERE parent_id IS NOT NULL) rd,(SELECT @pid := 3) pd where FIND_IN_SET( parent_id, @...
  • mysql 中的数据实现递归查询

    千次阅读 2017-03-10 15:18:04
    一. 表结构 二. create procedure Pro_GetUnderOrge5(in idd varchar(36)) begin declare lev int; set lev=1; drop table if exists tmp1... CREATE TABLE tmp1(vid VARCHAR(5),vname varchar(10),vpid var
  • mybatis+mysql递归查询

    2018-01-02 22:49:00
    mybatis+mysql实现递归查询: <!-- 查询所有,使用resultMap返回结果集,将数据组装成树形结构 --> select from sys_menu pid = 0; <!-- 根据PID查询菜单 --> ...
  • Mysql 实现树状递归查询

    千次阅读 2013-11-15 10:39:56
    在Oracle 中我们知道有一个 ...在MySQL中如果是有限的层次,比如我们事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4,则我们可以直接通过left join 来实现。    ...
  • mybatis MySQL 递归查询

    千次阅读 2018-03-03 13:44:46
    --查询所有,使用resultMap返回结果集,将数据组装成树形结构 --> select * from menu where menu_level='1' <!-- 返回结果集 --> select="findMenuByparentId"> <!-- ...
  • 对于数据库中的树形结构数据,我们经常会有一种...接下来,我将介绍如何在MySql中使用函数来实现递归。 1.创建表 DROP TABLE IF EXISTS `address`; CREATE TABLE `address` ( `id` bigint(20) AUTO_INCREMENT CO...
  • MySql递归查询实现父子查询

    千次阅读 2019-11-25 19:08:33
    父子查询 根据父id查询下面所有子节点数据。 功能需求 我们这里以sec_org(部门表)为例,在一个部门表里面包含org_id(子部门id)、...我们可以通过Mysql函数的递归调用来实现。 1.创建函数getChildrenOrg。 CRE...
  • 在Oracle中,能够通过语法轻松实现父子级间的递归查询,无论到父,子,孙,曾孙等多少级,都能查出来。 但是在mysql中,就没有像Oracle中那样有现成的语法直接调用了。 本篇文章里,我将带大家了解一种在sql中设置...
  • MySQL实现递归查询

    千次阅读 2019-02-18 15:46:23
    MySQL实现递归查询 对于数据库中的树形结构数据,如部门表,有时候,我们需要知道某部门的所有下属部分或者某部分的所有上级部门,这时候就需要用到mysql的递归查询 1、创建表   DROP TABLE IF EXISTS `t_...
  • mysql实现递归查询---使用存储过程

    千次阅读 2017-12-28 18:15:23
    说明:mysql不支持WITH RECURSIVE,不能像PostgreSQL数据库那样查询,需要使用到存储过程 1、创建表:DROP TABLE IF EXISTS `t_areainfo`; CREATE TABLE `t_areainfo` ( `id` int(11) NOT '0' AUTO_INCREMENT, `...
  • 主要介绍了php+mysql不用递归实现的无限级分类实例,重点在不使用递归,需要的朋友可以参考下
  • 一般大家都喜欢在mysql中写递归函数查询子节点如下:   CREATE DEFINER = `mysql_xxx`@`%` FUNCTION `NewProc`(pid VARCHAR(40), holdPid INT) RETURNS varchar(20000) BEGIN #根据指定ID获取下级模块所有节点...
  • mysql 递归查询 树型结构 代码逻辑
  • Mysql快速实现递归树状查询 【前言】今天一个好朋友问我的这个问题,以前也没有用到过,恰好有时间,就帮他研究了一下,纯属“现学现卖”,正好在过程中,自己也能学习一下!个人感觉,其实一点也不难,不过是“闻...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 34,968
精华内容 13,987
关键字:

mysql如何实现递归查询

mysql 订阅