mysql - 生成具有重复间隔的日期列表

Question

我正在创建一个程序，其主要功能是事件日历。数据库中有人物和事件，它会跟踪各个事件的出席情况。

在事件方面，我希望能够提供一个用于生成重复事件的选项。如果事件重复，用户提供以下信息:

1) 事件开始日期2) 事件结束日期3)重复的日子(例如，星期四)4)重复间隔(每周、每两周、每月)

所有这些信息都被插入到“事件”表中，然后导致触发器执行。触发器使用此信息在另一个表“event_instances”中生成记录，该表包含给定事件的每个实例(事件发生的每一天)的记录。然后根据每个实例跟踪事件出席情况。

为了最大限度地提高性能，我一直在使用一种创建临时表的方法，将其过滤为仅包含我需要的日期，然后将其插入到 event_instance 表中。这是一个很好的方法，因为它能够在 1-2 秒内执行整个操作。

我的问题现在归结为重复间隔。在开始之前，先介绍一下我用来生成临时表的 SQL 代码。此公式创建一个包含 @start_date 和 @end_date 之间 100,000 个日期的表。 @start_date 和 @end_date 是临时值，在最终实现中，这将位于一个带有两个日期参数的函数中。

set @start_date = '2015-9-20';
set @end_date = '2016-1-17';

select * from
(select  @start_date + interval ((a.a) + (10 * b.a) + (100 * c.a) + (1000 * d.a) + (10000 * e.a)) day this_date
from 
(select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) as a 
join
(select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) as b 
join
(select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) as c
join
(select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) as d
join
(select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) as e) v
where this_date <= date(@end_date);

这会生成一个包含 @start_date 和 @end_date 之间每个日期的表。要缩小到每周重复很容易，我需要做的就是添加(示例中每周星期四):

and dayofweek(this_date) = 5

问题是，我不知道如何从这里进行每两周或每月的过滤。这是完成所有这些工作的最后一部分。

提前非常感谢。

Answer 1

对于每月，您可以使用 day() 函数:

where day(this_date) = 5

本月 5 日。

对于每两周一次(每两周)，您可能应该减去一些规范日期并使用 mod:

where mod(datediff(this_date, '2001-01-01'), 14) = 5

一周中的某一天，每隔一周。