mysql - 防止循环连接、递归搜索

Question

所以在我的情况下，我有三个表:list、item 和 list_relation。

每个 item 都将通过 list_id 外键链接到一个列表。

list_relation 如下所示:

CREATE TABLE list_relation
    (
        parent_id INT UNSIGNED NOT NULL,
        child_id INT UNSIGNED NOT NULL,

        UNIQUE(parent_id, child_id)

        FOREIGN KEY (parent_id)
            REFERENCES list (id)
                ON DELETE CASCADE,    

        FOREIGN KEY (child_id)
            REFERENCES list (id)
                ON DELETE CASCADE
    );

我也希望能够继承多个列表(包括相关项)。

例如我有列表:1、2、3。

我想知道是否有任何 SQL 方法可以防止出现循环关系。例如

List 1 继承自List 3，List 2 继承自List 1，List 3 继承自List 1。

1 -> 2 -> 3 -> 1

我目前的想法是，我必须先验证所需的继承，然后将其插入数据库，以确定它是否是循环的。

Answer 1

如果您使用 MySQL 8.0 或 MariaDB 10.2(或更高版本)，您可以尝试递归 CTE(公用表表达式)。

假设以下架构和数据:

CREATE TABLE `list_relation` (
  `child_id`  int unsigned NOT NULL,
  `parent_id` int unsigned NOT NULL,
  PRIMARY KEY (`child_id`,`parent_id`)
);
insert into list_relation (child_id, parent_id) values
    (2,1),
    (3,1),
    (4,2),
    (4,3),
    (5,3);

现在您尝试使用 child_id = 1 和 parent_id = 4 插入一个新行。但这会产生循环关系(1->4->2->1 和 1->4->3->1)，这是您要避免的。要查明反向关系是否已存在，您可以使用以下查询，它将显示列表 4 的所有父项(包括继承/传递的父项):

set @new_child_id  = 1;
set @new_parent_id = 4;

with recursive rcte as (
  select *
  from list_relation r
  where r.child_id = @new_parent_id
  union all
  select r.*
  from rcte
  join list_relation r on r.child_id = rcte.parent_id
)
select * from rcte

结果是:

child_id | parent_id
       4 |         2
       4 |         3
       2 |         1
       3 |         1

Demo

您可以在结果中看到，列表 1 是列表 4 的父项之一，您不会插入新记录。

因为您只想知道列表 1 是否在结果中，您可以将最后一行更改为

select * from rcte where parent_id = @new_child_id limit 1

或到

select exists (select * from rcte where parent_id = @new_child_id)

顺便说一句:您可以使用相同的查询来防止冗余关系。假设您要插入 child_id = 4 和 parent_id = 1 的记录。这将是多余的，因为 list 4 已经继承了 list 1 而不是 list 2 和 list 3。以下查询将向您展示:

set @new_child_id  = 4;
set @new_parent_id = 1;

with recursive rcte as (
  select *
  from list_relation r
  where r.child_id = @new_child_id
  union all
  select r.*
  from rcte
  join list_relation r on r.child_id = rcte.parent_id
)
select exists (select * from rcte where parent_id = @new_parent_id)

并且您可以使用类似的查询来获取所有继承的项目:

set @list = 4;

with recursive rcte (list_id) as (
  select @list
  union distinct
  select r.parent_id
  from rcte
  join list_relation r on r.child_id = rcte.list_id
)
select distinct i.*
from rcte
join item i on i.list_id = rcte.list_id