存储交互数据的规范方法sql

Question

我有很多相同类的实例。现在这些对象可以被链接并且这个链接可以有一个权重。就像在无向图中一样。现在我想在我的mysql数据库中存储每两个对象之间的关系。

数据如下所示

a ===WEIGHT=== b
a ===WEIGHT=== c
b ===WEIGHT=== a
...

我可以创建一个具有以下结构的表：

object1_id object2_id权重

但是在搜索两个对象之间的权重时，我不知道哪个是object1和哪个是object2。所以我需要写两个查询。此外，如果我想添加权重，并且想首先检查它是否已经在我的数据库中，我必须编写两个查询以确保它不在其中。

这两个问题是：

SELECT weight from tableName where object1_id = $1 AND object2_id = $2;
SELECT weight from tableName where object1_id = $2 AND object2_id = $1;

以确保检查这两种可能性，因为我不知道它是如何存储的。

我想有一个更好的方法来存储这样的数据。可能对于这个问题已经有很多答案了，但是我不知道这个问题是怎么称呼的，所以很难找到解决方案。

谢谢你的建议。

Answer 1

如果数据中只有一个边沿，那么您可以使用：

select t.*
from t
where (object1_id, object2_id) in ( ($1, $2), ($2, $1) );

如果两条边都可能在表中，并且您任意需要一条边，则添加limit1。

如果希望强制数据库中只有一个边缘，则可以对表达式使用唯一索引：

create unique index unq_t_object1_id_object2_id on
    t( least(object1_id, object2_id), greatest(object1_id, object2_id) );

您可能还想添加一个check约束,以便它们始终保持顺序：

alter table t add constraint chk_object1_id_object2_id check (object1_id < object2_id);

在强制执行这些条件后,您可以将上述查询更改为：

select t.*
from t
where object1_id = least($1, $2) and
      object2_id = greatest($1, $2);

这对优化器来说其实更容易优化。