mysql - 有没有比使用删除后插入或差异更新更好的方法来刷新连接表？

Question

使用 MySQL 时，有没有比在主键上执行 DELETE，然后执行 INSERT 更好的方法来维护多对多连接表当前值？

例如，如果您有表 A 和表 B，表 A_B 包含两个主键，表示 A 到 B 中记录的多对多关系。A_B 中的记录需要定期更新以反射(reflect)新的关联，需要一些被删除和其他被添加。

我读到在某些情况下更容易计算差异并有选择地DELETE 未使用的对并且仅INSERT 新的对，但是如果数据集很小仅在受影响的主键上执行 DELETE，然后用 INSERT 重新填充它是更可取的。

我忍不住认为必须有一种比完全吹气然后填充或计算变化更好的方法。

Answer 1

你真正要找的更多的是一个“状态”表，其中一部分会提供多对多连接表的功能。

这个状态会做的是跟踪表A和表B之间关系的所有状态。与简单的多对多连接表相比，您将能够在该表中保留历史数据，查看谁进行了更改，并且根本不删除任何数据。您还可以使用此结构进行更改，这些更改将在将来应用，只需一点基础设施。您还可以随时轻松存档历史记录。

在最简单的实现中，您的表 A_B 中将包含以下列:

ID - Primary Key, could be anything.  The value isn't important
A_ID - The ID from table A
B_ID - The ID from table B
RELATIONSHIP - This is how the record from table A and table B are related.   It could be as simple as "true/false" or "listed/not-listed" or "associated/removed"
STATUS - "Current/Not-current"  or something similar.

您将对该表执行的所有操作是插入记录，假设您使用该值，状态为“当前”。然后，您将在插入之前触发一个函数。伪代码类似于:

beforeInsert (current_data) {
  find all rows where A_Id = current_data.id and b_id = current_data.id and status = 'Current'
  for each row, change status to 'Not-current' 
}

那么，这对您有何帮助？

您的联接(几乎)与简单的多联接表完全相同

SELECT A.name, B.name 
FROM A 
JOIN A_B on A.ID = A_B.A_ID and A_B.status = 'Current' and A_B.relationship = 'listed'
JOIN B on A_B.B_ID = B.id

获取历史数据

您可以通过一些审计字段轻松查看谁在何时做了什么，以及特定日期的关系状态。+-----+------------+------------+------------+---- ----------+----------------+------+
|编号 | ID_A | ID_B |关系 |状态 |进入_日期 |用户 |
+-----+------------+------------+------------+---- ----------+----------------+------+
| 1 |公司A |产品A |上市 |非当前 | 11 月 1 日 |汤姆 |
| 2 |公司A |产品B |上市 |非当前 | 11 月 1 日 | jack |
| 3 | B公司|产品A |上市 |当前 | 11 月 1 日 |蒂娜 |
| 4 | B公司|产品B |未上市 |当前 | 11 月 1 日 |蒂娜 |
| 5 |公司A |产品A |未上市 |当前 | 11 月 2 日 |汤姆 |
| 6 |公司A |产品B |上市 |当前 | 11 月 2 日 |汤姆 |
+-----+------------+------------+------------+---- ----------+----------------+------+

预输入数据

如果您将状态更改为“active_on”和“expires_on”，您可以使用它来预先将状态更改输入系统。

更多

这为您提供了一个灵活的结构，能够扩展一个简单的多对多连接表，而不会增加查询的复杂性或连接的实际工作方式。