MySQL 不允许 ON UPDATE CURRENT_TIMESTAMP 用于 DATETIME 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12882663/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL is not allowing ON UPDATE CURRENT_TIMESTAMP for a DATETIME field
提问by Michael Plautz
I have seen a lot of related questions, but I cannot place my finger on this specific question:
我看过很多相关的问题，但我无法将手指放在这个具体问题上：
I have a MySQL table with both a TIMESTAMP (for when the field was created) and a DATETIME (for each time the field gets updated). It looks like this:
我有一个 MySQL 表，其中包含 TIMESTAMP（用于创建字段的时间）和 DATETIME（用于每次更新字段时）。它看起来像这样：
CREATE TABLE 'vis' (
ID BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
ENTRY VARCHAR(255),
AUTHOR VARCHAR(255),
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UPDATED_AT DATETIME ON UPDATE CURRENT_TIMESTAMP,
UPDATED_BY VARCHAR(255)
)
When I try this though, the error I am getting is: (SQL Error: 1294 SQL State: HY000) - Invalid ON UPDATE clause for 'updated_at' field
当我尝试这个时，我得到的错误是： (SQL Error: 1294 SQL State: HY000) - Invalid ON UPDATE clause for 'updated_at' field
Everywhere I have read (even on Stack Overflow) suggests I should be able to do this, yet I am getting this error. Perhaps there is another way to have a field that automatically updates the time each time I update it?
我读过的任何地方（甚至在 Stack Overflow 上）都表明我应该能够做到这一点，但我收到了这个错误。也许还有另一种方法可以让一个字段在我每次更新时自动更新时间？
I am using MySQL Server 5.5.
我正在使用 MySQL 服务器 5.5。
回答by slugonamission
DATETIMEcannot use CURRENT_TIMESTAMPon update. Instead, change it to a TIMESTAMP.
DATETIME不能CURRENT_TIMESTAMP在更新时使用。相反，将其更改为TIMESTAMP.
Or, consider using a trigger for this situation: http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html
或者，考虑在这种情况下使用触发器：http: //dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html
EDIT: As in the comments (thanks @АлександрФишер!), this is no longer the case since MySQL 5.6.5, so another solution is to upgrade your server if possible.
编辑：在评论中（感谢@АлександрФишер！），自 MySQL 5.6.5 以来不再是这种情况，因此另一个解决方案是如果可能的话升级您的服务器。
回答by rich remer
That feature appears to have been introduced in 5.6. Works as expected on my default OS X install.
该功能似乎已在 5.6 中引入。在我的默认 OS X 安装上按预期工作。
Reference: Automatic Timestamp Properties Before MySQL 5.6.5
回答by Anshu
回答by SandroMarques
INSERT and UPDATE date/time automatically
自动插入和更新日期/时间
Works with data type: DATETIME or TIMESTAMP
适用于数据类型：DATETIME 或 TIMESTAMP
Tested on: MySQL 5.6.27-2 and MariaDB 10.1.10
测试：MySQL 5.6.27-2 和 MariaDB 10.1.10
Stores the current date and time on INSERT
在INSERT上存储当前日期和时间
CREATE TABLE table_demo (
...
`CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP
...
);
Stores the current date and time on INSERTand UPDATE
在INSERT和UPDATE上存储当前日期和时间
CREATE TABLE table_demo (
...
`UpdatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
...
);
Stores the current date and time only on UPDATE
仅在UPDATE上存储当前日期和时间
NOTE: when INSERT, the default value is '0000-00-00 00:00:00'
注意：插入时，默认值为“0000-00-00 00:00:00”
CREATE TABLE table_demo (
...
`UpdatedAt` datetime DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
...
);
回答by Shihab Uddin
Look my picture.
看我的图。
Set column Filed Type timestamp, Attributes on Update CURRENT_TIMESTAMP& Extra also . No problem after changed the filed type in Database, in my case . But not sure for all case. 
设置列字段类型时间戳，更新 CURRENT_TIMESTAMP和额外的属性。更改数据库中的归档类型后没问题，就我而言。但不确定所有情况。
回答by u5583952
The TIMESTAMP and (as of MySQL 5.6.5) DATETIME data types offer automatic initialization and updating to the current date and time. For more information, see Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
TIMESTAMP 和（从 MySQL 5.6.5 开始）DATETIME 数据类型提供自动初始化和更新到当前日期和时间。有关更多信息，请参阅第 11.3.5 节，“TIMESTAMP 和 DATETIME 的自动初始化和更新”。
回答by FSP
Yeah, and if you change it to timestamp , with neither DEFAULT CURRENT_TIMESTAMPnor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMPand ON UPDATE CURRENT_TIMESTAMP.
是的，如果您将其更改为时间戳，既不使用DEFAULT CURRENT_TIMESTAMP也不ON UPDATE CURRENT_TIMESTAMP，则与同时指定DEFAULT CURRENT_TIMESTAMP和相同ON UPDATE CURRENT_TIMESTAMP。
