“ dateAdd”的默认值无效

我在 SQL 上遇到了一个无法修复的愚蠢问题。

ALTER TABLE  `news`
ADD  `dateAdded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AUTO_INCREMENT ,
ADD PRIMARY KEY (  `dateAdded` )

错误:

(#1067)Invalid default value for 'dateAdded'

有人能帮帮我吗？

169023 次浏览
小开
最佳答案

CURRENT_TIMESTAMP is only acceptable on TIMESTAMP fields. DATETIME fields must be left either with a null default value, or no default value at all - default values must be a constant value, not the result of an expression.

relevant docs: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

You can work around this by setting a post-insert trigger on the table to fill in a "now" value on any new records.

小开

CURRENT_TIMESTAMP is version specific and is now allowed for DATETIME columns as of version 5.6.

See MySQL docs.

小开

Also do note when specifying DATETIME as DATETIME(3) or like on MySQL 5.7.x, you also have to add the same value for CURRENT_TIMESTAMP(3). If not it will keep throwing 'Invalid default value'.

小开

I have mysql version 5.6.27 on my LEMP and CURRENT_TIMESTAMP as default value works fine.

小开

mysql version 5.5 set datetime default value as CURRENT_TIMESTAMP will be report error you can update to version 5.6 , it set datetime default value as CURRENT_TIMESTAMP

小开

Change the type from datetime to timestamp and it will work! I had the same issue for mysql 5.5.56-MariaDB - MariaDB Server Hope it can help... sorry if depricated

小开

I had the same issue, following fix solved my problem.

  • Select Type as 'TIMESTAMP'

  • DON'T ENTER ANYTHING IN LENGTH/VALUES FIELD. KEEP IT BLANK

  • Select CURRENT_TIMESTAMP as Default value.

I am using MySQL ver 5.5.56

小开

I solved mine by changing DATE to DATETIME


提交答案