Automatically Updating Time Stamp Columns in MySql

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.

Related