MySQL 时间戳（自动）何时更新？

When is a timestamp (auto) updated?

提问by Jim

If I have a column in a table of type TIMESTAMPand has as default: CURRENT_TIMESTAMP does this column get updated to the current timestamp if I update the value of anyother 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):

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

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

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

回答by O. Jones

Give the command SHOW CREATE TABLE whatever

发出指令 SHOW CREATE TABLE whatever

Then look at the table definition.

然后看表定义。

It probably has a line like this

它可能有这样的一行

logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

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

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

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;

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

这将导致表上的 INSERT 和 UPDATE 操作自动更新时间戳列。如果你想在whatevertable不改变时间戳的情况下进行更新，即

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'

And, this works with TIMESTAMPcolumns only, not DATETIMEor DATEcolumns. Because the columns are 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.

而且，这一点也适用TIMESTAMP列只，而不是DATETIME或DATE列。因为列是TIMESTAMPs，所以考虑了时区：在正确配置的服务器机器上，这些值总是以 UTC 存储并在检索时转换为本地时间。

回答by juergen d

I think you have to define the timestamp column like this

我认为你必须像这样定义时间戳列

CREATE TABLE t1 
(
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

See here

看这里

回答by Sibin John Mattappallil

Add a trigger in database:

在数据库中添加触发器：

DELIMITER //
CREATE TRIGGER update_user_password 
  BEFORE UPDATE ON users
  FOR EACH ROW
    BEGIN
      IF OLD.password <> NEW.password THEN
        SET NEW.password_changed_on = NOW();
      END IF;
    END //
DELIMITER ;

The password changed time will update only when password column is changed.

密码更改时间只有在更改密码列时才会更新。

回答by Alexander

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values.

当行中任何其他列的值从其当前值更改时，自动更新的列会自动更新为当前时间戳。如果所有其他列都设置为其当前值，则自动更新的列保持不变。

To explain it let's imagine you have only one row:

为了解释它，让我们假设您只有一行：

-------------------------------
| price | updated_at          |
-------------------------------
|  2    | 2018-02-26 16:16:17 |
-------------------------------

Now, if you run the following update column:

现在，如果您运行以下更新列：

 update my_table
 set price = 2

it will not change the value of updated_at, since price value wasn't actually changed (it was already 2).

它不会改变 updated_at 的值，因为价格值实际上并没有改变（它已经是 2）。

But if you have another row with price value other than 2, then the updated_at value of that row (with price <> 3) will be updated to CURRENT_TIMESTAMP.

但是，如果您有另一行的价格值不是 2，那么该行的 updated_at 值（价格 <> 3）将更新为 CURRENT_TIMESTAMP。

回答by BrinkDaDrink

Adding where to find UPDATE CURRENT_TIMESTAMPbecause for new people this is a confusion.

添加在哪里可以找到UPDATE CURRENT_TIMESTAMP因为对于新人来说这是一个混乱。

Most people will use phpmyadmin or something like it.

大多数人会使用 phpmyadmin 或类似的东西。

Default value you select CURRENT_TIMESTAMP

您选择的默认值 CURRENT_TIMESTAMP

Attributes(a different drop down) you select UPDATE CURRENT_TIMESTAMP

您选择的属性（不同的下拉列表） UPDATE CURRENT_TIMESTAMP