是否可以查看查询更改了哪些数据? [英] Is is possible to see what data was changed by a query?

问题描述

我有一个需要更改审核的数据库应用程序.我希望在数据库级别实现此目的，这样我就不必解析查询来查看正在更改的字段或将日志记录例程添加到现有代码中.相反，我想在数据库类级别添加必要的审核代码.

I have a database application that needs change-auditing. I am hoping to implement this at the database level so that I don't have to parse queries to see what fields are being changed or add in logging routines to existing code. Instead, I would like to add in the necessary auditing code at the DB class level.

我希望能够发出UPDATE查询，然后在此之后发出另一个查询以查看更改了哪些数据.

I would like to be able to issue an UPDATE query, and then, following that, issue another query to see what data was changed.

如果运行查询UPDATE customers SET cus_tel = '45678', cus_name = 'Mary', cus_city = 'Cape Town' WHERE cus_id = 123;，则更改检测查询将返回以下内容:

If the query UPDATE customers SET cus_tel = '45678', cus_name = 'Mary', cus_city = 'Cape Town' WHERE cus_id = 123; is run, the change-detection query would return something like this:

------------------------------------------
| Field    | PK  | Old Value | New Value |
==========================================
| cus_tel  | 123 | 12345     | 45678     |
| cus_name | 123 | John      | Mary      |
------------------------------------------

在这种情况下，我假设cus_city字段已经 Cape Town，因此不需要更新. PK字段在查询一次更新多行的情况下很有用.

In this case, I'm assuming that the cus_city field was already Cape Town and so did not need to be updated. The PK field is useful in case a query updates multiple rows at once.

使用这些数据，然后我可以根据需要将更改记录到审核表中.

Using this data, I could then log the changes into an auditing table as required.

我正在使用PHP和MySQL/PDO.

I am using PHP and MySQL/PDO.

编辑

我找到了此SO问题解决了将更改记录到表的触发器问题-几乎完全符合我的要求:

I found this SO question which addresses the issue of a trigger to log the changes to a table - almost exactly as I require:

DELIMITER $$

DROP TRIGGER `update_data `$$

CREATE TRIGGER `update_data` AFTER UPDATE on `data_table`
FOR EACH ROW
BEGIN
    IF (NEW.field1 != OLD.field1) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field1", OLD.field1, NEW.field1, NOW());
    END IF;
    IF (NEW.field2 != OLD.field2) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field2", OLD.field2, NEW.field2, NOW());
    END IF;
    IF (NEW.field3 != OLD.field3) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field3", OLD.field3, NEW.field3, NOW());
    END IF;
END$$

DELIMITER ;

不过，很明显，此日志仅来自具有定义字段的单个表.有没有一种方法可以通用化"此触发器，以便可以将其应用于具有任意字段而无需修改(或进行最少修改)的任意表?

It is clear, though, that this logs from only a single table with defined fields. Is there a way to "generalise" this trigger so that it could be applied to any arbitrary table with any arbitrary fields with no (or minimal) modification?

推荐答案

确定.我的解决方案是将PHP和MySQL结合使用，以使其尽可能透明"地工作.

OK. My solution is a combination of PHP and MySQL to get this working as "transparently" as possible.

这些方法存在于使用PDO和预处理语句的Data包装器类中.

These methods exist in a Data wrapper class which uses PDO and prepared statements.

使用的其他方法的一些解释:

Some explanation of other methods used:

• Data::prepareAndExecute ($query, $tokens);是一种快捷方法，用于准备查询，执行查询，如果有结果，则返回这些结果的关联数组.
• Data::isSafeDatabaseEntity ($table)只是检查表名是否适合preg_match ("/^([a-zA-Z0-9_]+)$/", $check);命令以防止SQL注入.这是因为我不能将准备好的语句用于字段名和表名.
• Data::tableInfo ($table);根据从PDOStatement::getColumnMeta ();收到的信息返回表中各列的关联数组.
• Data::getTablePrimaryKey ($table);使用SHOW INDEX FROM...查询的结果.应该说这仅适用于单域PK.

