mysql - 如何跟踪 MySQL 数据库中 bool 列的更改？

Question

我的应用程序为在线商店的客户提供服务。我的数据库中的一个表是“Product”，它有一列“In_Stock”。这是一个 bool (位(1))列。我的客户发送其产品目录的数据源，每个客户都有自己的此表版本。我想跟踪此“库存”列的更改，其影响是...

 11/13/2016  true
 12/26/2016  false
 01/07/2017  true

这样当我进行一些审核时，我可以看到给定时间段内给定产品的状态。

我怎样才能最好地做到这一点？

创建一个单独的历史表并通过触发器仅针对一个 bool 列更新它似乎有点过分了。历史专栏就足够了吗？我可以将数据保存在某种 JSON 字符串中。

Answer 1

抱歉，任何可行的解决方案都需要第二个表。

其中一个解决方案是版本范式 (vnf)，它是 2nf 的一种特殊情况。考虑包含 bool 字段的表(假设它已正确标准化为至少 3nf)。现在您想要跟踪对 bool 字段所做的更改。一种方法是通过添加 EffectiveDate 列将行转换为版本，然后不更新行，而是在日期字段中写入包含当前日期的新行(或者如果 bool 字段未更改则更新) )。

这允许跟踪字段，每次字段更改时都会有一个新版本。但也有严重的缺点，其中最重要的是行不再是实体，而是实体的版本。这使得无法使用该表的外键，因为那些想要引用实体的人。

但是仔细看看设计。在更改之前，您有一个良好的标准化表格，没有跟踪更改。添加EffectiveDate列后，出现了微妙的变化。除 bool 字段外的所有字段都像以前一样仅依赖于 PK。 bool 字段不仅取决于 PK，还取决于新的日期字段。它不再是 2nf。

标准化表格需要将 bool 字段和日期字段移动到新表格:

create table NewTable(
  EntityID   int  not null references OriginalTable( ID ),
  EffDate    date not null,
  TrackedCol boolean,
  constraint PK_NewTable primary key( EntityID, EffDate )
);

当新行插入到原始表中时，将插入第一个版本。从那时起，只有当原始表的更新更改了 bool 字段的值时，才会添加另一个版本。

Here是先前的答案，其中包括用于获取版本化数据的当前值和任何过去值的查询。我已经在这里多次讨论过这个设计。

此外，还有一种方法可以构 build 计，因此无需更改应用程序代码。也就是说，重新设计对于现有代码将是完全透明的。上面链接的答案包含另一个指向更多文档的链接，以展示如何完成此操作。