我们是否可以在mysql中,将Date字段的默认值设置为CURRENT_DATE(当前日期)?
CREATE TABLE `t_order` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`create_time` date DEFAULT (curdate()),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(curdate()), PRIMARY KEY (id
) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4′ at line 3
With one exception, the default value specified in a DEFAULT clause must be a literal 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 as NOW() or CURRENT_DATE. The exception is that, for TIMESTAMP and DATETIME columns, you can specify CURRENT_TIMESTAMP as the default. See Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
文档地址:https://dev.mysql.com/doc/refman/8.0/en/data–type–defaults.html
Date字段不能指定默认值now()或者CURRENT_DATE,但是我们可以使用TIMESTAMP或者DATETIME字段,指定默认值为CURRENT_TIMESTAMP。
但是从8.0.13版本开始可以指定DATE的默认值为的CURRENT_DATE。
需要注意的是指定CURRENT_DATE为默认值时,需要用括号() 包裹住CURRENT_DATE,但是timestamp或者datetime字段则不需要。
例如,
CREATE TABLE t (d DATETIME DEFAULT CURRENT_TIMESTAMP);
这是合法的。
CREATE TABLE t (d DATE DEFAULT (CURRENT_DATE));
因为default子句中指定的默认值通常是常量或者表达式。但是,需要将表达式默认值括在圆括号内,以区别常量默认值。
The default value specified in a DEFAULT clause can be a literal constant or an expression. With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values.
例如:
CREATE TABLE t1 (
-- literal defaults
i INT DEFAULT 0,
c VARCHAR(10) DEFAULT '',
-- expression defaults
f FLOAT DEFAULT (RAND() * RAND()),
b BINARY(16) DEFAULT (UUID_TO_BIN(UUID())),
d DATE DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
p POINT DEFAULT (Point(0,0)),
j JSON DEFAULT (JSON_ARRAY())
);
总结
mysql中,8.0.13版本之后的允许将Date字段的默认值设置为CURRENT_DATE(当前日期),但是之前的版本则不允许。设置CURRENT_DATE默认值,需要将其包裹在圆括号内,以区分常量默认值,否则将报语法错误。
参考文档: https://dev.mysql.com/doc/refman/8.0/en/data–type–defaults.html https://stackoverflow.com/questions/20461030/current–date–curdate-not-working-as–default-date-value/54119983#54119983
点个“赞 or 在看” 你最好看!
喜欢,就关注我吧!
原文地址:https://blog.csdn.net/qq_35462323/article/details/131290506
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.7code.cn/show_40164.html
如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!