Friday, May 8, 2009

MySQL: Adding Created and Updated Columns

I had a requirement to add a Last-Modified header to GET requests for an entity called Map in a service I was developing. I figured it would be a good idea to track that information in the MySQL database backing the service instead of in the application itself with a column named UPDATED. In other words, I wouldn't have to explicitly set andupdate the values during the lifetime of the Map.

While I was at it, I also thought it would be a good idea to add a CREATEDcolumn to track when the Map came into existence in case that information would be useful later.

At table creation time you can add the columns with the following SQL (which omits any other useful attributes or columns):

  CREATE TABLE MAP (
ID INT NOT NULL AUTO_INCREMENT,
CREATED DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00',
UPDATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
);
The columns can also be added after the table has been created, assigning the defaults to each table row:
  ALTER TABLE MAP ADD CREATED DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00';
ALTER TABLE MAP ADD UPDATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Finally a trigger can be used, which can be added at anytime, to have the CREATED column be given a value on an INSERT:
  CREATE TRIGGER MAP_CREATED BEFORE INSERT ON MAP
FOR EACH ROW SET NEW.CREATED = NOW();
With the trigger, the default on the CREATED column wouldn't exactly be needed, and in fact the table could be created without it, leaving the NOT NULL clause intact.

The resulting table definition:

mysql> DESCRIBE MAP;
+---------+-----------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+---------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| CREATED | datetime | NO | | 1900-01-01 00:00:00 | |
| UPDATED | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------+-----------+------+-----+---------------------+----------------+
3 rows in set (0.00 sec)

2 comments:

  1. Good article John. MySQL documentation on this page - http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html - also explains how to do this. However I liked your version because it's simple and concise.

    For other readers :: I do recommend looking at MySQL documentation too, so that you can get better understanding of how TIMESTAMP, DATE and DATETIME data types are handled in MySQL.

    ReplyDelete
  2. Super article. It supports my suspicion that when one really understands something it becomes possible to explain it clearly and concisely. Your post does exactly that. Many thanks for taking the time to lay this out.

    ReplyDelete