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

MySQL update kills timestamp fields?

Comments[4] by Dave in General

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?


Like this post?  del.icio.us Bookmark it   |   submit to dig digg.com Digg it   |   slashdot Slashdot it   |   technorati See who links to it

Comments:

<p>The <a href="http://dev.mysql.com/doc/mysql/en/TIMESTAMP_pre-4.1.html">Fine Manual</a> does a decent job of explaining why:</p> <blockquote> <p> Automatic updating of the first <code>TIMESTAMP</code> column in a table occurs under any of the following conditions: </p> <ul> <li>You explicitly set the column to <code>NULL</code>.</li> <li>The column is not specified explicitly in an <code>INSERT</code> or <code>LOAD DATA INFILE</code> statement.</li> <li>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.</li> </ul> </blockquote>

Posted by John Beimler on May 29, 2004 at 09:11 AM EDT #

Serves me right.

Posted by Dave Johnson on May 29, 2004 at 09:26 AM EDT #

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

Posted by Koz on May 30, 2004 at 05: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 09:19 AM EDT #

Post a Comment:
  • HTML Syntax: Allowed

This work is licensed under a Creative Commons License.
Copyright 2002-2007, David M Johnson (dave.johnson at rollerweblogger.org)

This is a personal weblog, I do not speak for my employer.