在MySQL中存储每周活动的最佳方法? [英] Best way to store weekly event in MySQL?

问题描述

我有一个每周事件表，这些事件在一周的某些天(例如MTWTh，MWF等)运行，并在某个时间(例如8 am-5pm)运行.在MySQL中存储星期几信息以使检索和使用数据最容易的最佳方法是什么?我的CakePHP应用程序将需要检索发生在NOW()上的所有事件.

I have a table of weekly events that run on certain days of the week (e.g. MTWTh, MWF, etc.) and run on a certain time (e.g. 8am-5pm). What's the best way to store day of week information in MySQL to make retrieving and working with the data easiest? My CakePHP app is going to need to retrieve all events happening NOW().

对于一天中的时间，我只会使用TIME.在一周的几天中，我考虑了一周中几天的7位位域，即varchar("MTWThFr"类型的交易)，但两者似乎都是笨拙的解决方案(varchar较笨拙).

For time of day, I would just use TIME. For days of the week, I had considered a 7-bit bitfield, a varchar ("MTWThFr" type deal) for the days of the week, but both of those seem like clunky solutions (the varchar being clunkier).

有什么建议吗?

推荐答案

这是一种简单的方法:

EventID Title    Mon Tue Wed Thu Fri Sat Sun BeginningDate EndDate
1       MyEvent  0   0   0   1   0   0   0   14-01-2010    14-01-2033

使用方法:

只需在要运行的日期将其设置为1.由于7天日历不太可能很快改变，因此该结构应该是不变的.您可以选择天数的任意组合.

Simply set a 1 on the days you want to run it. Since the 7-days calendar is not likely to change any time soon, that structure should be immutable. You can choose any combination of days.

回顾:

每个星期四运行一次:

EventID Title    Mon Tue Wed Thu Fri Sat Sun BeginningDate EndDate
1       MyEvent  0   0   0   1   0   0   0   14-01-2010    14-01-2033

每个星期四&星期一:

Run every Thursdays & Mondays:

EventID Title    Mon Tue Wed Thu Fri Sat Sun BeginningDate EndDate
1       MyEvent  1   0   0   1   0   0   0   14-01-2010    14-01-2033

此外，每个事件日程安排只得到一行，这样可以更轻松，更干净地以编程方式进行处理.

Further more, you get only one row per event schedule, which is easier and cleaner to handle programmatically.

例如，要查找周一要执行的所有事件，请执行以下操作:

For example, to find all events to be executed on monday, do:

select * from Events where Mon = 1

这篇关于在MySQL中存储每周活动的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！