mysql递归自连接相交列

Question

假设我有一个像这样的起始表(派生的)......

-------------------------------------------------------
| UsergroupID | ParentID | PermissionIDs              |
-------------------------------------------------------
| 1           | 0        | 1                          |
| 1           | 0        | 2                          |
| 1           | 0        | 3                          |
| 1           | 0        | 4                          |
| 1           | 0        | 5                          |
| 1           | 0        | 6                          |
| 2           | 1        | 1                          |
| 2           | 1        | 8                          |
| 2           | 1        | 9                          |
| 3           | 1        | 3                          |
| 3           | 1        | 8                          |
| 3           | 1        | 2                          |
-------------------------------------------------------

我希望得到一个像这样的最终结果集

-------------------------------------------------------
| UsergroupID | ParentID | PermissionID               |
-------------------------------------------------------
| 1           | 0        | 1                          |
| 1           | 0        | 2                          |
| 1           | 0        | 3                          |
| 1           | 0        | 4                          |
| 1           | 0        | 5                          |
| 1           | 0        | 6                          |
| 2           | 1        | 1                          |
| 3           | 1        | 3                          |
| 3           | 1        | 2                          |
-------------------------------------------------------

这基本上是对父 ID 进行递归查找，然后交叉(内部连接)PermissionID 列中的值。所以 child 永远不能拥有比 parent 更多的权限。

我已经查找了有关用户定义函数的资料(我想我可以将一个 udf 包裹在一个列周围并让它根据父 ID 递归地相交)但这并没有让我走得太远。我真正能想到的唯一一件事不是在数据库端做，而是用服务器端代码做。

Solarflare -- 这是我刚刚使用您的脚本尝试的...这有效!

delimiter $$
CREATE PROCEDURE prcPermCleanup5()
BEGIN
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (usergroupID INT, parentID INT, StoreID INT)  ENGINE=MEMORY; 
INSERT INTO table1 VALUES
(1,0,1),
(1,0,2),
(1,0,3),
(1,0,4),
(2,1,1),
(2,1,2),
(2,0,5),
(3,2,2),
(3,2,7),
(4,1,1),
(4,1,2),
(5,4,1),
(5,4,8),
(6,2,1),
(6,2,6);
  REPEAT
     DELETE entry.*
    FROM table1 entry
    LEFT JOIN table1 parent
    ON entry.parentID = parent.usergroupID
    AND entry.`StoreID` = parent.StoreID
    WHERE parent.usergroupID IS NULL
    AND NOT entry.parentID = 0;
   UNTIL row_count() = 0 END REPEAT;  
  SELECT * FROM table1;
END $$
delimiter ;

Answer 1

不可能在单个查询中执行此操作(除非可能在某些特殊条件下)，但您可以重复清理查询以通过这种方式进行递归。

如果您的清理是一次性的，您可以多次运行以下查询，直到不再有任何变化(您最多需要 depth of tree - 1 运行):

delete entry.*
from table1 entry
left join table1 parent
on entry.parentID = parent.usergroupID
and entry.permissionIDs = parent.permissionIDs
where parent.usergroupID is null
and not entry.parentID = 0;

您可以在一个过程中自动重复该过程，例如

delimiter $$
create procedure prcPermCleanup()
begin
  repeat
    delete entry.*
    from table1 entry
    left join table1 parent
    on entry.parentID = parent.usergroupID
    and entry.permissionIDs = parent.permissionIDs
    where parent.usergroupID is null
    and not entry.parentID = 0;
  until row_count() = 0 end repeat;  
end $$
delimiter ;

call prcPermCleanup;

作为旁注:

您可能想要规范化您的数据，例如有一个单独的表用于您的权限:

table permissions: usergroupID | permissionID

table tree: usergroupID | parentID 

在您当前的表中，您的表中有多次相同的信息(parentID 是 usergroupID 的父级信息)，也就是非规范化的。这样做的一个实际结果是，对于同一个 usergroupID，您可能有两个不同的 parentID，这在树中通常是未定义的。