符合增量同步条件的数据库架构

Question

这只是一个讨论的问题。现在，我需要重新设计一个mysql数据库表。基本上，这个表包含我从另一个数据库同步的所有合同记录。用户可以修改、删除合同记录，也可以通过GUI界面添加新的合同记录。在此阶段，表结构与合同信息完全相同(列:序列号、失效日期等)。在这种情况下，我只能同步整个表(删除所有旧记录，替换为新记录)。如果我想要增量(仅与已修改的、新的、已删除的记录同步)同步表，我应该如何更改数据库模式？

以下是我提出的方法，但我需要您的建议，因为我认为这是数据库应用程序中的常见场景。1)引入序列号概念/列:对于每个序列，用该序列号标记新增的记录、修改的记录、删除的记录。通过记录上一次同步的序列号，只通过序列号较高的记录；

2)由于已删除的合同可以重新添加，且原表有主键约束，是否需要为已删除的记录再创建一张表？或者增加一个标志栏来标示该合同是否已经被删除？

我希望我能清楚地解释我的问题。无论如何，如果你知道任何关于这方面的文章或你自己的建议，请让我知道。谢谢!

Answer 1

我想你把三角洲的概念搞混了。

您可以接收全部负载(整个数据集)，也可以只接收更改(“增量”)。

如果你处理满负载，你可以做一个truncate+insert。这样，您就不必处理新行和旧行或删除。由于引用完整性约束等原因，这可能是不可行的。

如果您收到增量，则通常将每行放入以下两种类别中的一种：

1. 匹配密钥= UPDATE。您可以选择忽略具有相同数据的行或覆盖。

键没有匹配键= INSERT

删除是特殊的。不存在的行将无法发送给您。因此，您需要就如何处理它达成一致。在满载的情况下，您可以删除接收到的数据集中不存在的所有本地行。

对于delta值，您可以同意发送带有删除标记(flag、date)的行。然后，您可以决定是否保留带有删除标记的行(由上面的(1)自动处理)，或者是否应该DELETE您的行。我建议保留它，因为迟早会有人指责你缺少行/糟糕的数据质量，然后你把DELETE_DATE扔到他们的脸上。

对于MySQL，您可以使用INSERT ... ON DUPLICATE KEY UPDATE来实现"upsert“功能。

如果您需要更具体的帮助，则必须提供更多详细信息。

更新：

好的，这是一个例子。假设您有以下表结构：

create table contracts(
   contract_id int         not null
  ,details1    varchar(20)
  ,details2    varchar(20)
  ,delete_date date
  ,primary key(contract_id)
);

每当收到更新的行时，都会将它们插入到具有相同结构的临时表中：

create table contracts_delta(
   contract_id int         not null
  ,details1    varchar(20)
  ,details2    varchar(20)
  ,delete_date date
  ,primary key(contract_id)
);

下面是一些示例数据：

mysql> select * from contracts;
+-------------+----------+----------+-------------+
| contract_id | details1 | details2 | delete_date |
+-------------+----------+----------+-------------+
|           1 | a1       | a2       | NULL        |
|           2 | b1       | b2       | NULL        |
|           3 | c1       | c2       | 2011-01-03  |
+-------------+----------+----------+-------------+

mysql> select * from contracts_delta;
+-------------+----------+----------+-------------+
| contract_id | details1 | details2 | delete_date |
+-------------+----------+----------+-------------+
|           2 | b1       | b2       | 2011-01-03  | <-- Row was deleted
|           3 | c1       | c2       | NULL        | <-- No longer deleted
|           4 | d1       | d2       | NULL        | <-- This is new row
+-------------+----------+----------+-------------+

使用我之前链接的语法，您可以插入所有新行。只要行已经存在(在复制时)，我们就选择更新列。请注意，这会自动处理已删除的行，因为delete_date与其他列一样都是常规列。

insert 
  into contracts(
        contract_id
       ,details1
       ,details2
       ,delete_date
       )
 select contract_id
       ,details1
       ,details2
       ,delete_date
  from contracts_delta s
    on duplicate key 
    update contracts.details1    = s.details1
          ,contracts.details2    = s.details2
          ,contracts.delete_date = s.delete_date;

在"upsert“之后，合约中的数据将如下所示：

mysql> select * from contracts;
+-------------+----------+----------+-------------+
| contract_id | details1 | details2 | delete_date |
+-------------+----------+----------+-------------+
|           1 | a1       | a2       | NULL        |
|           2 | b1       | b2       | 2011-01-03  |
|           3 | c1       | c2       | NULL        |
|           4 | d1       | d2       | NULL        |
+-------------+----------+----------+-------------+

--此时，您可以选择删除增量表(记得下次重新创建它)

drop table contracts_delta;

--或者您可以直接截断它以节省一些空间。(你需要确保它在下一次加载时是空的)

truncate table contracts_delta;

--或者您可以保存实际的增量(重命名该表)，以防您在某些时候需要单个增量

alter table contracts_delta rename to contracts_delta_20110115;