跟踪myql更改的最佳方法 [英] best way to keep track of myql changes

问题描述

我正在使用MySQL Workbench的EER工具为正在开发的应用程序进行数据库设计.跟踪数据库上的数据更改非常重要，因此我遵循了

I'm using MySQL workbench's EER tool to make the database design for an application I'm developing. it is very important to track data changes on the DB, so i was following this post which makes a lot of sense the accepted answer. the problem im having is that i am not good understanding the primary keys so im a little confused on how this should work, since every time a change is made there will be another row inserted with the same Id, therefore the DB wouldn't allow it am I right? And i need to use a primary key because of foreign keys.

推荐答案

我在(非常)伪代码中的正常样式":

My normal "pattern" in (very)pseudo code:

• 表A:a_id(PK)，a_stuff
• 表A_history:a_history_id(PK)，a_id(FK引用A.a_id)，valid_from，valid_to，a_stuff

在A上触发:

• 插入时:将值插入到带有有效_现在=现在且有效_ =空值的A_history中.
• 更新时:将a_id的最近历史记录设置为valid_to = now；并使用该行的更新值从插入时"触发器执行相同的插入.
• 删除时:将a_id的最近历史记录设置为valid_to = now.

在这种情况下，您将使用"x> = from and x< to"查询历史记录(不 BETWEEN，因为前一条记录的"from"值应与下一条记录的"from"值匹配).

In this scenario, you'd query history with "x >= from and x < to" (not BETWEEN as the a previous record's "from" value should match the next's to "value").

这篇关于跟踪myql更改的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！