mysql - sysdatetime() 错误

Question

+-------------------------+
| Tables_in_movierentaldb |
+-------------------------+
| MEMBERSHIP              |
| PRICE                   |
| RENTAL                  |
| movie                   |
| video                   |
+-------------------------+

mysql> describe rental
    -> ;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| RENT_NUM  | int(11) | NO   | PRI | NULL    |       |
| MEM_NUM   | int(11) | YES  | MUL | NULL    |       |
+-----------+---------+------+-----+---------+-------+

你好 stackoverflow 社区，我在 mysql 中创建表时遇到问题，我正在尝试输入:

alter table RENTAL add RENT_DATE datetime default SYSDATETIME(); 

我遇到语法错误。

创建表时我没有输入的原因是因为在创建表命令中尝试输入它时遇到相同的语法问题:

CREATE TABLE RENTAL (
RENT_NUM     int PRIMARY KEY,
RENT_DATE   DATE DEFAULT SYSDATETIME(),
MEM_NUM int CONSTRAINT RENTAL_MEM_NUM_FK REFERENCES MEMBERSHIP);

我在约束 MEM_NUM 方面遇到了同样的问题，但是当我输入以下命令时，它能够创建约束:

alter table RENTAL add foreign key (MEM_NUM) references
MEMBERSHIP(MEM_NUM);

感谢您的宝贵时间!

Answer 1

你必须使用:

alter table RENTAL add RENT_DATE datetime default CURRENT_TIMESTAMP; 

The DEFAULT value clause in a data type specification indicates 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 as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for TIMESTAMP and DATETIME columns. See Section 12.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

DOCs here