树MySQL8 / MariaDB10中的递归查询

Question

我在我的数据库中有一个树状的分类结构：

create table cat(
     id int auto_increment primary key,
     url varchar(50),
     parent int references cat(id)
)

如何通过连接类别的URL和父url来构建类别的url？(直到parent不为空)

我使用的是MariaDB 10.2

Answer 1

这个递归的CTE将在表中的每个级别形成一个URL，并使用一个/将它们连接起来(尽管这很容易更改)。所需的最终节点在CTE中指定(在本例中为7)：

WITH RECURSIVE urls AS (
  SELECT url, parent
  FROM cat
  WHERE id = 7
  UNION ALL
  SELECT CONCAT_WS('/', cat.url, urls.url), cat.parent
  FROM urls
  JOIN cat ON cat.id = urls.parent
)
SELECT CONCAT('http://', url) AS url
FROM urls
WHERE parent IS NULL

Demo on dbfiddle

您可以通过从CTE中省略WHERE id =条件来获取所有可用路径。对于我的样本数据，这提供了：

url
http://home
http://home/apps
http://home/user
http://home/apps/excel
http://home/apps/word
http://home/user/nick
http://home/user/nick/docs

Demo on dbfiddle