mysql - 审核产品数据的日志记录？

Question

当工作人员更改产品名称、选项名称或价格信息时。它应该将数据插入历史日志以及谁完成了它。

元素表:

item_id (PK)
item_name
item_description

注意:商品价格在 item_options 表中

item_options 表:

option_id (PK)
item_id (FK)
option_name
option_price

一个项目可以有一个或多个选项。

如果我想改名 items.item_name , 应该将当前记录复制到历史表中，从 items 中删除当前记录表，然后在 items 中插入一 strip 有新信息的新记录 table ？
item_options 呢？ ，这将如何工作？如果特定 item_id 有多个选项，这是否意味着我需要将选项复制到历史表？
items 的审核记录/历史记录表应该是什么样子和 item_options ?

谢谢

Answer 1

您的审计数据应该按表存储，而不是全部存储在一个地方。您要做的是为要跟踪的每个表创建一个审计表，并创建触发器以在审计表中为审计表上的任何数据操作操作创建一条记录。

绝对建议禁止 DELETE items 上的操作和 item_options表 - 添加标志，如 item_active和 item_option_active这样您就可以软删除它们。在您正在执行诸如存储引用过去订购的产品的发票之类的事情时，这是正常的做法，并且需要将数据用于历史报告目的，但不用于日常使用。

您的审计表不应该用于引用旧数据，您的正常数据模型应该支持简单地“隐藏”旧数据，它可能仍将被使用，并存储多个版本的数据，这些数据会随着时间的推移而变化。

对于审计，存储最后一个用户的用户名以修改给定记录也很有用 - 当从 Web 应用程序中使用时，您不能使用 MySQL 的 USER()函数来获取有关谁登录的任何有用信息。添加一列并填充它意味着您可以在审计触发器中使用该信息。

注意:我假设您不允许在正常情况下更改项目 ID - 这会使您的审计系统更加复杂。

如果您将事件标志和最后修改者数据添加到表中，它们将如下所示:

元素表:

mysql> desc items;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| item_id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| item_name        | varchar(100) | YES  |     | NULL    |                |
| item_description | text         | YES  |     | NULL    |                |
| item_active      | tinyint(4)   | YES  |     | NULL    |                |
| modified_by      | varchar(50)  | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

项目选项表:

mysql> desc item_options;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| option_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| item_id       | int(11)      | YES  | MUL | NULL    |                |
| option_name   | varchar(100) | YES  |     | NULL    |                |
| option_price  | int(11)      | YES  |     | NULL    |                |
| option_active | tinyint(4)   | YES  |     | NULL    |                |
| modified_by   | varchar(50)  | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

您的审计表需要存储四个额外的信息:

审核 ID - 此 ID 仅对该表的历史记录是唯一的，它不是全局值

Change made by - 进行更改的数据库用户

更改日期/时间

操作类型 - INSERT或 UPDATE (或 DELETE 如果您允许)

您的审计表应如下所示:

项目审核表:

mysql> desc items_audit;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| audit_id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| item_id          | int(11)      | YES  |     | NULL    |                |
| item_name        | varchar(100) | YES  |     | NULL    |                |
| item_description | text         | YES  |     | NULL    |                |
| item_active      | tinyint(4)   | YES  |     | NULL    |                |
| modified_by      | varchar(50)  | YES  |     | NULL    |                |
| change_by        | varchar(50)  | YES  |     | NULL    |                |
| change_date      | datetime     | YES  |     | NULL    |                |
| action           | varchar(10)  | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

项目选项审核表:

mysql> desc item_options_audit;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| audit_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| option_id     | int(11)      | YES  |     | NULL    |                |
| item_id       | int(11)      | YES  |     | NULL    |                |
| option_name   | varchar(100) | YES  |     | NULL    |                |
| option_price  | int(11)      | YES  |     | NULL    |                |
| option_active | tinyint(4)   | YES  |     | NULL    |                |
| modified_by   | varchar(50)  | YES  |     | NULL    |                |
| change_by     | varchar(50)  | YES  |     | NULL    |                |
| change_date   | datetime     | YES  |     | NULL    |                |
| action        | varchar(10)  | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

不要在审计表上使用外键；审计表中的行不是他们正在审计的记录的子行，因此外键没有任何用处。

触发器

注意: MySQL 不支持多语句类型的触发器，因此您需要为每个 INSERT 提供一个, UPDATE和 DELETE (如果适用)。

您的触发器只需要 INSERT所有 NEW值到审计表中。 items 的触发器定义表可能是:

/* Trigger for INSERT statements on the items table */
CREATE DEFINER=`root`@`localhost` TRIGGER trigger_items_insert_audit 
AFTER INSERT ON items 
  FOR EACH ROW BEGIN
    INSERT INTO items_audit (
                  item_id, item_name, item_description, 
                  item_active, modified_by, change_by,  
                  change_date, action
                ) VALUES (
                  NEW.item_id, NEW.item_name, NEW.item_description,  
                  NEW.item_active, NEW.modified_by, USER(),  
                  NOW(), 'INSERT'
                ); 
  END;

/* Trigger for UPDATE statements on the items table */
CREATE DEFINER=`root`@`localhost` TRIGGER trigger_items_update_audit 
AFTER UPDATE ON items 
  FOR EACH ROW BEGIN
    INSERT INTO items_audit (
                  item_id, item_name, item_description, 
                  item_active, modified_by, change_by,  
                  change_date, action
                ) VALUES (
                  NEW.item_id, NEW.item_name, NEW.item_description,  
                  NEW.item_active, NEW.modified_by, USER(),  
                  NOW(), 'UPDATE'
                ); 
  END;

为 item_options 创建类似的触发器 table 。

更新:电子商务中的数据历史

我们在上面所做的审计将允许您保留任何给定数据库表的历史记录，但会创建一个不适合用于需要定期访问的数据的数据存储。

在电子商务系统中，保留可用的历史数据很重要，这样您就可以在更改属性的同时在某些情况下仍显示旧值。

这应该与您的审计解决方案完全分开

存储历史的最佳方式是为每个需要历史存储的属性创建一个历史表。 This Stackoverflow question has some good information about keeping a history of a given attribute .

在你的情况下，如果你只关心价格和标题，你会创建一个 prices表和 item_titles table 。每个人都有一个指向 item_options 的外键。表或 items表(主表仍将存储当前价格或标题)，并将具有价格或标题及其生效日期。这些表应该有细粒度的(可能是基于列的)权限以避免更新 effective_from日期，以及插入记录后的实际值。

您还应该在这些表上使用上面的审计解决方案。