Automatically Updating Time Stamp Columns In MySql
Monday, 23rd July 2012
It is often useful to have a column that indicated the last update date of a record on a table. It is also useful to update this column automatically, rather than having to rely on the application updating the value.
There is a really simple way to do this in MySql, using a column defined like this:
LastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
You can see this in action in the following example.
Firstly, let's create a table that includes our LastUpdate column.
CREATE TABLE t1 ( a VARCHAR(50), b VARCHAR(50), c VARCHAR(50), LastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
And we'll add a row to the table.
INSERT INTO t1 (a, b, c) VALUES ('a', 'b', 'c');
We haven't specified a value for LastUpdate in our query, so it will use the default value, which is CURRENT_TIMESTAMP. We can see this by looking at the row.
SELECT * FROM t1;
Now let's update any column except our LastUpdate column and see what happens.
UPDATE t1 SET a = 'd' WHERE a = 'a';
If we look again at our row, we'll see the LastUpdate has been moved on.
SELECT * FROM t1;
The only downside is that you can't have two columns for this information. My preference would be to have a "Created" column with a default value of CURRENT_TIMESTAMP and a "LastUpdated" column with an ON UPDATE CURRENT_TIMESTAMP value, but according to the MySql documentation:
It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.
And I even tested this to make sure it was true - if you try and add a table with two columns using CURRENT_TIMESTAMP, you get an error.