如何模拟MySQL表中的2个时间戳字段？

Question

I know you can only have 1 timestamp per table in mysql. But I need 2. One for when an article was created and one for everytime it's updated. The latter is the one that will be changing most of the time so I decided to make that the timestamp field. For the other one, how can I prepare the time in PHP so that it looks just like a timestamp when inserted into the database:

2011-07-29 03:28:20

And if this string is prepared in PHP, will the usual ways of formatting it work, e.g.:

    $time = strtotime($timestamp);
    $date = date('n/d/Y @ g:i a', $time);

Answer 1

You can use a datetime field in order to store insert time (use now() function of mysql). Use instead a timestamp field to store update time.

create table mytb (
id int not null auto_increment primary key,
article varchar(50),
ins_time datetime,
upd_time timestamp not null default current_timestamp on update current_timestamp
) engine = myisam;

insert into mytb (
article,ins_time) values ('bla bla bla', now());

update mytb set article = 'bla bla' where id = 1