数据库管理员经常面临一个经典问题:如何追踪数据的变更历史,包括谁在何时对数据进行了哪些更改。虽然许多博客作者都讨论过这个问题,但关于如何在关系数据库中实现这一功能的通用方法却寥寥无几。
这是一个从未发生过的虚构故事:
朱丽叶·卡普莱特遇到了保罗·史密斯,他们相爱并于2009年8月25日结婚。当朱丽叶回到工作岗位时,她请求数据库管理员在公司数据库中更改她的姓名。管理员虽然对此额外的工作并不高兴,但还是为她做了。
朱丽叶和保罗的婚姻并不幸福,不久朱丽叶就和保罗离婚了。之后,朱丽叶感到孤独,她遇到了罗密欧·蒙太古,并再次坠入爱河。理所当然地,他们安排了另一场婚礼,朱丽叶再次请求数据库管理员在2010年1月10日更改她的姓名。
当朱丽叶发现她的丈夫罗密欧是一个赌徒,挥霍了他们所有的钱财时,她的爱情故事以悲剧告终。她再次离婚,并于2010年7月21日将自己的名字改为朱丽叶·辛格尔顿,决心不再结婚。不用说,数据库管理员几乎要自杀,因为他的数据库不允许超过两次更改姓名。
如果在下辈子遇到这种情况,会建议数据库管理员怎么做得更好?
让总结一下数据库管理员被要求对朱丽叶的个人数据进行的操作:
在数据库中保留历史数据的最简单方法是在更新之前创建一个重复的记录。之后,可以更新请求的数据。在追踪朱丽叶的姓名时,在数据库表中得到这些数据:
id | name | created_date | created_by |
---|---|---|---|
124 | 朱丽叶·卡普莱特 | 2005-03-01 | admin |
124 | 朱丽叶·史密斯 | 2009-08-25 | admin |
124 | 朱丽叶·蒙太古 | 2010-01-10 | admin |
124 | 朱丽叶·辛格尔顿 | 2010-07-21 | admin |
在实际的数据库模式中,id是主键,必须是唯一的。因此,需要一个额外的列来包含从哪个数据行滚动的数据。此外,有一个代表记录状态的列会很有帮助。因为情况变得更加复杂,这里展示了数据在数据库中的演变:
CREATE TABLE person (
id INT PRIMARY KEY,
original_id INT,
status VARCHAR(10),
name VARCHAR(100),
created_date DATE,
created_by VARCHAR(50)
);
朱丽叶·卡普莱特于2005年3月1日获得工作。
id | original_id | status | name | created_date | created_by |
---|---|---|---|---|---|
124 | 124 | active | 朱丽叶·卡普莱特 | 2005-03-01 | admin |
姓名从朱丽叶·卡普莱特变更为朱丽叶·史密斯,日期为2009年8月25日。
id | original_id | status | name | created_date | created_by |
---|---|---|---|---|---|
124 | 124 | active | 朱丽叶·史密斯 | 2009-08-25 | admin |
254 | 124 | history | 朱丽叶·卡普莱特 | 2005-03-01 | admin |
姓名从朱丽叶·史密斯变更为朱丽叶·蒙太古,日期为2010年1月10日。
id | original_id | status | name | created_date | created_by |
---|---|---|---|---|---|
124 | 124 | active | 朱丽叶·蒙太古 | 2010-01-10 | admin |
254 | 124 | history | 朱丽叶·卡普莱特 | 2005-03-01 | admin |
347 | 124 | history | 朱丽叶·史密斯 | 2009-08-25 | admin |
姓名从朱丽叶·蒙太古变更为朱丽叶·辛格尔顿,日期为2010年7月21日。
id | original_id | status | name | created_date | created_by |
---|---|---|---|---|---|
124 | 124 | active | 朱丽叶·辛格尔顿 | 2010-07-21 | admin |
254 | 124 | history | 朱丽叶·卡普莱特 | 2005-03-01 | admin |
347 | 124 | history | 朱丽叶·史密斯 | 2009-08-25 | admin |
489 | 124 | history | 朱丽叶·蒙太古 | 2010-01-10 | admin |
可以看到,每次对记录id=124的更改都会触发:创建一个带有status=history的状态的重复行,并更新列名称为请求的值,将created_date设置为当前日期。
当活动记录即将被“删除”时,只有状态标志的值从active更改为history。
基于滚动旧数据实现审计日志非常简单。不需要额外的表。维护也很简单。如果决定删除旧数据,那么就是一条SQL的事情。
但是,如果需要在更多的表中实现审计日志,需要添加columns original_id, status, created_date 和 created_by。必须直接在数据库中实现之前引入的滚动逻辑(通常使用表触发器)或在应用程序中实现。
问题可能是当数据更改时,整个记录都被复制,即包括未更改的数据。这会导致数据重复,如果更改频繁发生,数据库磁盘空间可能会显著增加。例如,如果表persons有一个包含照片的二进制数据列,那么每次朱丽叶的姓名更改时,整个记录(包括照片)都会被复制到滚动记录中。
另一个缺点是支持审计日志的每个表的复杂性都会增加。必须始终记住,检索记录并不简单。总是必须使用带有条件的SELECT子句:
SELECT * FROM persons WHERE status='active';
当需要回滚特定记录的历史数据时,必须从历史记录中复制所有列到活动记录。如果只是将状态值从history切换到active,那就容易多了,但这会破坏引用完整性,如果该记录在其他表中被引用的话。
另一种方法是使用单独的审计日志表,该表专门用于记录来自所有具有审计日志功能的表的数据更改:
id | table | column | row | changed_date | changed_by | old_value | new_value |
---|---|---|---|---|---|---|---|
1241 | persons | name | 124 | 2005-03-01 | admin | 朱丽叶·卡普莱特 | 朱丽叶·卡普莱特 |
1654 | persons | name | 124 | 2009-08-25 | admin | 朱丽叶·卡普莱特 | 朱丽叶·史密斯 |
2547 | persons | name | 124 | 2010-01-10 | admin | 朱丽叶·史密斯 | 朱丽叶·蒙太古 |
3645 | persons | name | 124 | 2010-07-21 | admin | 朱丽叶·蒙太古 | 朱丽叶·辛格尔顿 |
这个解决方案更好地分离了关注点。只有一个通用的数据存储用于所有历史数据。此外,只需要为所有应该支持它的表实现审计日志功能一次。
与之前的解决方案相比,当记录更改时,只有更改的数据被记录。因此,预计会稍微快一些,但没有进行任何测试,所以不能确切证明。真正的优势是需要更少的磁盘空间来存储数据更改。可以通过规范化日志表并只放置对table和column的整数引用来改进表结构。
可以通过查询轻松检索用户活动:
SELECT * FROM audit_log WHERE changed_by='admin';
所有数据更改都记录在一个通用表中,所以那里存储的旧值和新值必须是某种通用类型。例如,在Microsoft SQL中,它可以是sql_variant。或者,可以在将值存储在日志表中之前将它们转换为varchar。
回滚是一个相当困难的任务。如果想将数据回滚到2010年3月3日,必须使用查询:
SELECT [table], [column], old_value, new_value FROM audit_log WHERE _ [
table
]=
'persons'
AND row=124
and changed_date >
'2010-03-01'
ORDER BY changed_date