基于MariaDB触发器与存储过程实现机票预订15分钟超时功能咨询

我来帮你实现这个MariaDB下的机票预订超时管理功能，用触发器+存储过程的组合完全能满足需求，下面是一步步的具体实现方案：

首先假设你的预订表基础结构是这样的，我补充了created_at和updated_at字段来跟踪时间，这是实现超时逻辑的关键：

CREATE TABLE reservations (
    reservation_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    flight_id INT NOT NULL,
    reservation_status VARCHAR(2) DEFAULT 'TE', -- 默认为超时状态
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 预订创建时间
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 记录最后修改时间
);

因为默认值是TE（超时），但新创建的预订需要先处于"待确认"状态，这样才能给用户15分钟完成流程。我们用BEFORE INSERT触发器来覆盖默认值：

DELIMITER //
CREATE TRIGGER tr_reservations_before_insert
BEFORE INSERT ON reservations
FOR EACH ROW
BEGIN
    -- 新预订创建时，将状态设为'PE'（待确认），开启15分钟窗口
    SET NEW.reservation_status = 'PE';
END //
DELIMITER ;

写一个存储过程，用来扫描所有超过15分钟仍处于待确认状态的预订，将它们的状态更新为TE（超时）：

DELIMITER //
CREATE PROCEDURE sp_mark_timeout_reservations()
BEGIN
    -- 更新创建时间超过15分钟、状态仍为待确认的预订为超时
    UPDATE reservations
    SET reservation_status = 'TE',
        updated_at = CURRENT_TIMESTAMP
    WHERE reservation_status = 'PE'
      AND created_at < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 15 MINUTE);
END //
DELIMITER ;

为了让存储过程自动定期执行，我们用MariaDB的事件调度器来设置定时任务，比如每分钟执行一次（平衡及时性和性能）：

-- 先确保事件调度器已开启（全局设置，需要权限）
SET GLOBAL event_scheduler = ON;

-- 创建定时事件
DELIMITER //
CREATE EVENT ev_check_reservation_timeouts
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    CALL sp_mark_timeout_reservations();
END //
DELIMITER ;

当用户在15分钟内完成预订流程时，只需要执行以下SQL将状态改为XE（预订成功）即可：

UPDATE reservations
SET reservation_status = 'XE',
    updated_at = CURRENT_TIMESTAMP
WHERE reservation_id = {你的预订ID};

为了避免已经超时的预订被误修改为成功状态，可以加一个AFTER UPDATE触发器做校验：

DELIMITER //
CREATE TRIGGER tr_reservations_after_update
AFTER UPDATE ON reservations
FOR EACH ROW
BEGIN
    -- 禁止将已超时的预订改为成功状态
    IF OLD.reservation_status = 'TE' AND NEW.reservation_status = 'XE' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '无法将已超时的预订标记为成功';
    END IF;
END //
DELIMITER ;

• 事件调度器检查：可以用SHOW VARIABLES LIKE 'event_scheduler';确认调度器是否开启，如果是OFF需要开启才能让定时任务生效。
• 性能优化：如果预订表数据量很大，建议给reservation_status和created_at加联合索引，加快超时查询的速度：

  CREATE INDEX idx_reservations_status_created ON reservations(reservation_status, created_at);
  

• 测试验证：可以手动修改某条预订的created_at为16分钟前的时间，然后调用CALL sp_mark_timeout_reservations();，检查状态是否变为TE。

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