对于监管和欺诈要求,我们需要记录对数据库中大多数表的每次更改。我们在以前的项目中非常成功地做到这一点的方法是拥有每个表的副本,除了:
- 4 个附加列:DateChanged、UserId、Action(创建、更新、删除)和 IP
- Id pk 不再是唯一的。
- 删除了所有其他约束(FK、唯一索引等)。
- 审计表位于单独的审计模式中
在之前的工作中,Oracle DBA 编写了一个脚本来自动生成所有这些,它执行以下操作:
- 如果不存在,则创建一个名为 audit 的新模式
- 迭代普通模式中的每个表 t:
- 在审计模式中创建了一个具有相同表名的新表,但前缀为 a_,例如 a_t
- 添加了与原始表相同的所有列,以及 4 个附加列(DateChanged、UserId、IP 地址和操作)
- 生成触发器并将其添加到原始表(如果还没有)到:
- 如果更新,则将新行写入相应的 a_ 表,“更新”操作包含所有列中的预更新(旧)值(主表将具有新值)
- 如果删除,添加一个作为主表行副本的行,但操作 =“删除”。
- 如果插入,则将该行添加到审计架构
笔记:
- 所有表都有一个 ID PK。
- 一些表需要排除性能或因为不需要(例如由触发器更新的余额表)
该系统的美妙之处在于您可以查询更改的内容和时间,或者谁更改了内容,并查看更改前后的记录。更改是在数据库行级别,而不是单个列级别。
有没有人遇到过这样的 MYSQL 5.6?我们的团队中没有可以从头开始编写这样的东西的 DBA,但我们知道足以修改类似的东西。
我今天早些时候才写了这个。它是一个对 information_schema 数据库起作用的 select 语句,它为审计表和触发器生成模式。
Vanyo 的回答几乎对我有用。一旦我在
NEW
andOLD
引用中移动了反引号就很好了(所以`NEW.column1`
我得到了NEW.`column1`
):