设置NOW()作为datetime datetime数据类型的默认值?

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

我在表用户中有两列，即registerDate和lastVisitDate，它们由datetime数据类型组成。我想做以下事情。

1. Set registerDate defaults value to MySQL NOW()
2. 设置registerDate默认值到MySQL ()
3. Set lastVisitDate default value to 0000-00-00 00:00:00 Instead of null which it uses by default.
4. 将lastVisitDate默认值设置为000000 -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.

由于该表已经存在，并且已有记录，所以我希望使用Modify表。我尝试过使用下面的两段代码，但是这两段都不行。

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):“registerDate”的无效默认值

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

是否可以将MySQL中的默认datetime值设置为NOW() ?

12 个解决方案

#1

As of MySQL 5.6.5, you can use the DATETIME type 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 TIMESTAMP data type, which automatically updates whenever the record is modified. Unfortunately, however, only one auto-updated TIMESTAMP field can exist per table.

在5.6.5之前，您需要使用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更新更新更新时的时间戳值(以便它只在插入时触发)，可以将定义更改为:

CREATE TABLE mytable (
  mydate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

#2

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

我使用一个触发器作为一个工作区来为新插入设置一个datetime字段():

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 timestamp question.

约翰和列奥纳多的回答涉及到转换到时间戳字段。尽管这对于问题中出现的用例(存储RegisterDate和LastVisitDate)来说是可以的，但它不是通用的解决方案。请参阅datetime与timestamp问题。

#3

My solution

我的解决方案

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

#4

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 quotes around 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安装上运行，我很想分享它。在您决定使用此解决方案之前，请阅读下面的评论。

#5

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可以作为默认的时间戳和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

#6

The best way is make a trigger:

最好的办法是做一个触发器:

/************ 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();

#7

`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.

可用于更新时的时间戳。

#8

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();

#9

Not sure if this is still active but here goes.

不确定它是否仍然活跃，但这里。

Regarding setting the defaults to Now(), I don't see that to be possible for the DATETIME data type. If you want to use that data type, set the date when you perform the insert like this:

关于将默认值设置为Now()，我认为DATETIME数据类型不可能使用这种方法。如果您想使用该数据类型，请在执行插入操作时设置日期:

INSERT INTO Yourtable (Field1, YourDateField) VALUES('val1', (select now()))

My version of mySQL is 5.5

我的mySQL版本是5。5

#10

This worked for me, using MySQL:

这对我很有效，使用MySQL:

ALTER TABLE `table_name` MODIFY `column_name` datetime NOT NULL DEFAULT NOW();

#11

This worked for me - just changed INSERT to UPDATE for my table.

这对我来说是可行的——只是更改了INSERT以更新我的表。

INSERT INTO Yourtable (Field1, YourDateField) VALUES('val1', (select now()))

#12

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

Finally, This worked for me!

最后，这对我起作用了!