gpt4 book ai didi

MySQL 触发器开始日期早于结束日期

转载 作者:行者123 更新时间:2023-11-29 11:50:51 24 4
gpt4 key购买 nike

我的服务器运行 MySQL 5.5.40 & 我目前有一个触发器,它首先检查两个日期列(discount_start 和discount_end)是否均为 NULL 或两者都有日期,然后检查discount_start 是否大于discount_end,其想法是确保开始日期不晚于结束日期。

第一位有效,但第二位检查开始日期是否晚于结束日期似乎不起作用。我已经进行了一些 Google 搜索,看来这里允许使用 > 运算符?

我使用的日期时间是discount_start的2015-12-10 04:16:34和disocunt_end的日期时间是2015-12-08 04:16:38,我编写的触发器如下。

# Both discount_start & discount_end must either be entered or NULL, one cannot be NULL while the other is entered
IF NEW.discount_start IS NULL AND NEW.discount_end IS NOT NULL THEN
signal sqlstate '45000' set message_text = 'discount_end has value but discount_start NULL, both need to have a value or be NULL';
ELSEIF NEW.discount_start IS NOT NULL AND NEW.discount_end IS NULL THEN
signal sqlstate '45000' set message_text = 'discount_start has value but discount_end NULL, both need to have a value or be NULL';
# discount_start date must be before discount_end
ELSEIF NEW.discount_start > NEW.discount_end THEN
signal sqlstate '45000' set message_text = 'discount_start must be before discount_end';
ELSE
SET new.created = NOW();
END IF

最佳答案

尝试

... ELSEIF DATEDIFF(NEW.discount_end, NEW.discount_start) < 0 THEN ...

触发代码中允许使用关系运算符(<、> 等),因此我假设您在 google 上搜索的答案专门指比较 DATE 类型。

关于MySQL 触发器开始日期早于结束日期,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34080877/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com