MySQL update kills timestamp fields?

« JRoller upgrade this... | Main | JRoller upgrade on... »

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 Comments [4]
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 #

Welcome

This is just one entry in the weblog Blogging Roller. You may want to visit the main page of the weblog

Related entries

Below are the most recent entries in the category General, some may be related to this entry.