MySQL: Updating entry WITHOUT updating timestamp

问题:

I have a timestamp in a mysql table with attribute "ON UPDATE CURRENT_TIMESTAMP". Is there a way to manually disable updating the timestamp on a special occasion? (eg: updating the entry to revise a blog post, but not to re-date it)

回答1:

Is there a way to manually disable updating the timestamp on a special occasion? (eg: updating the entry to revise a blog post, but not to re-date it)

Sounds like you need to configure the default constraint so that it populates the column on insertion only:

DEFAULT CURRENT_TIMESTAMP

Changing it to only be this means that any revisions will not trigger the timestamp value to be updated. IE: If you created the blogpost yesterday, and corrected a typo today - the date in the column would still be for yesterday.

回答2:

You can manually set the value of the column to its current value in the update command:

UPDATE table SET x=y, timestampColumn=timestampColumn WHERE a=b;

If you don't set the value in the query, it will be updated to the current timestamp as per the table definition.

回答3:

To make your table/timestamp auto-update:

ALTER TABLE myTable
CHANGE myTimestampColumn
        myTimestampColumn TIMESTAMP NOT NULL
                       DEFAULT CURRENT_TIMESTAMP 
                       ON UPDATE CURRENT_TIMESTAMP;

To make it not auto-update:

ALTER TABLE myTable
CHANGE myTimestampColumn
        myTimestampColumn TIMESTAMP NOT NULL
                       DEFAULT CURRENT_TIMESTAMP;

NOTE: The "default current_timestamp" part just sets it to the current stamp at default time, since the field is not-null. You can remove both the not null and the default, if you like.

回答4:

don't use timestamps, but track times manually.

if you really want to update a record and don't update it's timestamp use:

UPDATE `table` SET `timestamp` = `timestamp`, `col` = 'new data' …;

回答5:

If you do change timestemp on update then you have to take into your consideration that if the value was updated but not changed (updated the save value) then it will not update the "on update Current_timestemp" and on this situation you should set the timestemp manually

SET LastUpdatedDate=NOW() WHERE

The idea came from here: Touch MYSQL record to update TIMESTAMP field