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

2021-11-20 00:00:00 datetime sql mysql mysql-error-1067

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

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

  1. 将 registerDate 默认值设置为 MySQL NOW()
  2. 将 lastVisitDate 默认值设置为 0000-00-00 00:00:00 而不是它默认使用的 null.
  1. Set registerDate defaults value to MySQL NOW()
  2. Set lastVisitDate default value to 0000-00-00 00:00:00 Instead of null which it uses by default.

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

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;

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

是否可以在 MySQL 中将默认日期时间值设置为 NOW()?

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

推荐答案

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

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

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

参考:
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

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

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.

CREATE TABLE mytable (
  mydate TIMESTAMP
)

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

如果你想阻止 MySQL 在 UPDATE 上更新时间戳值(这样它只在 INSERT 上触发)你可以将定义更改为:

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:

CREATE TABLE mytable (
  mydate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

相关文章