mysql触发器查看删除的列,mysql触发器进行日志记录，查找更改的列

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.

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?

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; ...

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.