Blogging Roller: MySQL update kills timestamp fields?

MySQL update kills timestamp fields?

I've got a table that looks like this:

create table comment (
    id         varchar(48) not null primary key,
    entryid    varchar(48) not null,
    name       varchar(255) null,
    email      varchar(255) null,
    url        varchar(255) null,
    content    text null,
    posttime   timestamp not null,
    spam       bit default 0 not null,
    remotehost varchar(128) null
);

And I want to set the spam field in all rows to false like so:

update comment set spam=false;

But that command also resets all of the posttime fields in the table to the current time. If I use the following command, my posttime fields are preserved.

update comment set spam=false, posttime=posttime;

What's up with that? Why does an update of one field affect other fields?

Dave Johnson in General • 🕒 03:57AM May 29, 2004

Tags: General

Comments:

The Fine Manual does a decent job of explaining why:

Automatic updating of the first <code>TIMESTAMP</code> column in a table occurs under any of the following conditions:

You explicitly set the column to <code>NULL</code>.

The column is not specified explicitly in an <code>INSERT</code> or <code>LOAD DATA INFILE</code> statement.

The column is not specified explicitly in an <code>UPDATE</code> statement and some other column changes value. An <code>UPDATE</code> that sets a column to the value it already has does not cause the <code>TIMESTAMP</code> column to be updated; if you set a column to its current value, MySQL ignores the update for efficiency.

Posted by John Beimler on May 29, 2004 at 12:11 PM EDT #

Serves me right.

Posted by Dave Johnson on May 29, 2004 at 12:26 PM EDT #

It looks like maybe you shouldn't be using the timestamp type here?

Posted by Koz on May 30, 2004 at 08:19 PM EDT #

What you want is DATETIME field. This type doesn't update in any operation if not explicitly mentioned. TIMESTAMP is for register the time when the database's row is altered.

Posted by Vinicios Torres on May 31, 2004 at 12:19 PM EDT #