Đối với MySQL 8+: sử dụng withcú pháp đệ quy .
Đối với MySQL 5.x: sử dụng các biến nội tuyến, ID đường dẫn hoặc tự tham gia.

MySQL 8+

with recursive cte (id, name, parent_id) as (
  select     id,
             name,
             parent_id
  from       products
  where      parent_id = 19
  union all
  select     p.id,
             p.name,
             p.parent_id
  from       products p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;

Giá trị được chỉ định trong parent_id = 19phải được đặt thành giá trị idgốc mà bạn muốn chọn tất cả các giá trị con của.

MySQL 5.x

Đối với các phiên bản MySQL không hỗ trợ Biểu thức Bảng Chung (lên đến phiên bản 5.7), bạn sẽ đạt được điều này với truy vấn sau:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

Đây là một trò đùa .

Ở đây, giá trị được chỉ định trong @pv := '19'phải được đặt thành giá trị idgốc mà bạn muốn chọn tất cả các con của.

Điều này cũng sẽ hoạt động nếu cha mẹ có nhiều con. Tuy nhiên, yêu cầu mỗi bản ghi phải đáp ứng điều kiện parent_id < id, nếu không kết quả sẽ không đầy đủ.

Các phép gán biến bên trong một truy vấn

Truy vấn này sử dụng cú pháp MySQL cụ thể: các biến được gán và sửa đổi trong quá trình thực thi của nó. Một số giả định được đưa ra về thứ tự thực hiện:

• Các fromkhoản nào được tính trước. Vì vậy, đó là nơi @pvđược khởi tạo.
• Các wherekhoản được đánh giá cho mỗi bản ghi theo thứ tự thu hồi từ các frombí danh. Vì vậy, đây là nơi đặt một điều kiện để chỉ bao gồm các bản ghi mà cây mẹ đã được xác định là ở trong cây con (tất cả con cháu của cây mẹ chính được thêm dần vào @pv).
• Các điều kiện trong điều wherekhoản này được đánh giá theo thứ tự và việc đánh giá bị gián đoạn khi kết quả tổng thể đã chắc chắn. Do đó, điều kiện thứ hai phải ở vị trí thứ hai, vì nó thêm idvào danh sách cha và điều này chỉ xảy ra nếu idđiều kiện đầu tiên vượt qua. Các lengthchức năng được chỉ gọi là để đảm bảo điều kiện này luôn là sự thật, ngay cả khi pvchuỗi sẽ vì một lý do mang lại một giá trị falsy.

Nhìn chung, người ta có thể thấy những giả định này quá rủi ro để dựa vào. Các tài liệu cảnh báo:

bạn có thể nhận được kết quả mà bạn mong đợi, nhưng điều này không được đảm bảo [...] thứ tự đánh giá cho các biểu thức liên quan đến biến người dùng là không xác định.

Vì vậy, mặc dù nó hoạt động nhất quán với truy vấn ở trên, thứ tự đánh giá vẫn có thể thay đổi, chẳng hạn như khi bạn thêm điều kiện hoặc sử dụng truy vấn này như một dạng xem hoặc truy vấn phụ trong một truy vấn lớn hơn. Đây là một "tính năng" sẽ bị loại bỏ trong bản phát hành MySQL trong tương lai :

Các bản phát hành trước của MySQL cho phép gán giá trị cho một biến người dùng trong các câu lệnh khác SET. Chức năng này được hỗ trợ trong MySQL 8.0 để tương thích ngược nhưng có thể bị loại bỏ trong bản phát hành MySQL trong tương lai.

Như đã nêu ở trên, từ MySQL 8.0 trở đi, bạn nên sử dụng withcú pháp đệ quy .

Hiệu quả

Đối với các tập dữ liệu rất lớn, giải pháp này có thể bị chậm, vì find_in_setthao tác này không phải là cách lý tưởng nhất để tìm một số trong danh sách, chắc chắn không phải trong danh sách đạt kích thước theo cùng thứ tự độ lớn với số bản ghi được trả về.

Phương án 1: with recursive,connect by

Ngày càng nhiều cơ sở dữ liệu triển khai cú pháp tiêu chuẩn ISO SQL: 1999WITH [RECURSIVE] cho các truy vấn đệ quy (ví dụ: Postgres 8.4+ , SQL Server 2005+ , DB2 , Oracle 11gR2 + , SQLite 3.8.4+ , Firebird 2.1+ , H2 , HyperSQL 2.1.0+ , Teradata , MariaDB 10.2.2+ ). Và kể từ phiên bản 8.0, MySQL cũng hỗ trợ nó . Xem phần trên cùng của câu trả lời này để biết cú pháp sử dụng.

Một số cơ sở dữ liệu có cú pháp thay thế, không chuẩn để tra cứu thứ bậc, chẳng hạn như CONNECT BYmệnh đề có sẵn trên Oracle , DB2 , Informix , CUBRID và các cơ sở dữ liệu khác.

MySQL phiên bản 5.7 không cung cấp một tính năng như vậy. Khi công cụ cơ sở dữ liệu của bạn cung cấp cú pháp này hoặc bạn có thể chuyển sang một cú pháp có, thì đó chắc chắn là lựa chọn tốt nhất để sử dụng. Nếu không, sau đó cũng xem xét các lựa chọn thay thế sau đây.

Thay thế 2: Số nhận dạng kiểu đường dẫn

Mọi thứ trở nên dễ dàng hơn rất nhiều nếu bạn gán idcác giá trị chứa thông tin phân cấp: một đường dẫn. Ví dụ: trong trường hợp của bạn, nó có thể trông như thế này:

Sau đó, của bạn selectsẽ giống như thế này:

select  id,
        name 
from    products
where   id like '19/%'

Phương án 3: Tự tham gia lặp lại

Nếu bạn biết giới hạn trên cho mức độ sâu của cây phân cấp, bạn có thể sử dụng sqltruy vấn tiêu chuẩn như sau:

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;

Xem trò chơi này

Điều wherekiện chỉ định cha mẹ nào bạn muốn truy xuất con cháu của. Bạn có thể mở rộng truy vấn này với nhiều cấp hơn nếu cần.

Từ blog Quản lý dữ liệu phân cấp trong MySQL

Cấu trúc bảng

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

Truy vấn:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

Đầu ra

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

Hầu hết người dùng lúc này hay lúc khác đã xử lý dữ liệu phân cấp trong cơ sở dữ liệu SQL và chắc chắn rằng việc quản lý dữ liệu phân cấp không phải là mục đích của cơ sở dữ liệu quan hệ. Các bảng của cơ sở dữ liệu quan hệ không phân cấp (như XML), mà chỉ đơn giản là một danh sách phẳng. Dữ liệu phân cấp có mối quan hệ cha-con không được biểu diễn một cách tự nhiên trong bảng cơ sở dữ liệu quan hệ. Đọc thêm

Tham khảo blog để biết thêm chi tiết.

BIÊN TẬP:

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv

Đầu ra:

category_id name    parent
19  category1   0
20  category2   19
21  category3   20
22  category4   21

Tham khảo: Cách thực hiện truy vấn SELECT đệ quy trong Mysql?

Thử những thứ này xem:

Định nghĩa bảng:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    parent_id INT,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category (id)
) engine=innodb;

