mysql - 根据更新时的特定条件更新另一个表的最佳方法是什么？

Question

我有两个表subscription 和subscription_event。 subscription_event 可以是以下类型之一:

public enum SubscriptionEventType {

    CREATED,
    CANCELED,
    CHARGED_SUCCESSFULLY,
    CHARGED_UNSUCCESSFULLY,
    EXPIRED,
    TRIAL_STARTED,
    TRIAL_ENDED,
    WENT_ACTIVE, // Subscription went active and can be charged from now on.
    WENT_PAST_DUE;

    public Long getValue() {
        return this.ordinal() + 1L;
    }
}

我想要做的是将订阅的状态保持为最近的事件。问题是:这些事件的顺序不正确。例如。可以在 WENT_ACTIVE 事件之前获取 CHARGED_SUCCESSFULLY 事件。

所以有几种方法可以实现我的需求。首先，我可以检查应用程序层中的条件，并始终根据事件的时间戳设置“最新”状态。

Long subscriptionId = lastRecordedEvent.getSubscriptionId();

if(event.getTimestamp() > lastRecordedEvent.getTimestamp()) {
    // Since the current event is more recent than all other events
    // we also have to update the subscription state
    subscriptionRepository.updateState(subscriptionId, event.getTimestamp());
}

但是，我不想在我的应用程序层中执行此操作。另一种解决方案是在 subscription_event 表上使用 TRIGGER，并让它决定是否更新相关的subscription。我之所以不这么做，是因为我知道触发器很容易被遗忘，而且维护起来也很痛苦。我还知道在使用 TRIGGER 之前应该考虑所有其他选项，但由于我不是 SQL/MySQL 专家，所以我不知道这里的所有选项。

那么在这种情况下，保持订阅最新的最实用方法是什么？

Answer 1

照常将您的事件插入表中，然后执行以下操作

UPDATE subscriptions set state=events.state
FROM subscriptions inner join events on subscriptions.id = events.subscriptionID
Where events.SubscriptionId = ? and events.Timestamp = 
   (select max(timestamp) from events where events.SubscriptionId = ?)

您需要为两个 ? 传递参数，作为您刚刚插入的事件的订阅 ID

编辑

另一种方法是不在数据库中添加状态字段，而是为您的订阅创建一个 View 并始终查询该 View 。

CREATE VIEW vw_subscriptions as
  Select s.id, otherfields from subscription, coalesce(e.Status, 1) as status
  from subscriptions s left outer join events e on s.id=e.subscriptionId
  AND e.timestamp = 
   (select max(timestamp) from events where subscriptionId=s.id)

如果您担心忘记/维护 SQL 或触发器，请将它们记录为存储库函数中的注释，并将对数据库的所有更改维护为与源代码一起存储的更改脚本。这样您的更改就全部在源代码管理中。