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
完。