MySQL 中的树形数据处理示例

创建测试表

[xhtml]  view plain copy

1. CREATE TABLE `areatest` (  
2.    `id` int(11) NOT NULL AUTO_INCREMENT,  
3.    `parent_id` int(11) NOT NULL DEFAULT '0' COMMENT '父ID',  
4.    `order_id` int(11) NOT NULL DEFAULT '0' COMMENT '排序ID',  
5.    `name` varchar(50) NOT NULL COMMENT '名称',  
6.    PRIMARY KEY (`id`)  
7.  ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='地区';  
8. insert  into `areatest` values (1,0,1,'北京'),(2,0,3,'江西'),(3,0,2,'上海'),(4,2,1,'南昌'),(5,2,2,'九江'),(6,4,1,'永修'),(7,4,2,'星子'),(8,2,3,'赣州');  

 

 

创建树形层次分析存储过程

[xhtml]  view plain copy

1. DELIMITER $$  
2. DROP PROCEDURE IF EXISTS `sp_areatest_child_nodes`$$  
3. /*获取指定 父ID 树形层次数据放到 child_nodes 临时表*/  
4. CREATE PROCEDURE `sp_areatest_child_nodes`(IN $parent_id INT/*父ID*/)  
5. BEGIN  
6.     DECLARE LEVEL SMALLINT DEFAULT 0;/*层次*/  
7.     DECLARE cou INT DEFAULT 0;  
8.     DROP TABLE IF EXISTS child_nodes_temp;/*单层次临时表*/  
9.     DROP TABLE IF EXISTS child_nodes;/*层次临时表*/  
10.     CREATE TEMPORARY TABLE child_nodes_temp(`id` INT,`level` INT,`order_id` VARCHAR(1000)) DEFAULT CHARSET=utf8;  
11.     CREATE TEMPORARY TABLE child_nodes(`id` INT,`level` INT,`order_id` VARCHAR(1000)) DEFAULT CHARSET=utf8;  
12.     INSERT INTO child_nodes_temp SELECT `id`,LEVEL,CAST(RIGHT(100+`order_id`,2) AS CHAR) FROM `areatest` WHERE `parent_id`=$parent_id;  
13.     SELECT ROW_COUNT() INTO cou;  
14.     INSERT INTO child_nodes SELECT * FROM child_nodes_temp;  
15.     WHILE cou>0 DO  
16.         SET LEVEL=LEVEL+1;  
17.         TRUNCATE TABLE child_nodes_temp;  
18.         INSERT INTO child_nodes_temp SELECT a.`id`,LEVEL,CONCAT(b.order_id,CAST(RIGHT(100+a.`order_id`,2) AS CHAR)) FROM `areatest` a,child_nodes b WHERE a.`parent_id`=b.`id` AND b.`level`=LEVEL-1;  
19.         SELECT ROW_COUNT() INTO cou;  
20.         INSERT INTO child_nodes SELECT * FROM child_nodes_temp;  
21.     END WHILE;  
22.     DROP TABLE child_nodes_temp;  
23. END$$  
24. DELIMITER ;  

 

创建树形数据查询存储过程

[xhtml]  view plain copy

1. DELIMITER $$  
2. DROP PROCEDURE IF EXISTS `sp_areatest_tree`$$  
3. /*获取指定 父ID 树形数据*/  
4. CREATE PROCEDURE `sp_areatest_tree`(IN $parent_id INT/*父ID*/)  
5. BEGIN  
6.     CALL sp_areatest_child_nodes($parent_id);  
7.     SELECT a.id,CASE b.parent_id WHEN 0 THEN b.name ELSE CONCAT(SPACE(a.level*2),'|-',b.name) END AS `name`  
8.     FROM child_nodes a INNER JOIN `areatest` b ON a.id=b.id  
9.     ORDER BY a.order_id;  
10.     DROP TABLE child_nodes;  
11.     END$$  
12. DELIMITER ;  

 

 

测试1

CALL sp_areatest_tree(0); 

 

测试2

CALL sp_areatest_tree(2);