防止循环连接、递归搜索 [英] Preventing circular joining, recursive searches
问题描述
所以在我的情况下，我有三个表:list、item 和 list_relation.
So in my situation I have three tables: list, item and list_relation.
每个item将通过list_id外键链接到一个列表.
Each item will be linked to a list through the list_id foreign key.
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
);
我也希望能够从多个列表(包括相关项)继承.
I want to be be able to inherit from multiple lists as well (which includes the related items).
例如我有列表:1、2、3.
For example I have list: 1, 2, 3.
我想知道是否有任何 SQL 方法可以防止出现循环关系.例如
I was wondering if there was any SQL way to prevent there from being a circular relation. E.g.
列表 1 继承自列表 3，列表 2 继承自列表 1，列表 3 继承自列表 1.
List 1 inherits from List 3, List 2 inherits from List 1, List 3 inherits from List 1.
<代码>1 ->2 ->3 ->1
我目前的想法是，我必须先验证所需的继承，然后将其插入数据库，以确定它是否是循环的.
My current idea is that I would have to find out whether it would be circular by validating the desired inheritance first then inserting it into the DB.
推荐答案
如果您使用 MySQL 8.0 或 MariaDB 10.2(或更高版本)，您可以尝试 recursiveCTE(常用表表达式).
If you use MySQL 8.0 or MariaDB 10.2 (or higher) you can try recursive CTEs (common table expressions).
假设有以下架构和数据:
Assuming the following schema and data:
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 的所有父级(包括继承/传递父级):
Now you try to insert a new row with child_id = 1 and parent_id = 4. But that would create cyclic relations (1->4->2->1 and 1->4->3->1), which you want to prevent. To find out if a reverse relation already exists, you can use the following query, which will show all parents of list 4 (including inherited/transitive parents):
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
您可以在结果中看到，列表 1 是 列表 4 的父项之一，您不会插入新记录.
You can see in the result, that the list 1 is one of the parents of list 4, and you wouldn't insert the new record.
既然你只想知道结果中是否有list 1，你可以把最后一行改成
Since you only want to know if list 1 is in the result, you can change the last line to
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.以下查询将显示:
BTW: You can use the same query to prevent redundant relations.
Assuming you want to insert the record with child_id = 4 and parent_id = 1. This would be redundant, since list 4 already inherits list 1 over list 2 and list 3. The following query would show you that:
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)
并且您可以使用类似的查询来获取所有继承的项目:
And you can use a similar query to get all inherited items:
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
这篇关于防止循环连接、递归搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