Các hàng thử nghiệm:

INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);

Thủ tục lưu trữ đệ quy:

DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(20);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, '/', catname);
    END IF;
END$$
DELIMITER ;

Chức năng gói cho thủ tục được lưu trữ:

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(cat_id, res);
    RETURN res;
END$$
DELIMITER ;

Chọn ví dụ:

SELECT id, name, getpath(id) AS path FROM category;

Đầu ra:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1                               |
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA                     |
| 24 | categoryB | category1/categoryA/categoryB           |
| 25 | categoryC | category1/categoryA/categoryC           |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+

Lọc các hàng với đường dẫn nhất định:

SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';

Đầu ra:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+

Đã làm điều tương tự cho một hàng đợi khác ở đây

Mysql chọn đệ quy nhận tất cả các con với nhiều cấp độ

Truy vấn sẽ là:

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
  SELECT @pv:=(
    SELECT GROUP_CONCAT(id SEPARATOR ',')
    FROM table WHERE parent_id IN (@pv)
  ) AS lv FROM table 
  JOIN
  (SELECT @pv:=1)tmp
  WHERE parent_id IN (@pv)
) a;

Cách tiếp cận tốt nhất mà tôi nghĩ ra là

1. Sử dụng dòng dõi để lưu trữ \ sắp xếp \ theo dõi cây. Như vậy là quá đủ và hiệu quả đọc nhanh hơn hàng nghìn lần so với bất kỳ cách tiếp cận nào khác. Nó cũng cho phép duy trì mẫu đó ngay cả khi DB sẽ thay đổi (vì BẤT KỲ db nào sẽ cho phép sử dụng mẫu đó)
2. Sử dụng hàm xác định dòng dõi cho ID cụ thể.
3. Sử dụng nó như bạn muốn (trong các lựa chọn, hoặc hoạt động CUD, hoặc thậm chí theo công việc).

