MySQL 触发器内的日期差异检查

Question

在触发器中，在将一些数据插入表中之前，我希望它检查我输入的事件的日期差异是否大于或等于 1 天。一个俱乐部每天只能举办一场事件。

示例故事

如果数据库中已有 2014-01-01 19:00:00 日期，并且我尝试插入另一条具有 2014-01-01 日期的记录(时间并不重要)，它不应该允许。

触发器的部分代码

DECLARE k INT DEFAULT 0;

/* This is where I get the error, ABS is to make it always positive to go through  
checking, so that it wont matter whether the NEW date is before or after */

SELECT ABS(DATEDIFF(DATE_FORMAT(`performance_date`, '\'%Y-%m-%d %H:%i:%s\''), 
DATE_FORMAT(NEW.`performance_date`, '\'%Y-%m-%d %H:%i:%s\''))) INTO k;

/* Below code is out of scope for this question */

IF k = 0 THEN
    SIGNAL SQLSTATE '58005'
    SET MESSAGE_TEXT = 'Wrong! Only 1 performance in 1 club is allowed per day! Change your date, or club!';
END IF;

Error Code: 1054. Unknown column 'performance_date' in 'field list'

我尝试过一些简单的方法:

...DATEDIFF(`performance_date`, NEW.`performance_date`)

Answer 1

您可以使用 SELECT ... INTO var_list 查询来COUNT数据库中已有与您的时间匹配的条目数:

我假设您的意思是每天一个条目，并且我假设 performance_date 列是 DATETIME 或 TIMESTAMP类型。

DECLARE k INT DEFAULT 0;

/* Count number of performances occurring on the same date as the 
   performance being inserted */
SELECT COUNT(*)
FROM tbl
WHERE performance_date
BETWEEN DATE(NEW.`performance_date`)
AND DATE(DATE_ADD(NEW.`performance_date`, INTERVAL 1 DAY))
INTO k;

/* If k is not 0, error as there is already a performance */
IF k != 0 THEN
    SIGNAL SQLSTATE '58005'
    SET MESSAGE_TEXT = 'Wrong! Only 1 performance in 1 club is allowed per day! Change your date, or club!';
END IF;

为了清楚起见，如果您有一个 performance_date 为 2014-01-01 19:00:00 的表演，并且您插入了日期为 2014-01-01 08:30:00 的新表演(例如)然后上面的代码将运行此查询，该查询将返回 COUNT 1，这将导致触发器给出该错误:

SELECT COUNT(*)
FROM tbl
WHERE performance_date
BETWEEN DATE("2014-01-01 08:30:00") AND DATE(DATE_ADD("2014-01-01 08:30:00", INTERVAL 1 DAY))
# The line above will become:
# BETWEEN "2014-01-01" AND "2014-01-02"
INTO k