Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of mysql tree query", which is easy to understand and clear. I hope it can help you solve your doubts. Let me lead you to study and learn the article "sample Analysis of mysql Tree query".

-- create tables

Drop table if exists t_hierarchy

CREATE TABLE t_hierarchy (

Id int (10) unsigned NOT NULL AUTO_INCREMENT

Parent int (10) unsigned NOT NULL

PRIMARY KEY (id)

KEY ix_hierarchy_parent (parent, id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- create stored procedures to insert data

DROP PROCEDURE if exists prc_fill_hierarchy

DELIMITER $$

CREATE PROCEDURE prc_fill_hierarchy (level INT, fill INT)

BEGIN

DECLARE _ level INT

DECLARE _ fill INT

INSERT

INTO t_hierarchy (id, parent)

VALUES (1,0)

SET _ fill = 0

WHILE _ fill

< fill DO INSERT INTO t_hierarchy (parent) VALUES (1); SET _fill = _fill + 1; END WHILE; SET _fill = 1; SET _level = 0; WHILE _level < level DO INSERT INTO t_hierarchy (parent) SELECT hn.id FROM t_hierarchy ho, t_hierarchy hn WHERE ho.parent = 1 AND hn.id >

_ fill

SET _ level = _ level + 1

SET _ fill = _ fill + POWER (fill, _ level)

END WHILE

END

$

DELIMITER

-- insert data

START TRANSACTION

CALL prc_fill_hierarchy (3,2)

COMMIT

-- A function that creates a spanning tree

DELIMITER $$

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id (value INT) RETURNS INT

NOT DETERMINISTIC

READS SQL DATA

BEGIN

DECLARE _ id INT

DECLARE _ parent INT

DECLARE _ next INT

DECLARE CONTINUE HANDLER FOR NOT FOUND SET @ id = NULL

SET _ parent = @ id

SET _ id =-1

IF @ id IS NULL THEN

RETURN NULL

END IF

LOOP

SELECT MIN (id)

INTO @ id

FROM t_hierarchy

WHERE parent = _ parent

AND id > _ id

IF @ id IS NOT NULL OR _ parent = @ start_with THEN

SET @ level = @ level + 1

RETURN @ id

END IF

SET @ level: = @ level-1

SELECT id, parent

INTO _ id, _ parent

FROM t_hierarchy

WHERE id = _ parent

END LOOP

END

$

DELIMITER

-- generate queries for tree

SELECT CONCAT (REPEAT ('-->', level-1), CAST (hi.id AS CHAR)) AS treeitem, parent, level

FROM (

SELECT hierarchy_connect_by_parent_eq_prior_id (id) AS id, @ level AS level

FROM (

SELECT @ start_with: = 0

@ id: = @ start_with

@ level: = 0

) vars, t_hierarchy

WHERE @ id IS NOT NULL

) ho

JOIN t_hierarchy hi

ON hi.id = ho.id

The above is all the contents of the article "sample Analysis of mysql Tree query". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.