Mysql树结构查询，通过递归查询实现

朴灿烈づ我的快乐病毒、 2022-01-26 17:37 894阅读 0赞

## 废话不多说，以下是表结构： ##

CREATE DATABASE /*!32312 IF NOT EXISTS*/`iot-web` /*!40100 DEFAULT CHARACTER SET utf8 */;
    
    USE `iot-web`;
    
    /*Table structure for table `test` */
    
    DROP TABLE IF EXISTS `test`;
    
    CREATE TABLE `test` (
      `id` varchar(32) NOT NULL,
      `name` varchar(50) DEFAULT NULL,
      `pid` varchar(32) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    /*Data for the table `test` */
    
    insert  into `test`(`id`,`name`,`pid`) values ('1','中国','0'),('10','XX路','9'),('11','XX巷','10'),('12','XX号','11'),('13','XX的家','12'),('14','XX家中的卧室','13'),('15','XX家中卧室里的床','14'),('2','江苏省','1'),('3','山东省','1'),('4','徐州市','2'),('5','济南市','3'),('6','新沂市','4'),('7','历下区','5'),('8','XX街道','6'),('9','XX村','8');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmthaXhp_size_16_color_FFFFFF_t_70]  
我想查询江苏省下的所有城市地区；  
mysql中没有connect by，所以只能通过存储过程+临时表的方式来解决。  
如果层级比较少并且是固定的，那么通过内连接直接可以实现，  
我们这里的方式适用于多层级、并且层级不确定的情况；

首先创建存储过程；

DROP PROCEDURE IF EXISTS `findOrgChildList` ;
    
    DELIMITER ;;
    
    CREATE DEFINER = `root` @`%` PROCEDURE `findOrgChildList` (IN pid VARCHAR (32)) 
    BEGIN
      DECLARE oid VARCHAR (32) ;
       DECLARE oname VARCHAR (100) ;
      DECLARE opid VARCHAR (32) ;
      DECLARE done INTEGER DEFAULT 0 ;
      
     DECLARE C_org CURSOR FOR 
      SELECT 
        t.id,
        t.name,
        t.pid
      FROM
        test t 
      WHERE t.pid = pid ;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
      SET @@max_sp_recursion_depth = 10 ;
      -- 传入的组织id写入临时表
     
      OPEN C_org ;
      FETCH C_org INTO oid, oname, opid ;
      INSERT INTO temp_org 
      VALUES
        (oid, oname, opid) ;
      
      WHILE
        (done = 0) DO -- 递归调用，查找下级
     CALL findOrgChildList (oid) ;
        FETCH C_org INTO oid, oname, opid ;
        
      END WHILE ;
      CLOSE C_org ;
      
    END ;;

然后再创建一个临时表的存储过程；

DELIMITER ;
    
    DROP PROCEDURE IF EXISTS `findOrgList`;
    DELIMITER ;;
    CREATE DEFINER=`root`@`%` PROCEDURE `findOrgList`(IN pid VARCHAR(32))
    BEGIN
      DROP TEMPORARY TABLE IF EXISTS temp_org;
        -- 创建临时表
        CREATE TEMPORARY TABLE temp_org(oid VARCHAR(32), oname VARCHAR(100), opid VARCHAR(32));
        -- 清空临时表数据
        DELETE FROM temp_org;
        -- 发起调用
        CALL findOrgChildList(pid);
        
    END ;;
    DELIMITER ;

至此就ok了，执行call调用存储过程即可。

CALL findOrgList('2');

然后查询临时表中的数据

SELECT * FROM temp_org;

结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmthaXhp_size_16_color_FFFFFF_t_70 1]

## PS：如果层级较多，请确定存储过程创建语句中的深度值设置正确。 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmthaXhp_size_16_color_FFFFFF_t_70 2]

## 深度越多越大，需要消耗的mysql的线程栈的大小就越大，32位的mysql默认thread\_stack位128k，64位的默认为256k，如果深度很大就需要调整thread\_stack的大小。 ##

可以通过以下sql查询thread\_stack值

SHOW VARIABLES WHERE `variable_name` = 'thread_stack';

![在这里插入图片描述][20190529171020134.png]  
调整thread\_stack的值：  
在mysql配置文件中添加以下配置即可：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmthaXhp_size_16_color_FFFFFF_t_70 3]  
linux修改\*.cnf

> 完。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmthaXhp_size_16_color_FFFFFF_t_70]: /images/20220126/4a0f2b62c7474b6bb89eee93605d49ad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmthaXhp_size_16_color_FFFFFF_t_70 1]: /images/20220126/323b30048af74625a24d7a6932d6394a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmthaXhp_size_16_color_FFFFFF_t_70 2]: /images/20220126/7c2367aec0c943ce8c5577c42459813f.png
[20190529171020134.png]: /images/20220126/40b44580c2c64c1d966738f729b9c708.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmthaXhp_size_16_color_FFFFFF_t_70 3]: /images/20220126/7324c355165f476fbdb9f39946d184c4.png