Phương pháp tiếp cận dòng dõi descr. có thể được tìm thấy ở bất cứ đâu, ví dụ Ở đây hoặc ở đây . Về chức năng - đó là điều khiến tôi bị cuốn hút.

Cuối cùng - ít nhiều đã có giải pháp đơn giản, tương đối nhanh và ĐƠN GIẢN.

Cơ quan chức năng

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
    READS SQL DATA
BEGIN

 DECLARE v_rec INT DEFAULT 0;

 DECLARE done INT DEFAULT FALSE;
 DECLARE v_res text DEFAULT '';
 DECLARE v_papa int;
 DECLARE v_papa_papa int DEFAULT -1;
 DECLARE csr CURSOR FOR 
  select _id,parent_id -- @n:=@n+1 as rownum,T1.* 
  from 
    (SELECT @r AS _id,
        (SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := the_id, @l := 0,@n:=0) vars,
        table m
    WHERE @r <> 0
    ) T1
    where T1.parent_id is not null
 ORDER BY T1.lvl DESC;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open csr;
    read_loop: LOOP
    fetch csr into v_papa,v_papa_papa;
        SET v_rec = v_rec+1;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- add first
        IF v_rec = 1 THEN
            SET v_res = v_papa_papa;
        END IF;
        SET v_res = CONCAT(v_res,'-',v_papa);
    END LOOP;
    close csr;
    return v_res;
END

Và sau đó bạn chỉ

select get_lineage(the_id)

Hy vọng nó sẽ giúp ai đó :)

Nếu bạn cần tốc độ đọc nhanh, lựa chọn tốt nhất là sử dụng bảng đóng. Một bảng đóng chứa một hàng cho mỗi cặp tổ tiên / con cháu. Vì vậy, trong ví dụ của bạn, bảng đóng sẽ giống như

ancestor | descendant | depth
0        | 0          | 0
0        | 19         | 1
0        | 20         | 2
0        | 21         | 3
0        | 22         | 4
19       | 19         | 0
19       | 20         | 1
19       | 21         | 3
19       | 22         | 4
20       | 20         | 0
20       | 21         | 1
20       | 22         | 2
21       | 21         | 0
21       | 22         | 1
22       | 22         | 0

Khi bạn có bảng này, các truy vấn phân cấp trở nên rất dễ dàng và nhanh chóng. Để có được tất cả con cháu của loại 20:

SELECT cat.* FROM categories_closure AS cl
INNER JOIN categories AS cat ON cat.id = cl.descendant
WHERE cl.ancestor = 20 AND cl.depth > 0

Tất nhiên, có một nhược điểm lớn bất cứ khi nào bạn sử dụng dữ liệu không chuẩn hóa như thế này. Bạn cần duy trì bảng đóng cùng với bảng danh mục của mình. Cách tốt nhất có lẽ là sử dụng trình kích hoạt, nhưng hơi phức tạp để theo dõi chính xác các lần chèn / cập nhật / xóa cho các bảng đóng. Đối với bất kỳ điều gì, bạn cần phải xem xét các yêu cầu của mình và quyết định cách tiếp cận nào là tốt nhất cho bạn.

Chỉnh sửa : Xem câu hỏi Các tùy chọn để lưu trữ dữ liệu phân cấp trong cơ sở dữ liệu quan hệ là gì? để có nhiều lựa chọn hơn. Có các giải pháp tối ưu khác nhau cho các tình huống khác nhau.

Truy vấn đơn giản để liệt kê con của đệ quy đầu tiên:

select @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where parent_id=@pv

Kết quả:

id  name        parent_id
20  category2   19
21  category3   20
22  category4   21
26  category24  22

... với phép nối bên trái:

select
    @pv:=p1.id as id
  , p2.name as parent_name
  , p1.name name
  , p1.parent_id
from products p1
join (select @pv:=19)tmp
left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
where p1.parent_id=@pv

Giải pháp của @tincot để liệt kê tất cả trẻ em:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id)

Kiểm tra nó trực tuyến với Sql Fiddle và xem tất cả kết quả.

http://sqlfiddle.com/#!9/a318e3/4/0

Bạn có thể thực hiện như vậy trong các cơ sở dữ liệu khác khá dễ dàng với một truy vấn đệ quy (YMMV về hiệu suất).

