logo标签列表

数据变更历史记录与审计表：对变更进行分组

mysqltriggersaudit-tables
8
假设我想在MySQL数据库中存储用户和组。它们之间有一个n:m的关系。为了跟踪所有更改，每个表都有一个审计表user_journal、group_journal和user_group_journal。MySQL触发器会在每次插入或更新时将当前记录复制到日志表中（不支持删除，因为我需要知道哪个应用程序用户删除了记录--所以有一个标志active，而不是删除，将设置为0）。
我的问题/问题是：假设我一次要向一个组添加10个用户。当我稍后在应用程序的用户界面中浏览该组的历史记录时，我希望将这10个用户的添加作为一个步骤而不是10个独立的步骤显示出来。有没有一种好的解决方案来将这些更改分组在一起？也许可以有一个计数器，在每次触发触发器时增加它？我从未使用过触发器。
最佳解决方案是将在事务中进行的所有更改组合在一起。因此，当用户在一个步骤中更新组的名称并添加10个用户时（一个表单控制器调用），这将是历史中的一个步骤。也许可以定义一个随机哈希或每次启动事务时递增一个全局计数器，并在触发器中访问该值？
我不想使表设计比拥有每个“真实”表一个日志表更加复杂。我不想在每个数据库表中添加事务哈希（意味着“真实”表，而不是审计表--当然，在那里是可以的）。此外，我希望在数据库中有一个解决方案--而不是在应用程序中。
2个回答
4
我稍微尝试了一下，现在我找到了一个非常好的解决方案：

数据库设置

# First of all I create the database and the basic table:

DROP DATABASE `mytest`;
CREATE DATABASE `mytest`;
USE `mytest`;
CREATE TABLE `test` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `something` VARCHAR(255) NOT NULL
);

# Then I add an audit table to the database:

CREATE TABLE `audit_trail_test` (
    `_id` INT PRIMARY KEY AUTO_INCREMENT,
    `_revision_id` VARCHAR(255) NOT NULL,
    `id` INT NOT NULL,
    `something` VARCHAR(255) NOT NULL
);

# I added a field _revision_id to it. This is 
# the ID that groups together all changes a
# user made within a request of that web
# application (written in PHP). So we need a
# third table to store the time and the user
# that made the changes of that revision:

CREATE TABLE `audit_trail_revisions` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `time` DATETIME NOT NULL
);

# Now we need a procedure that creates a
# record in the revisions table each time an
# insert or update trigger will be called.

DELIMITER $$

CREATE PROCEDURE create_revision_record()
BEGIN
    IF @revision_id IS NULL THEN
        INSERT INTO `audit_trail_revisions`
            (user_id, `time`)
                VALUES
            (@user_id, @time);
        SET @revision_id = LAST_INSERT_ID();
    END IF;
END;

# It checks if a user defined variable
# @revision_id is set and if not it creates
# the row and stores the generated ID (auto
# increment) into that variable.
# 
# Next I wrote the two triggers:

CREATE TRIGGER `test_insert` AFTER INSERT ON `test` 
    FOR EACH ROW BEGIN
        CALL create_revision_record();
        INSERT INTO `audit_trail_test`
            (
                id,
                something,
                _revision_id
            ) 
        VALUES
            (
                NEW.id,
                NEW.something,
                @revision_id
            );
    END;
$$

CREATE TRIGGER `test_update` AFTER UPDATE ON `test` 
    FOR EACH ROW BEGIN
        CALL create_revision_record();
        INSERT INTO `audit_trail_test`
            (
                id,
                something,
                _revision_id
            ) 
        VALUES
            (
                NEW.id,
                NEW.something,
                @revision_id
            );
    END;
$$

应用程序代码（PHP）

$iUserId = 42;

$Database = new \mysqli('localhost', 'root', 'root', 'mytest');

if (!$Database->query('SET @user_id = ' . $iUserId . ', @time = NOW()'))
    die($Database->error);
if (!$Database->query('INSERT INTO `test` VALUES (NULL, "foo")'))
    die($Database->error);
if (!$Database->query('UPDATE `test` SET `something` = "bar"'))
    die($Database->error);

// To simulate a second request we close the connection,
// sleep 2 seconds and create a second connection.
$Database->close();
sleep(2);
$Database = new \mysqli('localhost', 'root', 'root', 'mytest');

if (!$Database->query('SET @user_id = ' . $iUserId . ', @time = NOW()'))
    die($Database->error);
if (!$Database->query('UPDATE `test` SET `something` = "baz"'))
    die($Database->error);

然后...结果

mysql> select * from test;
+----+-----------+
| id | something |
+----+-----------+
|  1 | baz       |
+----+-----------+
1 row in set (0.00 sec)

mysql> select * from audit_trail_test;
+-----+--------------+----+-----------+
| _id | _revision_id | id | something |
+-----+--------------+----+-----------+
|   1 | 1            |  1 | foo       |
|   2 | 1            |  1 | bar       |
|   3 | 2            |  1 | baz       |
+-----+--------------+----+-----------+
3 rows in set (0.00 sec)

mysql> select * from audit_trail_revisions;
+----+---------+---------------------+
| id | user_id | time                |
+----+---------+---------------------+
|  1 |      42 | 2013-02-03 17:13:20 |
|  2 |      42 | 2013-02-03 17:13:22 |
+----+---------+---------------------+
2 rows in set (0.00 sec)

如果我有遗漏的地方，请告诉我。为了记录删除操作，我需要在审计表中添加一个action列。

2
假设您每秒添加一批用户到一个组，我建议简单地向user_group和user_group_journal添加一个名为added_timestamp的时间戳类型列。请不要将其设置为自动更新时间戳或默认为CURRENT_TIMESTAMP。相反，在代码中，当您批量插入到user_group时，计算当前日期和时间，然后手动为所有新user_group记录设置此值。
您可能需要调整设置以将字段复制到其他新的user_group记录中，以便将其添加到user_group_journal表中。
然后，您可以创建一个按group_id和新added_timestamp列分组的查询/视图。
如果需要更高的精度，则可以使用字符串列并使用更细粒度的时间的字符串表示形式进行填充（但您需要根据所用语言的库生成）。
好的，简单的解决方案。 - ToolmakerSteve
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接