mysql中数据的树形_MySQL 中的树形数据处理示例

创建测试表

CREATE TABLE `areatest` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`parent_id` int(11) NOT NULL DEFAULT '0' COMMENT '父ID',

`order_id` int(11) NOT NULL DEFAULT '0' COMMENT '排序ID',

`name` varchar(50) NOT NULL COMMENT '名称',

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='地区';

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,'赣州');

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

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_areatest_child_nodes`$$

/*获取指定 父ID 树形层次数据放到 child_nodes 临时表*/

CREATE PROCEDURE `sp_areatest_child_nodes`(IN $parent_id INT/*父ID*/)

BEGIN

DECLARE LEVEL SMALLINT DEFAULT 0;/*层次*/

DECLARE cou INT DEFAULT 0;

DROP TABLE IF EXISTS child_nodes_temp;/*单层次临时表*/

DROP TABLE IF EXISTS child_nodes;/*层次临时表*/

CREATE TEMPORARY TABLE child_nodes_temp(`id` INT,`level` INT,`order_id` VARCHAR(1000)) DEFAULT CHARSET=utf8;

CREATE TEMPORARY TABLE child_nodes(`id` INT,`level` INT,`order_id` VARCHAR(1000)) DEFAULT CHARSET=utf8;

INSERT INTO child_nodes_temp SELECT `id`,LEVEL,CAST(RIGHT(100+`order_id`,2) AS CHAR) FROM `areatest` WHERE `parent_id`=$parent_id;

SELECT ROW_COUNT() INTO cou;

INSERT INTO child_nodes SELECT * FROM child_nodes_temp;

WHILE cou>0 DO

SET LEVEL=LEVEL+1;

TRUNCATE TABLE child_nodes_temp;

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;

SELECT ROW_COUNT() INTO cou;

INSERT INTO child_nodes SELECT * FROM child_nodes_temp;

END WHILE;

DROP TABLE child_nodes_temp;

END$$

DELIMITER ;

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

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_areatest_tree`$$

/*获取指定 父ID 树形数据*/

CREATE PROCEDURE `sp_areatest_tree`(IN $parent_id INT/*父ID*/)

BEGIN

CALL sp_areatest_child_nodes($parent_id);

SELECT a.id,CASE b.parent_id WHEN 0 THEN b.name ELSE CONCAT(SPACE(a.level*2),'|-',b.name) END AS `name`

FROM child_nodes a INNER JOIN `areatest` b ON a.id=b.id

ORDER BY a.order_id;

DROP TABLE child_nodes;

END$$

DELIMITER ;

测试1

CALL sp_areatest_tree(0);

测试2

CALL sp_areatest_tree(2);