MySQL: add created and modified fields

What's the best approach to add created and modified fields in MySQL:

1) using MySQL features like on update CURRENT_TIMESTAMP or

2) using PHP (or something else)? Why?

If the answer is MySQL,开发者_StackOverflow社区 how would you do this?

This is a good question. I suppose you would need to look at this from two respects

• performance
• implementation architecture

From a performance perspective if you are already creating a row or updating a row then updating the timestamp on that row is negligible and therefore I don't think there is much difference between updating from php or via a trigger.

From an implementation architecture perspective triggers are fairly easy and within php if you have a nice ORM architecture (or use something like Doctrine) you can overwrite your save() logic in an abstract layer to always update the create/modified timestamps when you save.

So personally I would implement this in PHP if I pretty much have all of my database access via the ORM and not through triggers or stored procs.

My preference is to alter or add a column to your MySQL table like LastChanged with the type as TIMESTAMP and the default value as CURRENT_TIMESTAMP. So when a row is added to the table the date is automatically generated.

Then you could have a trigger in the AFTER UPDATE like, that will update the last changed date once the record has been modified

    IF NEW.LastChanged = '0000-00-00 00:00:00' THEN
        SET NEW.LastChanged = NOW();
    END IF