Mysql parent-child structure sorting

In the project, the Parent-Child structure is often displayed. Different databases have different writing methods. For example, with uses the with union method, while Mysql does not have such a convenient statement.

In the following category table, foods include pizaa, buger, and coffee, while pizza is divided into several types of cheese. How can they express their parent-child structure?

CREATE TABLE category(    id INT(10),    parent_id INT(10),    name VARCHAR(50));INSERT INTO category (id, parent_id, name) VALUES(1, 0, 'pizza'),        --node 1(2, 0, 'burger'),       --node 2(3, 0, 'coffee'),       --node 3(4, 1, 'piperoni'),     --node 1.1(5, 1, 'cheese'),       --node 1.2(6, 1, 'vegetariana'),  --node 1.3(7, 5, 'extra cheese'); --node 1.2.1

 

One person on stackoverflow gave a good solution:

1. Create a function

Delimiter ~ Drop function getPriority ~ Create function getPriority (inID INT) returns varchar (255) DETERMINISTICbegin DECLARE gParentID int default 0; DECLARE gPriority VARCHAR (255) DEFAULT ''; SET gPriority = inID; SELECT parent_id INTO gParentID FROM category where id = inID; WHILE gParentID> 0 DO/* 0 is the root */SET gPriority = CONCAT (gParentID ,'. ', gPriority); SELECT parent_id INTO gParentID FROM category where id = gParentID; end while; RETURN gPriority; end ~ Delimiter;

2. The result of calling the function is the result of sorting out the order.

SELECT * FROM category ORDER BY getPriority(ID);

☆The restriction of the getPriority function is that all data tracing must have a unique ancestor. The tree structure cannot contain multiple trees.