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

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

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

我想查询江苏省下的所有城市地区；
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;

结果如下：

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

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

可以通过以下sql查询thread_stack值

SHOW VARIABLES WHERE `variable_name` = 'thread_stack';

调整thread_stack的值：
在mysql配置文件中添加以下配置即可：

linux修改*.cnf

完。