您如何精确地使MySQL记录到期(或更新)到到期时间? [英] How would you expire (or update) a MySQL record with precision to the expiry time?
问题描述
去年，我在一个大学的项目上工作，该项目的一项功能要求数据库中的记录有效期几乎达到秒级(即创建后x分钟/小时).我说几乎"是因为几秒钟可能对我来说并不意味着世界的尽头，尽管我可以想象在像拍卖网站之类的网站上，这可能很重要(我确信这些类型的网站会使用不同的措施，但仅作为示例).
Last year I was working on a project for university where one feature necessitated the expiry of records in the database with almost to-the-second precision (i.e. exactly x minutes/hours after creation). I say 'almost' because a few seconds probably wouldn't have meant the end of the world for me, although I can imagine that in something like an auction site, this probably would be important (I'm sure these types of sites use different measures, but just as an example).
我研究了MySQL事件并最终使用了它们，尽管现在我回想起来，我想知道是否有更好的方法来做我所做的事情(不是那么精确或有效) .我可以考虑使用三种方法来使用事件来实现此目的-我想知道这些方法是否有效，或者是否有更好的方法:
I did research on MySQL events and did end up using them, although now that I think back on it I'm wondering if there is a better way to do what I did (which wasn't all that precise or efficient). There's three methods I can think of using events to achieve this - I want to know if these methods would be effective and efficient, or if there is some better way:
-
安排一个事件以每秒运行一次并更新过期的记录.一世 想象这会导致问题，因为记录数 增加并花费超过一秒的时间来执行，甚至可能 干扰正常的数据库操作.如果我错了，请纠正我.
Schedule an event to run every second and update expired records. I imagine that this would cause issues as the number of records increases and takes longer than a second to execute, and might even interfere with normal database operations. Correct me if I'm wrong.
安排每半小时左右运行一次的事件(可以是任何 时间间隔，实际上)，更新过期的记录.同时，强加 查询数据库以仅返回记录时的选择标准 其到期日期尚未过去，因此任何记录 由于未检索到上一个事件执行，此操作已过期.虽然这 在检索时将是准确的，这违背了目的 首先要举办的活动，我会承担额外的费用 选择条件会减慢选择查询的速度.在我的项目中 去年，我使用了这种方法，并且事件更新了记录 确实仅用于后端日志记录.
Schedule an event that runs every half-hour or so (could be any time interval, really), updating expired records. At the same time, impose selection criteria when querying the database to only return records whose expiration date has not yet passed, so that any records that expired since the last event execution are not retrieved. While this would be accurate at the time of retrieval, it defeats the purpose of having the event in the first place, and I'd assume the extra selection criteria would slow down the select query. In my project last year, I used this method, and the event updating the records was really only for backend logging purposes.
在插入时，有一个触发器可以创建一个特定于 即将过期的记录. 到期后，删除事件.我觉得这将是一个 很棒的方法，但是我不太确定是否有那么多 一次运行的事件会影响 数据库(想象一个小时甚至有60次插入的数据库- 这60个事件同时运行仅一个小时.超过 时间，根据有效期限长短而定).
At insert, have a trigger that creates a dynamic event specific to the record that will expire it precisely when it should expire. After the expiry, delete the event. I feel like this would be a great method of doing it, but I'm not too sure if having so many events running at once would impact on the performance of the database (imagine a database that has even 60 inserts an hour - that's 60 events all running simultaneously for just one hour. Over time, depending on how long the expiration is, this would add up).
我敢肯定，您可以通过更多方法来执行此操作-也许可以使用在RDBMS外部运行的单独脚本-但是这些是我在考虑的方法.如果有人对您如何精确地终止记录有任何见解，请告诉我.
I'm sure there's more ways that you could do this - maybe using a separate script that runs externally to the RDBMS is an option - but these are the ones I was thinking about. If anyone has any insight as to how you might expire a record with precision, please let me know.
尽管事实上我过去确实使用过它，但我并不真正喜欢方法2，因为虽然该方法适用于记录到期，但如果不是在以下位置使记录到期，它并没有真正的帮助:在一个精确的时间，我想使其在特定时间处于活动状态(例如，博客站点中的预定帖子).因此，出于这个原因，如果您有一种方法可以在准确的时间更新记录，而不管该更新(过期或发布)是什么，我都会很乐意听到.
Also, despite the fact that I actually did use it in the past, I don't really like method 2 because while this works for the expiration of records, it doesn't really help me if instead of expiring a record at a precise time, I wanted to make it active at a certain time (i.e. a scheduled post in a blog site). So for this reason, if you have a method that would work to update a record at a precise time, regardless of what that that update does (expire or post), I'd be happy to hear it.
推荐答案
选项3:
在插入时，具有一个触发器，该触发器创建一个特定于记录的动态事件，该事件将在记录到期时准确地使其过期.到期后，删除事件.我觉得这是个很好的方法，但是我不太确定一次运行这么多事件是否会影响数据库的性能(假设一个数据库每小时有60次插入-那是60事件全部同时运行仅一小时.随着时间的流逝，这取决于到期的时间长短.
At insert, have a trigger that creates a dynamic event specific to the record that will expire it precisely when it should expire. After the expiry, delete the event. I feel like this would be a great method of doing it, but I'm not too sure if having so many events running at once would impact on the performance of the database (imagine a database that has even 60 inserts an hour - that's 60 events all running simultaneously for just one hour. Over time, depending on how long the expiration is, this would add up).
如果您知道插入的到期时间，只需将其放在表中即可.
If you know the expiry time on insert just put it in the table..
- library_record-id，...，create_at，expire_at
并查询具有以下条件的实时记录:
And query live records with the condition:
expire_at > NOW()
与发布相同:
- library_record-id，...，create_at，publish_at，expire_at
位置:
publish_at <= NOW() AND expire_at > NOW()
您可以将publish_at = create_at设置为立即发布，或者如果不需要，只需将create_at放下即可.
You can set publish_at = create_at for immediate publication or just drop create_at if you don't need it.
使用正确的索引，每个索引的性能都可以与表中的is_live = 1标志媲美，并且可以节省许多与事件相关的麻烦.
Each of these, with the correct indexing, will have performance comparable to an is_live = 1 flag in the table and save you a lot of event related headache.
您还可以确切知道为什么记录不存在以及何时过期/应该轻松发布.您还可以查询即将过期的记录等内容，并轻松发送提醒.
Also you will be able to see exactly why a record isn't live and when it expired/should be published easily. You can also query things such as records that expire soon and send reminders with ease.
这篇关于您如何精确地使MySQL记录到期(或更新)到到期时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
