TRIGGERs can be used to log changes to individual DB columns as described at https://stackoverflow.com/a/779250/569976 but that technique requires you have an IF statement for each column. It's not a huge issue if you're just interested in changes to one column BUT if you're interested in changes to all columns it becomes a bit more unweildy.
I can get all the column names of a table, dynamically, by querying the INFORMATION_SCHEMA.COLUMNS table. My question is... can I use that to dynamically reference the column names? Like in the TRIGGER you'd do OLD.columnName <> NEW.columnName but I don't think you can really make a column name dynamic like that.
In PHP you could use variable variables. eg. $obj->$var. But if MySQL has anything remotely similar that'd be news to me.
Any ideas? Or am I just going to go with the old fashioned approach of writing an IF statement for each of the 100s of columns this table has?
The trigger can only reference identifiers directly. You can't use a variable or an expression to name an identifier.
That would require dynamic SQL with PREPARE and EXECUTE so you could have the statement parsed at runtime from a string, but you can't PREPARE a new statement inside a trigger, because the trigger is already executing in the context of the currently executing statement.
The simplest solution is to write a trigger that references each column directly, with as many IF statements as there are columns in the table (I wonder why you have hundreds of columns in your table; that sounds like a different problem of bad design).
The comments above mention a binary log parser. Debezium is an example of an open-source binlog parser.
MySQL also supports an audit plugin architecture, but frankly the existing implementations of audit plugins are pretty clumsy.