MySQL - 如何为 Payment status 创建 trigger

2026-10-24支付老李

结论

需要创建两个触发器：

AFTER INSERT：新增 Booking 后，通过 NEW.id_booking 创建对应的 Payment，并将初始状态设为 'unpaid'。
AFTER UPDATE：更新 Booking.date_expired 后，根据它与 date_booking 的时间差更新付款状态。

Trigger 只会在执行 INSERT、UPDATE 或 DELETE 时运行。如果实际需求是让预订在创建一小时后自动过期，仅靠 Trigger 无法实现，需要使用 MySQL Event Scheduler 或应用程序定时任务。

创建插入触发器

不要使用 MAX(id_booking)，也不要再次调用 LAST_INSERT_ID() 来查找刚插入的记录。在触发器中，可以直接通过 NEW.id_booking 获取当前新增记录的主键。

DELIMITER //

CREATE TRIGGER booking_after_insert
AFTER INSERT ON Booking
FOR EACH ROW
BEGIN
    INSERT INTO Payment (id_booking, status_payment)
    VALUES (NEW.id_booking, 'unpaid');
END//

DELIMITER ;

Payment.id_payment 应定义为 AUTO_INCREMENT，插入时不需要显式提供。

例如：

CREATE TABLE Payment (
    id_payment BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_booking BIGINT UNSIGNED NOT NULL,
    status_payment VARCHAR(20) NOT NULL,
    PRIMARY KEY (id_payment),
    UNIQUE KEY uk_payment_booking (id_booking),
    CONSTRAINT fk_payment_booking
        FOREIGN KEY (id_booking) REFERENCES Booking (id_booking)
);

触发器并不依赖 UNIQUE KEY 才能运行。不过，如果每个 Booking 只能对应一条 Payment，建议添加这个约束，防止产生重复记录。

创建状态更新触发器

假设业务规则是：

date_expired IS NULL：unpaid
date_expired > date_booking + 1 小时：expired
date_expired 晚于 date_booking，但时间差不超过一小时：paid

可以创建下面的触发器：

DELIMITER //

CREATE TRIGGER booking_after_update
AFTER UPDATE ON Booking
FOR EACH ROW
BEGIN
    UPDATE Payment
    SET status_payment =
        CASE
            WHEN NEW.date_expired IS NULL THEN 'unpaid'
            WHEN NEW.date_expired > DATE_ADD(NEW.date_booking, INTERVAL 1 HOUR)
                THEN 'expired'
            ELSE 'paid'
        END
    WHERE id_booking = NEW.id_booking;
END//

DELIMITER ;

例如：

UPDATE Booking
SET date_expired = '2017-01-06 16:20:00'
WHERE id_booking = 1;

16:20:00 与 15:44:49 的时间差不超过一小时，因此对应 Payment 的状态会更新为：

paid

如果改为：

UPDATE Booking
SET date_expired = '2017-01-06 17:00:00'
WHERE id_booking = 1;

时间差超过一小时，状态会变为：

expired

限制触发器只在相关字段变化时执行

为了避免修改 id_user 等无关字段时也更新 Payment，可以先判断相关字段是否发生变化：

DELIMITER //

CREATE TRIGGER booking_after_update
AFTER UPDATE ON Booking
FOR EACH ROW
BEGIN
    IF NOT (NEW.date_expired <=> OLD.date_expired)
       OR NOT (NEW.date_booking <=> OLD.date_booking) THEN

        UPDATE Payment
        SET status_payment =
            CASE
                WHEN NEW.date_expired IS NULL THEN 'unpaid'
                WHEN NEW.date_expired > DATE_ADD(NEW.date_booking, INTERVAL 1 HOUR)
                    THEN 'expired'
                ELSE 'paid'
            END
        WHERE id_booking = NEW.id_booking;

    END IF;
END//

DELIMITER ;

这里使用了 MySQL 的 NULL-safe equality 运算符 <=>，因为普通的 = 或 <> 无法可靠地比较包含 NULL 的值。

如果要按当前时间自动过期

如果”过期”指的是：

date_expired 仍为空，并且当前时间已经超过 date_booking 一小时。

这种情况不适合使用 Trigger，因为单纯经过一段时间不会触发数据库操作。可以通过 Event Scheduler 定期更新：

SET GLOBAL event_scheduler = ON;

CREATE EVENT expire_unpaid_bookings
ON SCHEDULE EVERY 1 MINUTE
DO
    UPDATE Payment AS p
    INNER JOIN Booking AS b
        ON b.id_booking = p.id_booking
    SET p.status_payment = 'expired'
    WHERE p.status_payment = 'unpaid'
      AND b.date_booking < NOW() - INTERVAL 1 HOUR;

能否执行 SET GLOBAL event_scheduler = ON，取决于数据库账号的权限和服务器配置。托管数据库也可能要求在控制台中启用这一功能。

注意事项

在触发器中，NEW.id_booking 才是当前 Booking 的主键。不要使用 MAX(id_booking)，否则并发插入时可能关联到错误的记录。
date_booking 和 date_expired 应使用 DATETIME 或 TIMESTAMP，不要保存为字符串。
如果 date_expired 可能早于 date_booking，需要单独定义异常数据的处理规则。按照上面的代码，这种情况会被归为 'paid'。
时间差恰好为一小时时会判定为 'paid'，只有严格超过一小时才会判定为 'expired'。
从字段含义看，仅通过 date_expired 判断是否已经付款可能并不合适。更常见的设计是保存实际付款时间，例如 paid_at，同时单独保存预订截止时间，例如 expires_at。付款状态通常由支付成功事件更新，而不是只根据两个时间字段推断。