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!

Leave a Reply