MySQL递归CTE层次路径

Question

我有categories表，其中parent_id是自引用foreign key。

categories table:

select *  from categories;

<表类>category_id添加名称parent_idtbody><<tr>1T1空2T2空3T1114病人15T11136T11237T112168T112269T121410T122411T21212T2111113T2121114T211112

您可以在CTE中使用CONCAT(parent_path,separator,child_id)来构建路径。您需要在UNION之前在CTE的第一部分指定'WHERE parent_id IS NULL '，以便我们只包含从根开始的路径。

CREATE TABLE IF NOT EXISTS categories (
category_id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
parent_id int(11) DEFAULT NULL,
created_at timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (category_id),
FOREIGN KEY (parent_id) REFERENCES categories(category_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T1', null);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T2', null);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T11', 1);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T12', 1);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T111', 3);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T112', 3);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T1121', 6);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T1122', 6);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T121', 4);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T122', 4);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T21', 2);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T211', 11);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T212', 11);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T2111', 12);

WITH RECURSIVE cte_name AS (
select category_id, name, parent_id , name AS path from  categories 
where parent_id is null
UNION ALL
select c.category_id,c.name,c.parent_id, concat_ws(' -> ', cte_name.path,  c.name) 
from categories as c
INNER JOIN cte_name  ON cte_name.category_id = c.parent_id  
)
SELECT * FROM cte_name;

category_id | name | parent_id | path----------: | :---- | --------: | :-------------------------1 | T1 |null| T12 | T2 |null| T23 | T11 | 1 | T1 ->T114 | T12 | 1 | T1 ->病人11 | T21 | 2 | T2 ->T215 | T111 | 3 | T1 ->T11→T1116 | T112 | 3 | T1 ->T11→T1129 | T121 | 4 | T1 ->病人→T12110 | T122 | 4 | T1 ->病人→T12212 | T211 | 11 | T2 ->T21→T21113 | T212 | 11 | T2 ->T21→T2127 | T1121 | 6 | T1 ->T11→T112→T11218 | T1122 | 6 | T1 ->T11→T112→T112214 | T2111 | 12 | T2 ->T21→T211→T2111

db<此处小提琴>