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)