Is there a way to get field => value pairs from a subselect in MySQL?

Question

I'm considering how to best implement an Audit Trail in an app, which is backed by MySQL. A few systems access the same database and tables, so I thought triggers would be the best solution.

However, I don't want to manually write a trigger for every table, so I'm looking to automatically log the field name and data for each insert/delete.

Something like:

INSERT INTO `audits` SET
    `table_name` = 'jobs',
    `table_key` = OLD.`id`,
    `changed_at` = NOW(),
    `notes` = (SELECT * FROM NEW);

However, the subselect will return a single row result set, which cannot be treated as string. I want a function that will take that row and convert it to:

"id = 1, name = 'something', another_field = 'data'"

Or something like that.

Edit: The main point here is that I don't want to have to type in every field in every table. There's over 120 tables, and some have > 100 fields. If it's not possible in MySQL itself, I guess I'll write a little program to spin over each table and field and generate the SQL for me.

Answer 1

only

SELECT GROUP_CONCAT(col1) FROM yourtable

but this return all field by comma delimiter or other separator that you can declare.

link

Answer 2

SELECT  CONCAT('id = ', id, ', name = ''', name, ''', another_field = ''', data, '''')
FROM    NEW

Answer 3

You can just concatenate the strings.

e.g:

select concat("id = ", t.id, "name = ", t.name) from myTable t;

Answer 4

I eventually wrote a PHP script to loop over each relevant table and build the triggers for me.