Example Analysis of mysql Tree query
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!
