MySQL CURRENT_DATE/CURDATE() 不作为默认 DATE 值工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20461030/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CURRENT_DATE/CURDATE() not working as default DATE value
提问by inControl
Pretty straight forward question here, I think this should work but it doesn't. Why doesn't it?
这里很直接的问题，我认为这应该有效，但事实并非如此。为什么不呢？
CREATE TABLE INVOICE(
INVOICEDATE DATE NOT NULL DEFAULT CURRENT_DATE
)
回答by zerkms
It doesn't work because it's not supported
它不起作用，因为它不受支持
The
DEFAULTclause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such asNOW()orCURRENT_DATE. The exception is that you can specifyCURRENT_TIMESTAMPas the default for aTIMESTAMPcolumn
该
DEFAULT子句指定列的默认值。除了一个例外，默认值必须是一个常量；它不能是函数或表达式。这意味着，例如，您不能将日期列的默认值设置为函数的值，例如NOW()或CURRENT_DATE。例外是您可以指定CURRENT_TIMESTAMP为TIMESTAMP列的默认值
回答by user3630264
declare your date column as NOT NULL, but without a default. Then add this trigger:
将您的日期列声明为 NOT NULL，但没有默认值。然后添加这个触发器：
USE `ddb`;
DELIMITER $$
CREATE TRIGGER `default_date` BEFORE INSERT ON `dtable` FOR EACH ROW
if ( isnull(new.query_date) ) then
set new.query_date=curdate();
end if;
$$
delimiter ;
回答by Amos Long
According to this documentation, starting in MySQL 8.0.13, you will be able to specify:
根据此文档，从 MySQL 8.0.13 开始，您将能够指定：
CREATE TABLE INVOICE(
INVOICEDATE DATE DEFAULT (CURRENT_DATE)
)
Unfortunately, as of today, that version is not yet released. You can check herefor the latest updates.
不幸的是，截至今天，该版本尚未发布。您可以在此处查看最新更新。
回答by Brian Purgert
create table the_easy_way(
capture_ts DATETIME DEFAULT CURRENT_TIMESTAMP,
capture_dt DATE AS (DATE(capture_ts))
)
(MySQL 5.7)
(MySQL 5.7)
回答by Rick James
----- 2016-07-04 MariaDB 10.2.1 -- Release Note -- -----
----- 2016-07-04 MariaDB 10.2.1 -- 发行说明 -- -----
Support for DEFAULT with expressions(MDEV-10134).
支持带有表达式的 DEFAULT( MDEV-10134)。
----- 2018-10-22 8.0.13 General Availability -- -- -----
----- 2018-10-22 8.0.13 全面上市 -- -- -----
MySQL now supports use of expressions as default values in data type specifications. This includes the use of expressions as default values for the BLOB, TEXT, GEOMETRY, and JSONdata types, which previously could not be assigned default values at all. For details, see Data Type Default Values.
MySQL 现在支持使用表达式作为数据类型规范中的默认值。这包括使用表达式作为BLOB、TEXT、 GEOMETRY 和JSON数据类型的默认值，这些数据类型以前根本无法分配默认值。有关详细信息，请参阅数据类型默认值。
回答by Niels Keurentjes
As the other answer correctly notes, you cannot use dynamic functions as a default value. You could use TIMESTAMPwith the CURRENT_TIMESTAMPattribute, but this is not always possible, for example if you want to keep both a creationand updatedtimestamp, and you'd need the only allowed TIMESTAMPcolumn for the second.
正如另一个答案正确指出的那样，您不能使用动态函数作为默认值。您可以TIMESTAMP与CURRENT_TIMESTAMP属性一起使用，但这并不总是可行的，例如，如果您想同时保留创建和更新的时间戳，并且您需要TIMESTAMP第二个唯一允许的列。
In this case, use a trigger instead.
在这种情况下，请改用触发器。
回答by Arun
I came to this page with the same question in mind, but it worked for me!, Just thought to update here , may be helpful for someone later!!
我带着同样的问题来到这个页面，但它对我有用！，只是想在这里更新，以后可能会对某人有所帮助！！
MariaDB [niffdb]> desc invoice;
+---------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------+------+-----+---------+----------------+
| inv_id | int(4) | NO | PRI | NULL | auto_increment |
| cust_id | int(4) | NO | MUL | NULL | |
| inv_dt | date | NO | | NULL | |
| smen_id | int(4) | NO | MUL | NULL | |
+---------+--------+------+-----+---------+----------------+
4 rows in set (0.003 sec)
MariaDB [niffdb]> ALTER TABLE invoice MODIFY inv_dt DATE NOT NULL DEFAULT (CURRENT_DATE);
Query OK, 0 rows affected (0.003 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [niffdb]> desc invoice;
+---------+--------+------+-----+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------+------+-----+-----------+----------------+
| inv_id | int(4) | NO | PRI | NULL | auto_increment |
| cust_id | int(4) | NO | MUL | NULL | |
| inv_dt | date | NO | | curdate() | |
| smen_id | int(4) | NO | MUL | NULL | |
+---------+--------+------+-----+-----------+----------------+
4 rows in set (0.002 sec)
MariaDB [niffdb]> SELECT VERSION();
+---------------------------+
| VERSION() |
+---------------------------+
| 10.3.18-MariaDB-0+deb10u1 |
+---------------------------+
1 row in set (0.010 sec)
MariaDB [niffdb]>
