Timestamp Gotchas in MySQL
Recently I realized that in MySQL if you declare a column as type TIMESTAMP, it would be automatically updated to the current time stamp any time the row is updated.
mysql> CREATE TABLE IF NOT EXISTS `MyTable` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
`expiry_date` TIMESTAMP NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
mysql> desc MyTable;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | NULL | ||
| expiry_date | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
If you don’t want this behavior then you can use the following command:
mysql> ALTER TABLE MyTable CHANGE expiry_date expiry_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
mysql> desc MyTable;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | NULL | ||
| expiry_date | timestamp | NO | CURRENT_TIMESTAMP |
Point is, Timestamps in MySQL are generally used to track changes to records, and are updated every time the record is changed. However if you want to store a specific value and don’t want it updated, its best you use a DATETIME field.
Change it using the following command:
mysql> ALTER TABLE MyTable CHANGE expiry_date expiry_date DATETIME NOT NULL DEFAULT ‘2012-10-01 00:00:00’;
mysql> desc MyTable;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | NULL | ||
| expiry_date | datetime | NO | 2012-10-01 00:00:00 |
I also learned that if a table has 2 Timestamps than MySQL does not know which one it needs to do an auto update. Hence this behavior does not work. The work around is:
mysql> CREATE TABLE IF NOT EXISTS `AnotherTable` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(150) NOT NULL,
`creation_date` TIMESTAMP DEFAULT ‘0000-00-00 00:00:00’,
`last_updated` TIMESTAMP DEFAULT now() on update now(),
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
