更新条目而不更新时间戳

在 mysql 表中有一个时间戳，属性为“ ON UPDATE CURRENT _ TIMESTAMP”。有没有办法在特殊情况下手动禁用更新时间戳？(例如: 更新条目以修改博客文章，但不要更新日期)

54021 次浏览
小开

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' …;
小开

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.

小开
最佳答案

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.

小开

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.

小开

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


提交答案