MySQL 创建和更新时的 CURRENT_TIMESTAMP
我想定义一个有 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
)
如何执行此操作以避免错误：
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
要点是在表架构中保留 ts_create 和 ts_update 的所需行为。
I want to define table which will have 2 TIMESTAMP fields, someting like this:
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_create and ts_update in table schema.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
我猜这是一篇旧文章，但实际上我猜 mysql 在其最新版本 mysql 5.6.25 中支持 2 个时间戳，这就是我现在使用的。
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.
我认为使用以下技术是可能的
i think it is possible by using below technique
您正在使用较旧的 MySql 版本。将你的 myqsl 更新到 5.6.5+ 就可以了。
You are using older MySql version. Update your myqsl to 5.6.5+ it will work.
表中不能有两个具有相同默认值 CURRENT_TIMESTAMP 的 TIMESTAMP 列。请参考此链接：http://www.mysqltutorial.org/mysql-timestamp.aspx
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
我想你可能想要 ts_create 作为日期时间（所以重命名 -> dt_create）并且只希望 ts_update 作为时间戳？这将确保它一旦设置就保持不变。
我的理解是，日期时间是手动控制的值，而时间戳有点“特殊”，因为MySQL会为你维护它。在这种情况下，日期时间是 ts_create 的不错选择。
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.
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.
我想说你不需要在 ts_update 上有 DEFAULT CURRENT_TIMESTAMP：如果它是空的，那么它就不会更新，所以你的“最后更新”是 ts_create。
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.
这是旧版本中Mysql的微小限制，实际上在5.6版本及更高版本之后，多个时间戳起作用了......
This is the tiny limitation of Mysql in older version , actually after version 5.6 and later multiple timestamps works...
你可以试试这个
ts_create时间戳默认 CURRENT_TIMESTAMP，ts_update更新时的时间戳默认为 CURRENT_TIMESTAMPyou can try this
ts_createTIMESTAMP DEFAULT CURRENT_TIMESTAMP,ts_updateTIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP