I want to have one table with two TIMESTAMP
columns. One column to keep track of when the record was created and another to keep track of when it was modified. I want these values handled by the database. I don't want my app layer to have to think about it.
I know that if you have a TIMESTAMP
column with a DEFAULT CURRENT_TIMESTAMP
or an ON UPDATE CURRENT_TIMESTAMP
you cannot have another TIMESTAMP
column. You can use DATETIME
but there is no way to default it, that I know of, outside of a trigger.
I found that you can have multiple TIMESTAMP
columns by leaving each without DEFAULT
or ON UPDATE
and inserting NULL
when the record is created, causing each to have the current timestamp. From that point on the f开发者_运维知识库irst column will automatically be updated.
This works fantastically but it leaves me with a funny feeling. Like this may be a bug and it could be patched at any time. If this is the way it is supposed to work then so be it. I will merrily go on my way. Can anyone tell me if this is the best way to do this or should I be using triggers?
It's documented in the MySQL docs:
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.
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
MySQL versions before 5.6.1 would not let two TIMESTAMP
columns in the same table, unless as you rightly noted with out defaults and allowing null.
MySQL 5.6.+ allows two or more TIMESTAMP
columns in a table.
More here: http://shankargopal.blogspot.in/2013/03/mysql-566-timestamp-columns-and-default.html
MySQL allows more than one TIMESTAMP columns in the same table, check this example:
CREATE TABLE t1 (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
ts1 TIMESTAMP NULL,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
ts1 TIMESTAMP NULL DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
Note that the ts1 TIMESTAMP column is DEFAULT with VALUE 0, and the ts2 TIMESTAMP column is DEFAULT with value CURRENT_TIMESTAMP. More info here http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
精彩评论