时间戳(自动)何时更新? [英] When is a timestamp (auto) updated?

问题描述

如果我在 TIMESTAMP 类型的表中有一个列并且默认具有: CURRENT_TIMESTAMP 如果我更新值，该列是否会更新为当前时间戳任何列在同一行中?
似乎没有，但我不确定这是否应该发生.
我不明白这是什么意思(来自 MySQL 文档):

If I have a column in a table of type TIMESTAMP and has as default: CURRENT_TIMESTAMP does this column get updated to the current timestamp if I update the value of any other column in the the same row?
It seems that it does not but I am not sure if this is what should happen.
I can not understand what this means (from MySQL documentation):

如果列是自动更新的，它会自动更新为当前时间戳，当行中任何其他列的值为从当前值改变.如果所有列保持不变其他列设置为其当前值.为了防止列在其他列更改时更新，将其显式设置为当前值.即使其他列没有更新列更改，将其显式设置为应具有的值]2

If the column is auto-updated, it is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. The column remains unchanged if all other columns are set to their current values. To prevent the column from updating when other columns change, explicitly set it to its current value. To update the column even when other columns do not change, explicitly set it to the value it should have]2

推荐答案

给出命令 SHOW CREATE TABLE 不管

然后查看表定义.

大概有这样一行

logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

在里面.DEFAULT CURRENT_TIMESTAMP 表示任何没有明确时间戳设置的 INSERT 都使用当前时间.同样，ON UPDATE CURRENT_TIMESTAMP 意味着任何没有明确时间戳的更新都会导致对当前时间戳值的更新.

in it. DEFAULT CURRENT_TIMESTAMP means that any INSERT without an explicit time stamp setting uses the current time. Likewise, ON UPDATE CURRENT_TIMESTAMP means that any update without an explicit timestamp results in an update to the current timestamp value.

您可以在创建表格时控制此默认行为.

You can control this default behavior when creating your table.

或者，如果时间戳列一开始没有正确创建，您可以更改它.

Or, if the timestamp column wasn't created correctly in the first place, you can change it.

ALTER TABLE whatevertable
     CHANGE whatevercolumn 
            whatevercolumn TIMESTAMP NOT NULL
                           DEFAULT CURRENT_TIMESTAMP 
                           ON UPDATE CURRENT_TIMESTAMP;

这将导致表上的 INSERT 和 UPDATE 操作自动更新您的时间戳列.如果你想在不改变时间戳的情况下更新whatevertable，也就是

This will cause both INSERT and UPDATE operations on the table automatically to update your timestamp column. If you want to update whatevertable without changing the timestamp, that is,

防止列在其他列发生变化时更新

To prevent the column from updating when other columns change

那么你需要发布这种更新.

then you need to issue this kind of update.

UPDATE whatevertable
   SET something = 'newvalue',
       whatevercolumn = whatevercolumn
 WHERE someindex = 'indexvalue'

这适用于 TIMESTAMP 和 DATETIME 列.(在 MySQL 版本 5.6.5 之前，它仅适用于 TIMESTAMPs)当您使用 TIMESTAMPs 时，会考虑时区:在正确配置的服务器机器上，这些值始终以 UTC 存储并在检索时转换为本地时间.

This works with TIMESTAMP and DATETIME columns. (Prior to MySQL version 5.6.5 it only worked with TIMESTAMPs) When you use TIMESTAMPs, time zones are accounted for: on a correctly configured server machine, those values are always stored in UTC and translated to local time upon retrieval.

这篇关于时间戳(自动)何时更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！