MySQL不允许在递归公共表达式中使用LIMIT。

Question

MySQL不允许在递归公共表达式中使用LIMIT。

3

我的目标是使用最新的MySQL WITH RECURSIVE方法构建一棵树。

我的表名为“categories”，其中包含2行：“ID”和“parentID”。

我的类别表：

 . ID . | ParentID   
--------|----------
 . 1  . | null  
 . 2  . |  1
 . 3  . |  1  
 . 4  . |  1
 . 6  . |  1
 . 7  . |  1
 . 8  . |  1
 . 9  . |  1
 . 10 . |  1
 . 11 . |  13
 . 12 . |  14
 . 13 . |  12     
 .... . | ...

从2到9的ID都有相同的父级，该父级的ID为1。我试图通过在递归公共表达式的第二个SELECT查询中提供 "LIMIT 5" 来限制这种情况。

上述表格在树形结构中的光学表示将类似于以下内容：我的问题是限制相同层级的子项数量（如下图所示标记为Item Y）。

+ Item X .............. (level 1)       
  + Item Y .............. (level 2)  
  + Item Y .............. (level 2)   
  + Item Y .............. (level 2) 
  + .... LIMIT to 5 Items 
+ Item X
    + Item X
      + Item X
         + Item X
             + Item X  
+ Item X

这是我的 mySQL 递归公共表达式查询，其中带有 LIMIT 子句导致问题：

WITH RECURSIVE cte AS
(
  SELECT ID, 0 AS depth, CAST(ID AS CHAR(200)) AS path
    FROM categories WHERE parentID = 1
  UNION ALL
  SELECT c.ID, cte.depth+1, CONCAT(cte.path, ',', c.ID)
    FROM categories c 
    JOIN cte ON cte.ID = c.parentID
    WHERE FIND_IN_SET(c.ID, cte.path)=0 AND depth <= 10
    LIMIT 5
)

 SELECT * FROM cte

从逻辑上讲，我希望通过在CTE的第二个SELECT语句中使用LIMIT子句来限制返回的行数，从而解决我的问题。但是它给了我一个错误：

This version of MySQL doesn't yet support 'ORDER BY / LIMIT over UNION in recursive Common Table Expression'

请注意，我正在使用MySQL 8.0+版本。我知道错误很明显，但是如果有100万个子级在同一个父级下面呢？这将会冻结系统！

我非常感谢任何解决方法。

谢谢。

- Petros Nicolaou

好问题！+1 - The Impaler

尽管SQL标准可以实现您想要的高效检索，但不幸的是，MySQL目前尚未实现此功能。 - The Impaler

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- GMB · Answer 1

如果我理解你的意思正确，row_number() 可以实现你想要的功能。思路是在递归部分枚举 categories 行，然后筛选出前五行：

with recursive cte as (
    select id, 0 as depth, cast(id as char(200)) as path
    from categories 
    where parentid = 1
    union all
    select c.id, cte.depth+1, concat(cte.path, ',', c.id)
    from cte
    inner join (
        select c.*, row_number() over(partition by c.parentid order by c.id) rn
        from categories c 
    ) c on cte.id = c.parentid
    where find_in_set(c.id, cte.path) = 0 and depth <= 10 and c.rn <= 5
)
select * from cte

你可以通过预过滤数据集来进行优化：

with recursive 
    cats as (
        select *
        from (
            select c.*, row_number() over(partition by parentid order by id) rn
            from categories c 
        ) t
        where rn <= 5
    ),
    cte as (
        select id, 0 as depth, cast(id as char(200)) as path
        from cats 
        where parentid = 1
        union all
        select c.id, cte.depth+1, concat(cte.path, ',', c.id)
        from cte
        inner join cats c on cte.id = c.parentid
        where find_in_set(c.id, cte.path) = 0 and depth <= 10 and c.rn <= 5
    )
select * from cte