CREATE TABLE `test_table` (
`id` INT( 10 ) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = INNODB;
That should be simple right? Apparently not:
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
I can use CURRENT_TIMESTAMP in only one place in the table. So I can’t have MySQL do what I intended it to do -
maintain two timestamps, one to store the record creation timestamp and another to store record update timestamp. So
I thought I’ll maintain those two timestamps by myself in the app. Give me two simple TIMESTAMP fields:
CREATE TABLE `test_table` (
`id` INT( 10 ) NOT NULL,
`created_at` TIMESTAMP NOT NULL,
`updated_at` TIMESTAMP NOT NULL
) ENGINE = INNODB;
MySQL automatically set the first timestamp field to DEFAULT to CURRENT_TIMESTAMP and ON UPDATE to CURRENT_TIMESTAMP.
The second timestamp field was set to DEFAULT to 0. That was indeed strange.
I thought I found a bug in MySQL. But no, it turns out it is actually a feature! Actually, it’s a result of a
combination of features:
- “With neither
DEFAULT
CURRENT_TIMESTAMP
nor ON
UPDATE CURRENT_TIMESTAMP
, it is the same as specifying both DEFAULT
CURRENT_TIMESTAMP
and ON
UPDATE CURRENT_TIMESTAMP
.”
- “It need not be the first
TIMESTAMP
column in a table that is automatically initialized or updated to the current timestamp. However, to specify automatic initialization or updating for a different TIMESTAMP
column, you must suppress the automatic properties for the first one. Then, for the other TIMESTAMP
column, the rules for the DEFAULT
and ON
UPDATE
clauses are the same as for the first TIMESTAMP
column, except that if you omit both clauses, no automatic initialization or updating occurs.”
Infering from the above two points from the MySQL manual, if there are two (or more) TIMESTAMP fileds in a table with
no DEFAULT and ON UPDATE clauses specified, MySQL automatically defaults the first timestamp field to have a DEFAULT
CURRENT_TIMESTAMP and an ON UPDATE CURRENT_TIMESTAMP field and the rest of the timestamp fields to have a DEFAULT 0.
Now coming back to the problem at hand, I need to track the creation and update timestamps of a record. This can be
achieved using another feature:
- By default,
TIMESTAMP
columns are NOT
NULL
, cannot contain NULL
values, and assigning NULL
assigns the current timestamp.
So if I assign a NULL value to a NOT NULL TIMESTAMP field, it uses the current timestamp value instead! And that’ll
be my created_at field. The updated_at TIMESTAMP field can have DEFAULT CURRENT_TIMESTAMP and an ON UPDATE
CURRENT_TIMESTAMP to track the update time. So just to keep it a little more obvious I now use the following CREATE
TABLE:
CREATE TABLE `test_table` (
`id` INT( 10 ) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = INNODB;
The corresponding INSERT statment will be:
References:
INSERT INTO test_table (id, created_at, updated_at) VALUES (1, NULL, NULL);