mysql - 是否可以查看查询更改了哪些数据？

Question

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

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

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

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

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

使用这些数据，我可以根据需要将更改记录到审计表中。

我正在使用 PHP 和 MySQL/PDO。

编辑

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

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 ;

不过，很明显，此日志仅来自具有已定义字段的单个表。有没有一种方法可以“概括”这个触发器，以便它可以应用于具有任意字段的任意表而不需要(或最小)修改？

Answer 1

好的。我的解决方案是结合使用 PHP 和 MySQL，以使其尽可能“透明”地工作。

这些方法存在于 Data 中使用 PDO 和准备好的语句的包装器类。

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

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

摘 self 的 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);
}