如何在check子句中使用CURDATE()? [英] How to use CURDATE() in check clause?

问题描述

我尝试创建一个表，其中"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文档中，

允许使用文字，确定性内置函数和运算符.如果给定表中的相同数据，则独立于所连接的用户，如果多次调用产生相同的结果，则该函数为确定性函数.不确定性且未通过此定义的函数示例: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

这篇关于如何在check子句中使用CURDATE()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！