MySQL:表插入后 15 分钟安排事件 [英] MySQL: Schedule event 15 minutes after table insert

问题描述

在我的预订数据库中，有一个带有 orderStatus 列的 RentOrder 表.用户可以通过预订 API 访问和下订单.

In my booking database, there is a RentOrder table with an orderStatus column. Users can access and make orders via a booking api.

我希望 MySQL 在初始 RentOrder 插入后等待 15 分钟，并检查 orderStatus 是否仍设置为 RESERVED(即检查订单尚未确认).如果是这种情况，那么我想自动将 orderStatus 设置为 TIMEOUT.

I want MySQL to wait 15 minutes after an initial RentOrder insert and check if orderStatus is still set to RESERVED (i.e. check if the order hasn't been confirmed). If this is the case, then I want to automatically set the orderStatus to TIMEOUT.

到目前为止，我的想法是使用 AFTER INSERT 和 SLEEP 但我不确定这是最佳选择吗?我主要担心的是 SLEEP 可能会对性能或其他任何方面产生不利影响.

My idea so far is to user AFTER INSERT and SLEEP but I'm not sure this is optimal? My main concern is that SLEEP might have some adverse effects on performance or anything else.

欢迎提出任何建议并提前致谢.

Any suggestions welcome and thanks in advance.

推荐答案

您可以设置一个事件.就我个人而言，我推荐用于此目的的视图:

You could set an event. Personally, I recommend a view for this purpose:

create view v_RentOrder as
    select . . .,
           (case when createdAt < now() - interval 15 minute and orderStatus = 'RESERVED'
                 then 'TIMEOUT'
                 else orderStatus
            end) as orderStatus
    from RentOrder;

createdAt 列是记录的创建时间.

The column createdAt is the time the record was created.

瞧！对于使用该视图的任何人来说，数据始终是最新的 - 秒.

Voila! The data is always up-to-date -- to the second -- for anyone using the view.

您可以将此与定期(例如每天一次或每小时一次)用您想要的状态更新表格的作业配对.

You can pair this with a job that periodically (say once per day or once per hour) updates the table with the status that you want.

为每次更新创建事件或作业的选项存在挑战:

The option of creating an event or job for every update has challenges:

• 如果数据库出现故障，更新可能不会生效.
• 如果服务器负载很重，更新可能会延迟.
• 如果表或行被锁定，更新可能会延迟.
• 更新会增加系统负载并可能干扰其他活动.

• If the database goes down, updates may not take effect.
• If there is a heavy server load, updates may be delayed.
• If the table or rows are locked, the updates may be delayed.
• The updates increase the load on the system and might interfere with other activities.

可以通过使用队列/消息系统进行更新来缓解这些问题.然而，对于一个简单的问题，这开始变得复杂.

These problems can be mitigated by using a queuing/messaging system for the updates. However, that starts to get complicated for a simple problem.

这篇关于MySQL:表插入后 15 分钟安排事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！