我有一个像这样的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,因为您只使用它来选择一个b中的所有a. ix_b上的RANGE SCAN将比ix_a_b上的SKIP SCAN快.
使用您的实体填充表,如下所示：
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在这里允许您使用上面的索引并避免额外的唯一性排序.
以上所有都适用于对称和反自反的关系.这意味着：
>如果a与b相关,那么b与a有关> a永远不会与a相关
