时间戳(自动)何时更新？

如果在 TIMESTAMP类型的表中有一列，并且默认为 CURRENT _ TIMESTAMP，那么如果我更新了该值，该列就会更新为当前时间戳 任何的其他列在同一行？
似乎并非如此，但我不确定这是否应该发生。
我不明白这意味着什么(来自 MySQL 文档) :

如果列是自动更新的，则它将自动更新为 当行中任何其他列的值为 从其当前值更改。如果所有 将其他列设置为它们的当前值 当其他列发生更改时，将其显式设置为 即使其他列没有更新该列，也要更新该列 修改，显式地将其设置为它应该具有的值] < a href = “ http://dev.mysql.com/doc/refman/5.0/en/time戳-initialization.html”> 2

163048 次浏览
小开

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

小开
最佳答案

Give the command 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_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;

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'

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.

小开

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

Most people will use phpmyadmin or something like it.

Default value you select CURRENT_TIMESTAMP

Attributes (a different drop down) you select UPDATE CURRENT_TIMESTAMP

小开

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.

小开

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).

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.


提交答案