Cách khác để làm điều đó là lưu trữ hai bit dữ liệu bổ sung, một giá trị bên trái và bên phải. Giá trị bên trái và bên phải được lấy từ một trình tự duyệt trước của cấu trúc cây mà bạn đang đại diện.

Điều này được gọi là Truyền tải cây đặt hàng trước đã sửa đổi và cho phép bạn chạy một truy vấn đơn giản để nhận tất cả các giá trị gốc cùng một lúc. Nó cũng có tên là "tập hợp lồng nhau".

Dựa trên câu trả lời @trincot, được giải thích rất tốt, tôi sử dụng WITH RECURSIVE ()câu lệnh để tạo breadcrumb bằng cách sử dụng idtrang hiện tại và quay ngược lại trong hệ thống phân cấp để tìm mọi thứ parenttrong routebảng của tôi .

Vì vậy, giải pháp @trincot được điều chỉnh ở đây theo hướng ngược lại để tìm cha mẹ thay vì con cháu.

Tôi cũng đã thêm depthgiá trị hữu ích để đảo ngược thứ tự kết quả (nếu không đường dẫn sẽ bị lộn ngược).

WITH RECURSIVE cte (
    `id`,
    `title`,
    `url`,
    `icon`,
    `class`,
    `parent_id`,
    `depth`
) AS (
    SELECT   
        `id`,
        `title`,
        `url`,
        `icon`,
        `class`,
        `parent_id`,
        1 AS `depth` 
    FROM     `route`
    WHERE    `id` = :id
      
    UNION ALL 
    SELECT 
        P.`id`,
        P.`title`,
        P.`url`,
        P.`icon`,
        P.`class`,
        P.`parent_id`,
        `depth` + 1
    FROM `route` P
        
    INNER JOIN cte
        ON P.`id` = cte.`parent_id`
)
SELECT * FROM cte ORDER BY `depth` DESC;

Trước khi nâng cấp lên mySQL 8+, tôi đã sử dụng vars nhưng nó không được dùng nữa và không còn hoạt động trên phiên bản 8.0.22 của tôi !

EDIT 2021-02-19 : Ví dụ cho menu phân cấp

Sau nhận xét của @david, tôi quyết định thử tạo một menu phân cấp đầy đủ với tất cả các nút và được sắp xếp như tôi muốn (với sortingcột sắp xếp các mục theo từng độ sâu). Rất hữu ích cho trang ma trận người dùng / ủy quyền của tôi.

Điều này thực sự đơn giản hóa phiên bản cũ của tôi với một truy vấn trên mỗi độ sâu (các vòng lặp PHP) .

Ví dụ này tích hợp INNER JOIN với urlbảng để lọc tuyến đường theo trang web (hệ thống CMS nhiều trang web).

Bạn có thể thấy pathcột thiết yếu chứa CONCAT()chức năng sắp xếp menu theo đúng cách.

SELECT R.* FROM (
    WITH RECURSIVE cte (
        `id`,
        `title`,
        `url`,
        `icon`,
        `class`,
        `parent`,
        `depth`,
        `sorting`,
        `path`
    ) AS (
        SELECT 
            `id`,
            `title`,
            `url`,
            `icon`,
            `class`,
            `parent`,
            1 AS `depth`,
            `sorting`,
            CONCAT(`sorting`, ' ' , `title`) AS `path`
        FROM `route`
        WHERE `parent` = 0
        UNION ALL SELECT 
            D.`id`,
            D.`title`,
            D.`url`,
            D.`icon`,
            D.`class`,
            D.`parent`,
            `depth` + 1,
            D.`sorting`,
            CONCAT(cte.`path`, ' > ', D.`sorting`, ' ' , D.`title`)
        FROM `route` D
        INNER JOIN cte
            ON cte.`id` = D.`parent`
    )
    SELECT * FROM cte
) R

INNER JOIN `url` U
    ON R.`id` = U.`route_id`
    AND U.`site_id` = 1

ORDER BY `path` ASC  

Chỉ cần sử dụng lớp php BlueM / tree để tạo cây của một bảng tự quan hệ trong mysql.

Tree và Tree \ Node là các lớp PHP để xử lý dữ liệu được cấu trúc phân cấp bằng cách sử dụng các tham chiếu ID mẹ. Ví dụ điển hình là một bảng trong cơ sở dữ liệu quan hệ trong đó trường “cha” của mỗi bản ghi tham chiếu đến khóa chính của bản ghi khác. Tất nhiên, Tree không thể chỉ sử dụng dữ liệu có nguồn gốc từ cơ sở dữ liệu mà là bất cứ thứ gì: bạn cung cấp dữ liệu và Tree sử dụng dữ liệu đó, bất kể dữ liệu đến từ đâu và nó được xử lý như thế nào. đọc thêm

