mysql - 如何创建一个包含可以轻松查询的重复事件的 SQL 日历？

Question

我检查了几个关于这个主题的旧问题，例如:Calendar Recurring/Repeating Events - Best Storage Method然而，答案在性能方面非常糟糕并且在实现中很麻烦。从另一个答案中，很容易看出为什么接受的答案是一个坏主意:一个月的事件需要 90 个查询。这是 Not Acceptable 。

无论如何，这是我面临的问题，因此您不必重新阅读这些问题:

1. 以允许它们重复出现的方式存储事件。想想 Google 日历，您可以在其中指定“每月 1 号发生”或“每月第二个星期一发生”之类的模式(后者对我来说不太重要。
2. 查询某个时间段内的事件列表。例如，我想向某人展示接下来 2 个月的事件列表，我不想查询每个月的每一天。这样做只会杀死服务器(在数千行事件中的每个用户)
3. 数据库不可知论者。我使用 PgSQL，并在其他论坛上使用特定于 MS SQL 的东西或 Oracle 看到了这个问题的许多答案。

如果有任何帮助，我将不胜感激!我阅读了几篇关于该主题的论文，但仍然找不到我可以专门在 SQL 中使用的内容。

Answer 1

我想出的解决方案是我有一个事件表，其中有五个定义事件重复发生的字段，定义如下。然后我还有一个时间表，我用事件的实际发生填充了它。我确实需要一个结束日期，即使他们指定了几年后的事件，它也是每月一次的事件，不会在日程表中创建那么多条目。

因此，事件存储在事件表中，如果没有重复发生，则事件表中包含描述事件整个持续时间的 startDateTime 和 endDateTime。这两个日期时间字段还定义事件的整体开始和结束(如果它是重复事件)。在同一个事件表中，我们有五个定义重复的字段，如下所示。

Schedule 表存储每个事件的单独发生。所以它有一个 eventId、startDateTime 和 endDateTime。此开始和结束仅指每次出现，而不是整个跨度。

为了查询一段时间内发生的所有预定事件，我只查询调度表以检查是否有任何匹配此条件的事件:

select * from schedule where schedule.startDateTime < @queryPeriodEnd and schedule.endDateTime > @queryPeriodStart

此查询仅向我提供部分或全部发生在我的查询期间内的计划条目。要获取事件数据，只需加入事件表即可。

有趣的部分是计算每月的第二个星期四。这发生在用于确定给定事件的所有计划发生的实际代码中。我还在下面附上了我的代码。

事件重复字段

反复出现0=没有复发1=每天2=每周3=每月

递归间隔这是重复之间的周期数。如果事件每 5 天重复一次，recurs_interval 的值为 5，recurs 的值为 1。如果事件每 3 周重复一次，recurs_interval 的值为 3，recurs 的值为 2。

重复日如果用户选择每月类型重复，则在一个月的给定日期(例如:10 号或 14 号)。这有那个日期。如果用户没有选择每月或每月的特定日期重复，则该值为 0。否则该值为 1 到 31。

递归序数如果用户选择了每月类型的重复，但是一天的顺序类型(例如:第一个星期一、第二个星期四、最后一个星期五)。这将具有该序号。如果用户没有选择这种类型的重复，则该值为 0。1=第一2=秒3=第三4=第四5=最后

recurs_weekdays对于每周和每月的顺序重复，这将存储重复发生的工作日。 1=星期日2=星期一4=星期二8=星期三16=星期四32=星期五64=星期六

所以，每 4 周的周六和周日就是recurs=2, recurs_interval=4, recurs_weekdays=65 (64 + 1)同样，每三个月的第一个星期五是recurs=3, recurs_interval=3, recurs_ordinal=1, recurs_weekdays=32

代码

        thisEvent.occurrences = new List<ScheduleInstance>();
        DateTime currentDateTime = (DateTime) thisEvent.start;
        DateTime currentEndTime;
        BitArray WeekDayRecurrenceBits = new BitArray(new Byte[] {(Byte) thisEvent.recursWeekdays});

        while (currentDateTime < thisEvent.end)
        {
            currentEndTime = new DateTime(currentDateTime.Year, currentDateTime.Month, currentDateTime.Day,
                                          thisEvent.end.Value.Hour, thisEvent.end.Value.Minute, thisEvent.end.Value.Second);
            switch (thisEvent.recurs)
            {
                case (RecurrenceTypeEnum.None):
                    AddOccurrenceToRooms(thisEvent, currentDateTime, currentEndTime);
                    currentDateTime = (DateTime)thisEvent.end;
                    break;
                case (RecurrenceTypeEnum.Daily):
                    AddOccurrenceToRooms(thisEvent, currentDateTime, currentEndTime);
                    currentDateTime = currentDateTime.AddDays(thisEvent.recursInterval);
                    break;
                case (RecurrenceTypeEnum.Weekly):
                    int indexIntoCurrentWeek = (int) currentDateTime.DayOfWeek;
                    while ((indexIntoCurrentWeek < 7) && (currentDateTime < thisEvent.end))
                    {
                        if (WeekDayRecurrenceBits[(int) currentDateTime.DayOfWeek])
                        {
                            AddOccurrenceToRooms(thisEvent, currentDateTime, currentEndTime);
                        }
                        currentDateTime = currentDateTime.AddDays(1);
                        currentEndTime = currentEndTime.AddDays(1);
                        indexIntoCurrentWeek++;
                    }
                    currentDateTime = currentDateTime.AddDays(7 * (thisEvent.recursInterval - 1));
                    break;
                case (RecurrenceTypeEnum.Monthly):
                    if (thisEvent.recursDay == 0)
                    {
                        DateTime FirstOfTheMonth = new DateTime(currentDateTime.Year, currentDateTime.Month, 1);
                        int daysToScheduleOccurrence = ((thisEvent.recursWeekdays - (int)FirstOfTheMonth.DayOfWeek + 7) % 7)
                                                       + ((thisEvent.recursOrdinal - 1) * 7)
                                                       - currentDateTime.Day + 1;
                        if (daysToScheduleOccurrence >= 0)
                        {
                            currentDateTime = currentDateTime.AddDays(daysToScheduleOccurrence);
                            currentEndTime = currentEndTime.AddDays(daysToScheduleOccurrence);
                            if (currentDateTime < thisEvent.end)
                            {
                                AddOccurrenceToRooms(thisEvent, currentDateTime, currentEndTime);
                            }
                        }
                    }
                    else
                    {
                        if (currentDateTime.Day <= thisEvent.recursDay && thisEvent.recursDay <= DateTime.DaysInMonth(currentDateTime.Year, currentDateTime.Month) )
                        {
                            currentDateTime = currentDateTime.AddDays(thisEvent.recursDay - currentDateTime.Day);
                            currentEndTime = currentEndTime.AddDays(thisEvent.recursDay - currentEndTime.Day);
                            AddOccurrenceToRooms(thisEvent, currentDateTime, currentEndTime);
                        }
                    }
                    currentDateTime = currentDateTime.AddDays((currentDateTime.Day - 1) * -1).AddMonths(thisEvent.recursInterval);
                    break;
                default:
                    break;
            }
        }