如何使用 MySQL 触发器按操作对数据更改进行分组

Question

我正在 MySQL 中使用触发器来记录数据更改。这些更改记录在行级别上。现在，我可以在日志表中为更改的每一行插入一个条目。不过，我还需要记录更改所属的操作。

例如，像“DELETE * FROM table WHERE type=x”这样的删除操作可以删除多行。使用触发器，我可以将每个已删除行的条目插入到日志表中，但我还想为整个操作提供唯一标识符，以便日志表看起来像这样：

log_id  operation_id  tablename  fieldname  oldvalue  newvalue

1       1             table      id         1         null  
2       1             table      type       a         null
3       1             table      id         2         null
4       1             table      type       a         null
5       2             table      id         3         null  
6       2             table      type       b         null
7       2             table      id         4         null
8       2             table      type       b         null

MySQL 中是否有一种方法可以确定行更改所属的更高级别操作？或者这只能通过应用程序级代码来实现？将来如果能够记录操作所属的事务也会很好。

另一个问题是除了使用查询日志之外是否可以捕获实际的 SQL 查询。我自己并不这么认为，但也许我错过了一些东西。当然可以在应用程序级别捕获这些，但目标是尽可能减少对应用程序级别代码的入侵。

如果 MySQL 无法做到这一点，那么其他数据库系统又如何呢？对于当前的项目来说，不能选择使用 MySQL 以外的其他东西，但对于未来的项目来说，了解这一点会很高兴。

编辑 在伪代码中，我想实现以下类型的触发器：

CREATE TRIGGER tablename_log_insert
AFTER INSERT ON tablename
INSERT INTO log_operations (operation_type, relation) VALUES ('insert', 'tablename');
SET @operation_id = LAST_INSERT_ID();
FOR EACH ROW
BEGIN
    INSERT INTO log_tablename(@operation_id, ...) VALUES (@operation_id, ...);
END;

我知道这对于 MySQL 来说是不正确的，但也许这个伪代码有助于澄清我的问题。

Answer 1

基于触发器的创建，您知道一些上下文是什么：删除、插入、更新。

CREATE TRIGGER  <name_of_trigger>  
   [BEFORE | AFTER] 
   [UPDATE | INSERT | DELETE] -- Dictates context for trigger 'action'.
ON <table_name>
FOR EACH ROW BEGIN
  <SQL to execute>
END

如果您将“操作”列添加到日志中，您就可以知道使用了什么操作来导致将记录插入到“更改”日志中。

示例：

DELIMITER $

-- Create Update,Insert,Delete triggers that logs the result of a table update to a log  
CREATE TRIGGER  trg_AFT_INS_table  AFTER UPDATE ON table
FOR EACH ROW BEGIN
  INSERT INTO table_log ( a, b, c, d, action)
  VALUES ( NEW.a, NEW.b, NEW.c, NEW.d, "update");
END $
CREATE TRIGGER  trg_AFT_INS_table  AFTER INSERT ON table
FOR EACH ROW BEGIN
  INSERT INTO table_log ( a, b, c, d, action)
  VALUES ( NEW.a, NEW.b, NEW.c, NEW.d, "insert");
END $
CREATE TRIGGER  trg_AFT_INS_table  AFTER DELETE ON table
FOR EACH ROW BEGIN
  INSERT INTO table_log ( a, b, c, d, action)
  VALUES ( OLD.a, OLD.b, OLD.c, OLD.d, "delete");
END $

在记录表修改时，您可以讨论 BEFORE 与 AFTER...我们发现使用“after”值使日志实际上与“二进制日志”相同，并且可用于重建，包括最后一次修改，日志记录所在的表。

——J·乔根森——

Answer 2

使用MySQL一般查询日志，它使您能够捕获准确的语句已被执行的。您可以选择将其写入文件还是表。