MySQL 将 NOW() 设置为日期时间数据类型的默认值？

Set NOW() as Default Value for datetime datatype?

提问by Ibrahim Azhar Armar

I have two columns in table users namely registerDate and lastVisitDatewhich consist of datetime data type. I would like to do the following.

我在表用户中有两列，即registerDate and lastVisitDate由日期时间数据类型组成。我想做以下事情。

1. Set registerDate defaults value to MySQL NOW()
2. Set lastVisitDate default value to 0000-00-00 00:00:00Instead of null which it uses by default.

1. 将 registerDate 默认值设置为 MySQL NOW()
2. 将 lastVisitDate 默认值设置为0000-00-00 00:00:00它默认使用的而不是 null。

Because the table already exists and has existing records, I would like to use Modify table. I've tried using the two piece of code below, but neither works.

因为表已经存在并且有现有记录，所以我想使用修改表。我试过使用下面的两段代码，但都不起作用。

ALTER TABLE users MODIFY registerDate datetime DEFAULT NOW()
ALTER TABLE users MODIFY registerDate datetime DEFAULT CURRENT_TIMESTAMP;

It gives me Error : ERROR 1067 (42000): Invalid default value for 'registerDate'

它给了我错误： ERROR 1067 (42000): Invalid default value for 'registerDate'

Is it possible for me to set the default datetime value to NOW() in MySQL?

我可以在 MySQL 中将默认日期时间值设置为 NOW() 吗？

回答by Johan

As of MySQL 5.6.5, you can use the DATETIMEtype with a dynamic default value:

从 MySQL 5.6.5 开始，您可以使用DATETIME具有动态默认值的类型：

CREATE TABLE foo (
    creation_time      DATETIME DEFAULT   CURRENT_TIMESTAMP,
    modification_time  DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Or even combine both rules:

或者甚至结合这两个规则：

modification_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Reference:
http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

参考：
http: //dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available .html

Prior to 5.6.5, you need to use the TIMESTAMPdata type, which automatically updates whenever the record is modified. Unfortunately, however, only one auto-updated TIMESTAMPfield can exist per table.

在 5.6.5 之前，您需要使用TIMESTAMP数据类型，它会在记录被修改时自动更新。然而不幸的是，TIMESTAMP每个表只能存在一个自动更新的字段。

CREATE TABLE mytable (
  mydate TIMESTAMP
)

See: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

见：http: //dev.mysql.com/doc/refman/5.1/en/create-table.html

If you want to prevent MySQL from updating the timestamp value on UPDATE(so that it only triggers on INSERT) you can change the definition to:

如果您想阻止 MySQL 更新时间戳值 on UPDATE（以便它只触发 on INSERT），您可以将定义更改为：

CREATE TABLE mytable (
  mydate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

回答by wardk

I use a trigger as a workaround to set a datetime field to NOW() for new inserts:

我使用触发器作为一种变通方法，将日期时间字段设置为 NOW() 以进行新插入：

CREATE TRIGGER `triggername` BEFORE INSERT ON  `tablename` 
FOR EACH ROW 
SET NEW.datetimefield = NOW()

it should work for updates too

它也应该适用于更新

Answers by Johan & Leonardo involve converting to a timestamp field. Although this is probably ok for the use case presented in the question (storing RegisterDate and LastVisitDate), it is not a universal solution. See datetime vs timestampquestion.

Johan & Leonardo 的回答涉及转换为时间戳字段。尽管这对于问题中提出的用例（存储 RegisterDate 和 LastVisitDate）可能没问题，但它不是通用的解决方案。请参阅日期时间与时间戳问题。

回答by Leonardo

My solution

我的解决方案

ALTER TABLE `table_name` MODIFY COLUMN `column_name` TIMESTAMP NOT
NULL DEFAULT CURRENT_TIMESTAMP;

回答by Augiwan

EUREKA !!!

尤里卡！！！

---

---

For all those who lost heart trying to set a default DATETIME value in MySQL, I know exactly how you feel/felt. So here it is:

对于所有试图在 MySQL 中设置默认 DATETIME 值而失去信心的人，我确切地知道您的感受/感受。所以这里是：

`ALTER TABLE  `table_name` CHANGE `column_name` DATETIME NOT NULL DEFAULT 0

Carefully observe that I haven't added single quotes/double quotesaround the 0.

仔细观察我没有在0周围添加单引号/双引号。

---

---

Important update:

重要更新：

This answer was posted long back. Back then, it worked on my (probably latest) installation of MySQL and I felt like sharing it. Please read the comments below before you decide to use this solution now.

这个答案是很久以前发布的。当时，它适用于我（可能是最新的）MySQL 安装，我想分享它。在您决定现在使用此解决方案之前，请阅读下面的评论。

回答by Jean Rajotte

mysql 5.6 docs say that CURRENT_TIMESTAMP can be used as default for both TIMESTAMP and DATETIME data types:

mysql 5.6 文档说 CURRENT_TIMESTAMP 可以用作 TIMESTAMP 和 DATETIME 数据类型的默认值：

http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

回答by Matteius

On versions mysql 5.6.5 and newer, you can use precise datetimes and set default values as well. There is a subtle bit though, which is to pass in the precision value to both the datetime and the NOW() function call.

在 mysql 5.6.5 及更新版本上，您可以使用精确的日期时间并设置默认值。但是有一个微妙的地方，就是将精度值传递给 datetime 和 NOW() 函数调用。

This Example Works:

这个例子有效：

    ALTER TABLE my_table MODIFY created datetime(6) NOT NULL DEFAULT NOW(6);

This Example Does not Work:

这个例子不起作用：

    ALTER TABLE my_table MODIFY created datetime(6) NOT NULL DEFAULT NOW();

回答by veelen

`ALTER TABLE  `table_name` CHANGE `column_name` 
    timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP

Can be used to update the timestamp on update.

可用于在更新时更新时间戳。

回答by Lucas Moyano Angelini

The best way is using "DEFAULT 0". Other way:

最好的方法是使用“DEFAULT 0”。另一种方式：

    /************ 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 Eagle_Eye

ALTER TABLE table_name
  CHANGE COLUMN date_column_name date_column_name DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Finally, This worked for me!

最后，这对我有用！

回答by Gvs Akhil

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime DEFAULT CURRENT_TIMESTAMP,
  `dateUpdated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mobile_UNIQUE` (`mobile`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;