• Data::prepareAndExecute ($query, $tokens); is a shortcut method that prepares a query, executes it and, if there are results, returns an associative array of those results.
• Data::isSafeDatabaseEntity ($table) simply checks that the table name fits a preg_match ("/^([a-zA-Z0-9_]+)$/", $check); command to prevent SQL injection. This is since I can't use prepared statements for field and table names.
• Data::tableInfo ($table); returns an associative array of the columns in a table based on information received from PDOStatement::getColumnMeta ();.
• Data::getTablePrimaryKey ($table); uses the results of a SHOW INDEX FROM... query. It should be said that this is designed to work with single-field PKs only.

从我的Data类中提取:

public static function addTableLogging ($table, $ignorecolumns = array ())
{
    if (Data::isSafeDatabaseEntity ($table))
    {
        $update_trigger = "CREATE TRIGGER `{$table}_after_update` AFTER UPDATE ON `{$table}` FOR EACH ROW BEGIN\n";
        $insert_trigger = "CREATE TRIGGER `{$table}_after_insert` AFTER INSERT ON `{$table}` FOR EACH ROW BEGIN\n";
        $columns = Data::tableInfo ($table);
        $pk = Data::getTablePrimaryKey ($table);
        foreach ($columns as $column)
        {
            if (!in_array ($column ['name'], $ignorecolumns))
            {
                $update_trigger .= "   IF (NEW.{$column ['name']} != OLD.{$column ['name']}) THEN
     CALL changelog_store ('{$table}', OLD.{$pk}, '{$column ['name']}', OLD.{$column ['name']}, NEW.{$column ['name']});
  END IF;\n";
                $insert_trigger .= "   CALL changelog_store ('{$table}', NEW.{$pk}, '{$column ['name']}', '', NEW.{$column ['name']});\n";
            }
        }
        $update_trigger .= "END";
        $insert_trigger .= "END";

        self::removeTableLogging ($table);
        self::prepareAndExecute ($update_trigger);
        self::prepareAndExecute ($insert_trigger);
    }
}

public static function removeTableLogging ($table)
{
    if (self::isSafeDatabaseEntity ($table))
    {
        Data::prepareAndExecute ("DROP TRIGGER IF EXISTS `{$table}_after_update`;");
        Data::prepareAndExecute ("DROP TRIGGER IF EXISTS `{$table}_after_insert`;");
    }
}

public static function refreshLoggingProcedure ()
{
    /* -- for logging into MySQL Table:
      CREATE TABLE `changelog` (
        `change_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `change_table` VARCHAR(50) NULL DEFAULT NULL,
        `change_table_id` VARCHAR(25) NULL DEFAULT NULL,
        `change_field` VARCHAR(50) NULL DEFAULT NULL,
        `change_old` VARCHAR(255) NULL DEFAULT NULL,
        `change_new` VARCHAR(255) NULL DEFAULT NULL,
        `change_user` INT(10) UNSIGNED NOT NULL DEFAULT '0',
        `change_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
        PRIMARY KEY (`change_id`),
        INDEX `change_table_id` (`change_table_id`),
        INDEX `change_table` (`change_table`, `change_field`)
      );
    */
    $logquery = "CREATE PROCEDURE `changelog_store`(IN `tab` VARCHAR(50), IN `pkval` INT, IN `fieldn` VARCHAR(50), IN `oldv` TEXT, IN `newv` TEXT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    IF ISNULL(@STAFFID) THEN
        SET @STAFFID = 0;
    END IF;
    INSERT INTO `changelog` (change_table, change_table_id, change_field, change_old, change_new, change_date, change_user)
        VALUES (tab, pkval, fieldn, oldv, newv, NOW(), @STAFFID);
END";
    Data::prepareAndExecute ("DROP PROCEDURE IF EXISTS `changelog_store`;");
    Data::prepareAndExecute ($logquery);
}

这篇关于是否可以查看查询更改了哪些数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！