我在表用户中有两列,即 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.
0000-00-00 00:00:00
而不是它默认使用的 null.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
)
这篇关于将 NOW() 设置为日期时间数据类型的默认值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!