Đây là một ví dụ về việc sử dụng BlueM / tree:

<?php 
require '/path/to/vendor/autoload.php'; $db = new PDO(...); // Set up your database connection 
$stm = $db->query('SELECT id, parent, title FROM tablename ORDER BY title'); 
$records = $stm->fetchAll(PDO::FETCH_ASSOC); 
$tree = new BlueM\Tree($records); 
...

Điều gì đó không được đề cập ở đây, mặc dù hơi giống với giải pháp thay thế thứ hai của câu trả lời được chấp nhận nhưng khác và chi phí thấp cho truy vấn phân cấp lớn và các mục dễ dàng (chèn cập nhật xóa), sẽ thêm một cột đường dẫn liên tục cho mỗi mục.

một số như:

id | name        | path
19 | category1   | /19
20 | category2   | /19/20
21 | category3   | /19/20/21
22 | category4   | /19/20/21/22

Thí dụ:

-- get children of category3:
SELECT * FROM my_table WHERE path LIKE '/19/20/21%'
-- Reparent an item:
UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'

Tối ưu hóa độ dài đường dẫn và ORDER BY pathsử dụng mã hóa base36 thay vì id đường dẫn số thực

 // base10 => base36
 '1' => '1',
 '10' => 'A',
 '100' => '2S',
 '1000' => 'RS',
 '10000' => '7PS',
 '100000' => '255S',
 '1000000' => 'LFLS',
 '1000000000' => 'GJDGXS',
 '1000000000000' => 'CRE66I9S'

https://en.wikipedia.org/wiki/Base36

Đồng thời loại bỏ dấu gạch chéo phân tách '/' bằng cách sử dụng độ dài cố định và phần đệm cho id được mã hóa

Giải thích chi tiết về tối ưu hóa tại đây: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/

LÀM

xây dựng một hàm hoặc thủ tục để phân chia đường dẫn cho tổ tiên truy xuất của một mục

Nó hơi khó một chút, hãy kiểm tra xem nó có phù hợp với bạn không

select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='') as c  having list like '%19,%';

Liên kết fiddle SQL http://www.sqlfiddle.com/#!2/e3cdf/2

Thay thế bằng tên trường và bảng của bạn một cách thích hợp.

Đó là một bảng phân loại .

SELECT  id,
        NAME,
        parent_category 
FROM    (SELECT * FROM category
         ORDER BY parent_category, id) products_sorted,
        (SELECT @pv := '2') initialisation
WHERE   FIND_IN_SET(parent_category, @pv) > 0
AND     @pv := CONCAT(@pv, ',', id)

Đầu ra ::

Điều này hiệu quả với tôi, hy vọng điều này cũng sẽ hiệu quả với bạn. Nó sẽ cung cấp cho bạn một bộ Record Root thành Child cho bất kỳ Menu cụ thể nào. Thay đổi tên trường theo yêu cầu của bạn.

SET @id:= '22';

SELECT Menu_Name, (@id:=Sub_Menu_ID ) as Sub_Menu_ID, Menu_ID 
FROM 
    ( SELECT Menu_ID, Menu_Name, Sub_Menu_ID 
      FROM menu 
      ORDER BY Sub_Menu_ID DESC
    ) AS aux_table 
    WHERE Menu_ID = @id
     ORDER BY Sub_Menu_ID;

Tôi thấy nó dễ dàng hơn để:

1) tạo một hàm sẽ kiểm tra xem một mục có ở bất kỳ đâu trong hệ thống phân cấp chính của một mục khác hay không. Một cái gì đó như thế này (tôi sẽ không viết hàm, hãy tạo nó bằng WHILE DO):

is_related(id, parent_id);

trong ví dụ của bạn

is_related(21, 19) == 1;
is_related(20, 19) == 1;
is_related(21, 18) == 0;

2) sử dụng một lựa chọn phụ, giống như sau:

select ...
from table t
join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id));

Tôi đã thực hiện một truy vấn cho bạn. Điều này sẽ cung cấp cho bạn Danh mục đệ quy với một Truy vấn duy nhất:

SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 WHERE prent is NULL
UNION 
SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL 
UNION 
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL 
UNION 
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL 
ORDER BY NAME,subName,subsubName,subsubsubName

Đây là một trò đùa .