Workaround for only one Mysql CURRENT_TIMESTAMP

Question

So MySQL will only allow a single field to use CURRENT_TIMESTAMP as the default. I recently (and accidentally) discovered a workaround to this by following the instructions here.

Basically, create a table myTable that has a Timestamp column (ts1) which uses CURRENT_TIMESTAMP for the default, and one (ts2) that defaults to 0. Then run

ALTER TABLE myTable ALTER COLUMN ts2 DROP DEFAULT

After doing this, ts2 will default to CURRENT_TIMESTAMP, as will ts1.

Can anyone explain what is going on behind the scenes here? Will this end up causing problems and breaking things?

What does DESCRIBE tbl tell you about `ts1` and `ts2` after you do this? — pilcrow, Oct 02 '13 at 21:39
Good question! It says that ts1 is not `NULL` and defaults to `CURRENT_TIMESTAMP`, and ts2 is not `NULL` and defaults to `NULL` — MirroredFate, Oct 02 '13 at 21:46

score 1 · Accepted Answer · answered Oct 03 '13 at 00:53

The behavior is not as spooky as it seems.

In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

By removing the default '0' from a TIMESTAMP that is NOT NULL, you're inserting the current timestamp instead of the all-zeroes placeholder.

Presumably, your insert is specifying that timestamp column by name in the list of columns, and you are explicitly inserting a NULL there, triggering nothing more than documented behavior on a not-quite-provisioned-as-anybody-intended column. So, no, don't use this.

I say "presumably" because if I don't explicitly list the column, I get this:

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1364 | Field 'ts2' doesn't have a default value |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

...and my NOT NULL (no default) 2nd timestamp initializes to the all 0's value.

If you're not doing that, then I'de be curious to know your specific MySQL Server version.

At any rate, though, if you need more flexible timestamps, upgrade to MySQL 5.6. The restriction on a single automatic timestamp column per table has been lifted, and timestamp columns also support optional millisecond and microsecond granularity.

Workaround for only one Mysql CURRENT_TIMESTAMP

1 Answers1