mysql-change-log

IF (NEW.mobilePhoneNo <> OLD.mobilePhoneNo) OR (NEW.mobilePhoneNo IS NOT NULL AND OLD.mobilePhoneNo IS NULL) OR (NEW.mobilePhoneNo IS NULL AND OLD.mobilePhoneNo IS NOT NULL) THEN
if (NEW.mobilePhoneNo <> OLD.mobilePhoneNo) then
 set logs=CONCAT(logs,'mobilePhoneNo [ ' ,OLD.mobilePhoneNo ,'->', NEW.mobilePhoneNo,']');
end if;
if (NEW.mobilePhoneNo IS NOT NULL AND OLD.mobilePhoneNo IS NULL) then
 set logs=CONCAT(logs,'mobilePhoneNo [ ' ,'NULL' ,'->', NEW.mobilePhoneNo,']');
end if;
if (NEW.mobilePhoneNo IS NULL AND OLD.mobilePhoneNo IS NOT NULL) then
 set logs=CONCAT(logs,'mobilePhoneNo [ ' ,OLD.mobilePhoneNo ,'->', 'NULL',']');
end if;
 set logs=CONCAT(logs,' ,');
END IF;

http://stackoverflow.com/questions/11625439/mysql-trigger-for-logging-find-changed-columns

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.