MySQL MySQL默认日期时间通过phpmyadmin
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13063980/
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 default datetime through phpmyadmin
提问by Kapil Sharma
In an existing database, I've age column (INT). Now I need to set it as dob (DATETIME).
在现有数据库中，我有年龄列 (INT)。现在我需要将它设置为 dob (DATETIME)。
I try doing so through PHPMyAdmin, giving CURRENT_TIMESTAMP as default value as defined by answer with 138 upvotes. However PHPMyAdmin is complaining #1067 - invalid default value for 'dob'as in attached screenshot:
我尝试通过 PHPMyAdmin 这样做，将 CURRENT_TIMESTAMP 作为默认值，由answer with 138 upvotes定义。但是 PHPMyAdmin 正在抱怨，#1067 - invalid default value for 'dob'如附件截图所示：
Can someone please suggest why I'm getting that error and how to fix that?
有人可以建议我为什么会收到这个错误以及如何解决这个问题吗？
回答by Mithun Sreedharan
You can't setCURRENT_TIMESTAMP as default value with DATETIME.
您不能将CURRENT_TIMESTAMP设置为 DATETIME 的默认值。
But you can do it with TIMESTAMP.
但是您可以使用 TIMESTAMP 来实现。
See the difference here.
请参阅此处的区别。
Words from thisblog
来自这个博客的词
The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression.
数据类型规范中的 DEFAULT value 子句指示列的默认值。除了一个例外，默认值必须是一个常量；它不能是函数或表达式。
This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE.
这意味着，例如，您不能将日期列的默认值设置为 NOW() 或 CURRENT_DATE 等函数的值。
The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.
例外是您可以将 CURRENT_TIMESTAMP 指定为 TIMESTAMP 列的默认值。
回答by user1518659
I don't think you can achieve that with mysql date. You have to use timestamp or try this approach..
我不认为你可以用 mysql date 来实现。您必须使用时间戳或尝试这种方法..
CREATE TRIGGER table_OnInsert BEFORE INSERT ON `DB`.`table`
FOR EACH ROW SET NEW.dateColumn = IFNULL(NEW.dateColumn, NOW());
回答by Ashutosh Bajpai
Set the type of the field as TIMESTAMP too.
将该字段的类型也设置为 TIMESTAMP。
回答by Lucas Moyano Angelini
The best way for DateTime is use a Trigger:
DateTime 的最佳方法是使用触发器：
/************ ROLE ************/
drop table if exists `role`;
create table `role` (
`id_role` bigint(20) unsigned not null auto_increment,
`date_created` datetime,
`date_deleted` datetime,
`name` varchar(35) not null,
`description` text,
primary key (`id_role`)
) comment='';
drop trigger if exists `role_date_created`;
create trigger `role_date_created` before insert
on `role`
for each row
set new.`date_created` = now();
回答by Nanne
You're getting that error because the default value current_timeis not valid for the type DATETIME. That's what it says, and that's whats going on.
您收到该错误是因为默认值current_time对 type 无效DATETIME。这就是它所说的，这就是正在发生的事情。
The only field you can use current_timeon is a timestamp.
您可以使用的唯一字段current_time是时间戳。
