创建MySQL事件遇启动事务语法错误，需实现原子性事务操作

你的代码出现语法错误，核心原因是默认语句分隔符;与事件内部语句的分隔符冲突，导致MySQL无法正确解析完整的事件执行体。以下是修正后的代码，同时保留事务原子性：

基础修正版 #

DELIMITER //

CREATE EVENT add_reserve_to_store_products_and_delete_reservations
ON SCHEDULE EVERY 15 MINUTE
DO
BEGIN
    START TRANSACTION;
    
    UPDATE zaloga z 
    JOIN ( 
        SELECT ID_skladisca, ID_izdelek, SUM(kolicina) AS reserve_sum
        FROM reserve 
        WHERE time <= NOW() - INTERVAL 15 MINUTE
        GROUP BY ID_skladisca, ID_izdelek
    ) r ON z.ID_izdelek = r.ID_izdelek AND z.ID_skladisca = r.ID_skladisca
    SET z.kolicina = z.kolicina + r.reserve_sum;
    
    DELETE FROM reserve WHERE time <= NOW() - INTERVAL 15 MINUTE;
    
    COMMIT;
END //

DELIMITER ;

优化严谨版 #

为避免UPDATE和DELETE操作使用不同时间窗口（极端情况下可能导致误删刚插入的记录），可以用变量统一记录截止时间：

DELIMITER //

CREATE EVENT add_reserve_to_store_products_and_delete_reservations
ON SCHEDULE EVERY 15 MINUTE
DO
BEGIN
    DECLARE cutoff_time DATETIME;
    SET cutoff_time = NOW() - INTERVAL 15 MINUTE;
    
    START TRANSACTION;
    
    UPDATE zaloga z 
    JOIN ( 
        SELECT ID_skladisca, ID_izdelek, SUM(kolicina) AS reserve_sum
        FROM reserve 
        WHERE time <= cutoff_time
        GROUP BY ID_skladisca, ID_izdelek
    ) r ON z.ID_izdelek = r.ID_izdelek AND z.ID_skladisca = r.ID_skladisca
    SET z.kolicina = z.kolicina + r.reserve_sum;
    
    DELETE FROM reserve WHERE time <= cutoff_time;
    
    COMMIT;
END //

DELIMITER ;

关键说明 #

1. 分隔符修改：通过DELIMITER //临时替换默认分隔符，确保MySQL将整个BEGIN...END块解析为事件的完整执行体，不会因内部的;提前终止解析。
2. 事务原子性：START TRANSACTION和COMMIT包裹操作，保证更新库存和删除预约记录要么全部成功，要么全部回滚。
3. 额外注意：
   • 需确保MySQL事件调度器已开启，执行SET GLOBAL event_scheduler = ON;（需管理员权限）
   • 当前操作账号需拥有EVENT权限

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