mysql修改记录储存,如何存储数据的更改历史记录？

I need to store data's change histories in database. For example some time some user modify some property of some data. The expected result is we can get the change histories for one data like

Tom changed title to 'Title one;'

James changed name to 'New name'

Steve added new_tag 'tag23'

Based on these change histories we can get all versions for some data.

Any good idea to achieve this? Not limited to traditional relation database.

解决方案

These are commonly called audit tables. How I generally manage this is using triggers on the database. For every insert/update from a source table the trigger copies the data into another table called the same table name with an _AUDIT appended to it (the naming convention does not matter, it's just what I use). ORACLE provides you with something called journal tables. Using ORACLE designer (or manually) you can achieve the same thing and often developers put a _JN to the end of the journal/audit table. This, however, works the same, with triggers on the source table copying data into the audit table.

EDIT:

I should also note that you can create a new separate schema to manage just your audit tables or you can keep them in your schema with the source tables. I do both, it just depends on the situation.