用于日志记录的 mysql 触发器，查找更改的列 [英] mysql Trigger for logging, find changed columns

问题描述

我正在编写一个触发器来跟踪表中发生的所有更改.不幸的是，该表有 150 多列，我想避免在代码中写入每一列(例如 new.col1、new.col2....)，因此我在更新后触发器"中编写了以下查询

I am writing a trigger to keep track of all the changes that happens in a table. Unfortunately the table has 150+ columns and I wanted to avoid writing each column in the code (Ex. new.col1, new.col2....) and thus I wrote a following query in "after update trigger"

INSERT INTO logs SELECT *, NOW() FROM abc WHERE abc.id = NEW.Id;

由于更新查询中未更改的数据重复，这个想法导致了多个问题.

This idea is causing multiple issue due to duplication of data that is not changed in update query.

简而言之，我想动态找出哪些列是更新查询的一部分，如果不可能，有没有办法遍历新"行的所有列，以便我可以动态比较旧的.@colName== new.@colName?

In a nutshell I want to dynamically find out which columns were part of the update query and if that is not possible is there a way to iterate through all the columns of "new" row so I can dynamically compare old.@colName == new.@colName?

我已经看过了Oracle PL/SQL:动态循环触发列，如何确定更新触发器中是否有任何更改在 t-sql 和 MySQL UPDATE 触发器:插入实际更改的列的值.

I have already seen Oracle PL/SQL: Loop Over Trigger Columns Dynamically, How to determine if anything changed in update trigger in t-sql and MySQL UPDATE trigger: INSERTing the values of the columns that actually changed.

最后一个链接与我需要的很接近，只有一个区别，我不想在以下语句中对列名进行硬编码，因为我将要编写类似触发器的所有表中有超过 100 多列为了！！

The last link is the closes to what I need with only one difference, I don't want to hard code column names in following statment because I have way over 100+ columns in all the tables I am going to write similar trigger for!!

IF NEW.column1 <> OLD.column1 THEN INSERT INTO... END IF; IF NEW.column2 <> OLD.column2 THEN INSERT INTO... END IF

推荐答案

今天早上我一直在对此进行一些研究，看起来我遇到了与您相同的搜索结果.最终在我看来，没有办法遍历所有表列并引用相应的旧/新值.我决定明确检查每一列，然后记录:

I've been doing a bit of research on this this morning and looks like I have come across much of the same search results as you. Ultimately it looks to me like there's no way to loop over all table columns and reference the corresponding old/new values. I'm settling on explicitly checking each column and then logging:

IF (NEW.fld1 <> OLD.fld1) OR (NEW.fld1 IS NOT NULL AND OLD.fld1 IS NULL) OR (NEW.fld1 IS NULL AND OLD.fld1 IS NOT NULL) THEN
 INSERT INTO `fld_audit` (`table`, `fldname`, `oldval`, `newval`)
 VALUES ("tblname", "fld1", OLD.fld1, NEW.fld1); 
END IF; 

IF (NEW.fld2 <> OLD.fld2) OR (NEW.fld2 IS NOT NULL AND OLD.fld2 IS NULL) OR (NEW.fld2 IS NULL AND OLD.fld2 IS NOT NULL) THEN
 INSERT INTO `fld_audit` (`table`, `fldname`, `oldval`, `newval`)
 VALUES ("tblname", "fld2", OLD.fld2, NEW.fld2); 
END IF; ...

我发现了另一种解决方案的暗示 此处.理论上你可以有 3 个分隔列表，一个用于列名，一个用于旧值，一个用于新值.您必须显式引用旧的和新的 val，但这将是一行(更容易维护或复制/粘贴以在其他表上实现)，然后您可以循环.所以在伪代码中它看起来像这样:

I found an inkling of another solution here. In theory you could have 3 delimited lists, one for column names, one for old vals and one for new vals. You would have to explicitly reference the old and new vals, but that would be one line (easier to maintain or copy/paste to implement on other tables) and you could then loop. So in pseudo code it would look something like this:

fields_array = concat_ws(",", "fld1", "fld2");
old_vals_array = concat_ws(",", OLD.fld1, OLD.fld2);
new_vals_array = concat_ws(",", NEW.fld1, NEW.fld2);

foreach fields_array as key => field_name
     INSERT INTO `fld_audit` (`table`, `fldname`, `oldval`, `newval`)
     VALUES ("tblname", field_name, old_vals_array[key], vew_vals_array[key]);

我没有想太多.您可能需要调用存储过程而不是设置变量.但它可能值得研究.我已经在触发器上花费了足够的时间.不确定我是否可以在更优雅的解决方案上验证(向我的老板)试错时间.

I haven't thought this through too much. You might need to call into a stored procedure rather than set variables. But it might be worth looking into. I've spent enough time on my triggers already. Not sure I could validate (to my boss) trial and error time on a more elegant solution.

这篇关于用于日志记录的 mysql 触发器，查找更改的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！