MySQL create update and modify

Short post to keep in memories for a very common task in MySQL with regards of two things:

• keeping track of record timestamp with regards to created/modified
• updating a record if it exists or creating one if absent

The above two issues are pretty common and many frameworks use them and have a way around them. Today I decided to make a small script that would use DB to keep a single table with a list of IP addresses as well as created and modified timestamps for them (kinda admin task), so I got into MySQL to sort out the table and had few thoughts on how to do it better. Googling for few minutes brought me to the article here that shows an interesting way of setting current timestamps on created and modified, while mysql documentation shows a INSERT … ON DUPLICATE KEY  section that I never saw before.

So having a table with a structure like:

CREATE TABLE ip_list(
    ip VARCHAR(20) NOT NULL PRIMARY KEY,
    souce_fw VARCHAR(50) NOT NULL,
    created TIMESTAMP DEFAULT '0000-00-00 00:00:00',
    modified TIMESTAMP DEFAULT NOW() ON UPDATE NOW()
);

 
I could use a simple statement to insert update the IP (adjusting the source field if needed) as follows:

INSERT INTO ip_list (ip,source,created,modified) 
    VALUES ('8.8.8.8','myfw1.com',NULL,NULL)  
    ON DUPLICATE KEY UPDATE source='myfw1.com';

 
Which would either insert a record in the DB with created/modified set to NOW() or update the row with given IP setting new source and adjusting modified to current time.

As usual: simple but cool!