创建和更新时的 MySQL CURRENT_TIMESTAMP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4897002/
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 CURRENT_TIMESTAMP on create and on update
提问by kuba
I want to define table which will have 2 TIMESTAMP fields, someting like this:
我想定义将有 2 个 TIMESTAMP 字段的表，如下所示：
CREATE TABLE `msgs` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`msg` VARCHAR(256),
`ts_create` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`ts_update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
How to do this avoiding error:
如何做到这一点避免错误：
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
Point is to keep desired behavior of ts_createand ts_updatein table schema.
要点是保持表模式的ts_create和ts_update在表模式中的所需行为。
采纳答案by avinash v p
Guess this is a old post but actually i guess mysql supports 2 TIMESTAMP in its recent editions mysql 5.6.25 thats what im using as of now.
猜猜这是一个旧帖子，但实际上我猜 mysql 在其最近版本的 mysql 5.6.25 中支持 2 TIMESTAMP，这就是我现在使用的。
回答by Himanshu Shekhar
You are using older MySql version. Update your myqsl to 5.6.5+ it will work.
您使用的是较旧的 MySql 版本。将您的 myqsl 更新到 5.6.5+ 它将起作用。
回答by iKing
i think it is possible by using below technique
我认为可以通过使用以下技术
`ts_create` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`ts_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
回答by clemquinones
You cannot have two TIMESTAMP column with the same default value of CURRENT_TIMESTAMP on your table. Please refer to this link: http://www.mysqltutorial.org/mysql-timestamp.aspx
您的表上不能有两个 TIMESTAMP 列具有相同的 CURRENT_TIMESTAMP 默认值。请参考这个链接：http: //www.mysqltutorial.org/mysql-timestamp.aspx
回答by Brian
I think you maybe want ts_create as datetime (so rename -> dt_create) and only ts_update as timestamp? This will ensure it remains unchanging once set.
我想你可能想要 ts_create 作为日期时间（所以重命名 -> dt_create）并且只想要 ts_update 作为时间戳？这将确保它在设置后保持不变。
My understanding is that datetime is for manually-controlled values, and timestamp's a bit "special" in that MySQL will maintain it for you. In this case, datetime is therefore a good choice for ts_create.
我的理解是 datetime 用于手动控制的值，而时间戳有点“特殊”，因为 MySQL 会为您维护它。在这种情况下，日期时间因此是 ts_create 的不错选择。
回答by Nanne
I would say you don't need to have the DEFAULT CURRENT_TIMESTAMP on your ts_update: if it is empty, then it is not updated, so your 'last update' is the ts_create.
我会说你不需要在你的 ts_update 上有 DEFAULT CURRENT_TIMESTAMP：如果它是空的，那么它没有更新，所以你的“最后一次更新”是 ts_create。
回答by Sonpal singh Sengar
This is the tiny limitation of Mysql in older version , actually after version 5.6 and later multiple timestamps works...
这是旧版本中 Mysql 的微小限制，实际上在 5.6 版及更高版本之后，多个时间戳有效...
回答by AJavaLover
you can try this
ts_createTIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_updateTIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
你可以试试这个
ts_createTIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_updateTIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
