在单个SQL表中表示记录之间的多对多关系的最佳方法是什么？

我有一个像这样的SQL表:

更新:我正在更改示例表,因为原始数据(州,城市,学校)的现有分层性质使得项目之间需要简单关系的事实蒙上阴影.

entities
id      name               
1       Apple     
2       Orange            
3       Banana             
4       Carrot                
5       Mushroom        

我想定义这些实体之间的双向关系,以便查看一个实体的用户可以看到所有相关实体的列表.

关系由最终用户定义.

在数据库中表示这些关系并随后查询和更新它们的最佳方法是什么？

我看到的一种方式......

我的直觉说这样的关系表是这样的:

entity_entity
entity_id_a       entity_id_b
1                 2
5                 1
4                 1
5                 4
1                 3

既然如此,如果提供的entity_id为4,那么如何获得所有相关记录,即1和5？

同样,entity_id = 1的查询应该返回2,3,4和5.

感谢您的时间,让我知道我是否可以澄清这个问题.

定义约束:entity_id_a < entity_id_b.

创建索引:

CREATE UNIQUE INDEX ix_a_b ON entity_entity(entity_id_a, entity_id_b);
CREATE INDEX ix_b ON entity_entity(entity_id_b);

第二个索引不需要包含,entity_id_a因为您将仅使用它来选择a一个索引b.RANGE SCANon ix_b会比SKIP SCANon 更快ix_a_b.

使用您的实体填充表,如下所示:

INSERT
INTO entity_entity (entity_id_a, entity_id_b)
VALUES (LEAST(@id1, @id2), GREATEST(@id1, @id2))

然后选择:

SELECT entity_id_b
FROM entity_entity
WHERE entity_id_a = @id
UNION ALL
SELECT entity_id_a
FROM entity_entity
WHERE entity_id_b = @id

UNION ALL 这里允许您使用上面的索引,避免额外的唯一性排序.

以上所有都适用于对称和反自反的关系.这意味着:

如果一个关系到b,则 b是关系到一个

a永远不会与a相关