本文介绍了如何在check子句中使用CURDATE()?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试创建一个表,其中"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文档中,
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()?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!