创建日期类型的表并在 MySQL 上使用其默认值作为 CURRENT_DATE [英] CREATE table of date type and use its default value as CURRENT_DATE on MySQL
问题描述
我尝试在 sql 命令中运行下面的代码,但是代码行 start_date date DEFAULT CURRENT_DATE
有问题,不知道为什么.我只需要约会.
I tried running below code in sql command, but the code line start_date date DEFAULT CURRENT_DATE
had issues, got no idea why. I needed only date.
但是如果我用 start_date datetime DEFAULT CURRENT_TIMESTAMP
替换它SQL 将运行,但我的想法是我只需要存储日期而不是日期时间.
But if I replace that with start_date datetime DEFAULT CURRENT_TIMESTAMP
the SQL will run but the idea is I only need to store date and not datetime.
如何在不使用时间戳且后者仅转换为日期的情况下执行此操作?
How do I do it without using timestamp and latter converting to only date?
CREATE TABLE advertisement (
id int not null AUTO_INCREMENT,
PRIMARY KEY(id),
summary text DEFAULT null,
featured_image varchar(50) DEFAULT null,
start_date date DEFAULT CURRENT_DATE,
end_date date not null,
link text DEFAULT null,
added_date datetime DEFAULT CURRENT_TIMESTAMP,
updated_date datetime ON UPDATE CURRENT_TIMESTAMP
)
推荐答案
文档 很清楚这适用于 datetime
列,但不适用于 date
列:
The documentation is quite clear that this works for datetime
columns but not date
columns:
这意味着,例如,您不能为日期设置默认值列作为函数的值,例如 NOW()
或 CURRENT_DATE
.例外是您可以指定 CURRENT_TIMESTAMP
作为默认值用于 TIMESTAMP
和 DATETIME
列.
This means, for example, that you cannot set the default for a date column to be the value of a function such as
NOW()
orCURRENT_DATE
. The exception is that you can specifyCURRENT_TIMESTAMP
as the default forTIMESTAMP
andDATETIME
columns.
如果您真的一心想忽略时间组件,则必须使用触发器来设置该值,而不是使用 default
约束.
If you really have your heart set on ignoring the time component, you will have to use a trigger to set the value, rather than a default
constraint.
这篇关于创建日期类型的表并在 MySQL 上使用其默认值作为 CURRENT_DATE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!