MySQL为什么不让我“在更新CURRENT_TIMESTAMP上"删除属性? [英] Why won't MySQL let me remove attribute "on update CURRENT_TIMESTAMP"?

问题描述

我有一个带有两个时间戳字段的表.我只是用名称和类型TIMESTAMP定义了它们，但是由于某些原因，MySQL自动使用默认值和属性on update CURRENT_TIMESTAMP设置了其中一个.我本来打算在两个字段中都没有默认值，但是其中一个字段称为"date_updated"，所以我想可以将提到的属性设置为该字段.

I have a table with two timestamp fields. I simply defined them with a name and the type TIMESTAMP, yet for some reason MySQL automatically set one of them with a default value and the attribute on update CURRENT_TIMESTAMP. I was planning on having NO default value in either of the fields, but one of the fields is called "date_updated" so I suppose I could set the mentioned attribute to that field.

不幸的是，是使用on update CURRENT_TIMESTAMP属性设置的"date_created"字段，无论我做什么，MySQL都不允许我删除它.

Unfortunately, it's the field "date_created" that was set with the on update CURRENT_TIMESTAMP attribute, and no matter what I do, MySQL won't let me remove it.

我尝试编辑"date_created"字段并删除该属性.单击保存时，该属性返回.我也尝试过选择两个字段，从其中一个字段中删除属性，然后在另一个字段上进行设置.它给了我错误#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause，并且值上的两个属性列突然都设置为on update CURRENT_TIMESTAMP结果:

I've tried editing the "date_created" field and removing the attribute. When clicking save, the attribute is back. I have also tried selecting both fields, removing the attribute from one of them and setting it on the other. It gives me the error #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause and suddenly both attribute columns on the values are set to on update CURRENT_TIMESTAMP the result:

Error
SQL query:

ALTER TABLE  `pages` CHANGE  `date_created`  `date_created` TIMESTAMP NOT NULL ,
CHANGE  `date_updated`  `date_updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL

MySQL said: 

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause 

我真的必须按照正确的顺序重新创建这两个列以解决此问题吗?

Must I really recreate both those columns in the correct order to fix this?

我想知道如何正确解决此问题，以供将来参考.

I would like to know how I could solve this problem correctly, for future reference.

谢谢

现在我也尝试着跑步

ALTER TABLE pages
CHANGE date_created
 date_created TIMESTAMP NOT NULL

推荐答案

您应指定 DEFAULT CURRENT_TIMESTAMP (或DEFAULT 0)

ALTER TABLE pages CHANGE date_created date_created TIMESTAMP NOT NULL DEFAULT 0,
CHANGE  `date_updated`  `date_updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL

这篇关于MySQL为什么不让我“在更新CURRENT_TIMESTAMP上"删除属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！