mysql - 如何在检查子句中使用 CURDATE()？

Question

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

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 上搜索了很多次，但仍然找不到解决方法。

Answer 1

在 MySQL 文档中，

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

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

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