如何在检查子句中使用 CURDATE()?

2022-01-15 00:00:00 sql database mariadb mysql

我尝试创建一个表，其中dateFrom"和dateTo"字段需要高于今天的日期.所以我像这样使用CHECK

I try to create a table where 'dateFrom' and 'dateTo' fields need to be higher than today's date. So I used CHECK like this

CREATE TABLE Booking (
       hotelNo int(10),
       guestNo int(10),
       dateFrom datetime,
       dateTo datetime,
       roomNo int(10),
       CHECK (dateFrom >= CURDATE() AND dateTo >= CURDATE())
);

但我不断收到此错误

  ERROR 1901 (HY000): Function or expression 'curdate()' cannot be used in the CHECK clause of `CONSTRAINT_1`

我在 Google 上搜索了很多次，但仍然找不到方法.

I've searched this on Google many times, but still couldn't figure out a way to do it.

推荐答案

在 MySQL 文档中，

In MySQL documentation,

允许使用文字、确定性内置函数和运算符.如果给定表中的相同数据，则函数是确定性的，多次调用产生相同的结果，独立于连接的用户.非确定性且未通过此定义的函数示例:CONNECTION_ID()、CURRENT_USER()、NOW().

Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

所以我改用了这样的触发器，

So I've used triggers like this instead,

CREATE TRIGGER date_check
BEFORE INSERT ON Booking
FOR EACH ROW
BEGIN
IF NEW.dateFrom <= CURDATE() OR NEW.dateTo <= CURDATE() THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid date!';
END IF;
END

相关文章