在PHP MyAdmin中创建复杂SQL触发器求助

功能需求 #

• 涉及表：events，核心字段：clock_on(datetime)、clock_off(datetime)、time_clocked(int(11))
• 当clock_on和clock_off均不为NULL时：
  • 计算两者的时间差（秒数），累加到time_clocked（若time_clocked为NULL则直接赋值）
  • 将clock_on和clock_off置为NULL
• 定时逻辑：
  • 周一至周四23点：自动将所有clock_off为NULL的记录的clock_off设为当前日期的16:15，然后执行上述时间计算逻辑
  • 周五23点：自动将所有clock_off为NULL的记录的clock_off设为当前日期的12:15，然后执行上述时间计算逻辑

用户原始触发器代码 #

DELIMITER ;
DROP TRIGGER IF EXISTS `calculate_duration`;
DELIMITER $$
CREATE TRIGGER `calculate_duration`
AFTER INSERT
ON `events`
FOR EACH ROW
BEGIN
IF NEW.clock_on IS NOT NULL AND NEW.clock_off IS NOT NULL THEN
  UPDATE events
    SET time_clocked = UNIX_TIMESTAMP(NEW.clock_off) - UNIX_TIMESTAMP(NEW.clock_on)
    WHERE id = NEW.id;
END IF;
IF (
  DAYOFWEEK(CURRENT_DATE()) IN (1, 2, 3, 4)
  AND HOUR(NOW()) = 23
  AND NEW.clock_off IS NULL
) THEN
  INSERT INTO events (clock_on, clock_off)
    VALUES (CURRENT_DATE(), '16:15');
END IF;
IF (
  DAYOFWEEK(CURRENT_DATE()) = 5
  AND HOUR(NOW()) = 23
  AND NEW.clock_off IS NULL
) THEN
  INSERT INTO events (clock_on, clock_off)
    VALUES (CURRENT_DATE(), '12:15');
END IF;
END$$
DELIMITER;

events表结构 #

CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `name` text DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `resource_id` int(11) DEFAULT NULL,
  `color` varchar(200) DEFAULT NULL,
  `join_id` int(11) DEFAULT NULL,
  `has_next` tinyint(1) NOT NULL DEFAULT 0,
  `TIMESTAMP` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `contract` text DEFAULT NULL,
  `part` text NOT NULL,
  `operation` text NOT NULL,
  `jobcard` text NOT NULL,
  `drawing` text DEFAULT NULL,
  `clock_on` datetime DEFAULT NULL,
  `clock_off` datetime DEFAULT NULL,
  `time_clocked` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci  

修正后的实现方案 #

1. 处理时间计算的触发器（INSERT/UPDATE时触发） #

使用BEFORE INSERT和BEFORE UPDATE触发器，在插入或更新记录时直接处理计算逻辑，避免额外的UPDATE操作：

DELIMITER $$

-- 处理INSERT时的逻辑
DROP TRIGGER IF EXISTS events_before_insert;
CREATE TRIGGER events_before_insert
BEFORE INSERT ON events
FOR EACH ROW
BEGIN
    -- 当clock_on和clock_off都不为空时计算时间差并置空
    IF NEW.clock_on IS NOT NULL AND NEW.clock_off IS NOT NULL THEN
        SET NEW.time_clocked = COALESCE(NEW.time_clocked, 0) + TIMESTAMPDIFF(SECOND, NEW.clock_on, NEW.clock_off);
        SET NEW.clock_on = NULL;
        SET NEW.clock_off = NULL;
    END IF;
END$$

-- 处理UPDATE时的逻辑
DROP TRIGGER IF EXISTS events_before_update;
CREATE TRIGGER events_before_update
BEFORE UPDATE ON events
FOR EACH ROW
BEGIN
    -- 当clock_on和clock_off都不为空时计算时间差并置空
    IF NEW.clock_on IS NOT NULL AND NEW.clock_off IS NOT NULL THEN
        SET NEW.time_clocked = COALESCE(NEW.time_clocked, 0) + TIMESTAMPDIFF(SECOND, NEW.clock_on, NEW.clock_off);
        SET NEW.clock_on = NULL;
        SET NEW.clock_off = NULL;
    END IF;
END$$

DELIMITER ;

2. 定时自动填充clock_off的事件调度器 #

触发器无法实现定时触发，需使用MySQL事件调度器来完成每天23点的自动处理：

-- 开启事件调度器（若未开启）
SET GLOBAL event_scheduler = ON;

DELIMITER $$

DROP EVENT IF EXISTS auto_fill_clock_off_weekdays;
CREATE EVENT auto_fill_clock_off_weekdays
ON SCHEDULE EVERY 1 DAY
STARTS DATE_ADD(CURDATE(), INTERVAL 23 HOUR)
DO
BEGIN
    DECLARE current_day INT;
    SET current_day = DAYOFWEEK(CURDATE());
    
    -- 周一至周四（DAYOFWEEK返回2-5）
    IF current_day BETWEEN 2 AND 5 THEN
        UPDATE events
        SET clock_off = CONCAT(CURDATE(), ' 16:15:00')
        WHERE clock_off IS NULL;
    END IF;
    
    -- 周五（DAYOFWEEK返回6）
    IF current_day = 6 THEN
        UPDATE events
        SET clock_off = CONCAT(CURDATE(), ' 12:15:00')
        WHERE clock_off IS NULL;
    END IF;
END$$

DELIMITER ;

关键说明 #

• 时间计算：使用TIMESTAMPDIFF(SECOND, clock_on, clock_off)直接获取秒数差，比UNIX_TIMESTAMP更安全，避免2038年溢出问题
• 累加逻辑：COALESCE(NEW.time_clocked, 0)确保当time_clocked为NULL时从0开始累加
• DAYOFWEEK取值：MySQL中DAYOFWEEK()返回值为1（周日）到7（周六），所以周一至周四对应2-5，周五对应6
• 事件调度器：需要确保MySQL的事件调度器已开启，事件会每天23点自动执行，更新符合条件的记录，触发之前创建的BEFORE UPDATE触发器完成时间计算和置空操作

内容的提问来源于stack exchange，提问作者Steve Lee