我正在构建一个自定义事件系统，如果你有一个如下所示的重复事件：
事件A每4天重复一次，开始于2011年3月3日
或者
事件B每2周星期二重复一次，开始于2011年3月1日
我应该如何在数据库中存储这些信息以便于查找？我不想因为有大量事件而出现性能问题，在渲染日历时必须逐个遍历每个事件。
11个回答
243
存储“简单”重复模式
对于我的基于PHP/MySQL的日历，我希望尽可能高效地存储重复事件信息。我不想有大量行，并且想要轻松查找特定日期上发生的所有事件。
以下方法非常适合存储以规律间隔出现的重复信息，例如每天、每 n 天、每周、每月、每年等等。这也包括像每个星期二和星期四这样的模式，因为它们分别作为从星期二开始的每周和从星期四开始的每周单独存储。
假设我有两个表，一个叫做 events，如下所示：
ID NAME
1 Sample Event
2 Another Event
还有一个名为events_meta的表格，如下所示：
ID event_id meta_key meta_value
1 1 repeat_start 1299132000
2 1 repeat_interval_1 432000
repeat_start是一个没有时间的日期，以Unix时间戳表示，而repeat_interval是间隔时间量（例如432000代表5天）。
repeat_interval_1对应ID为1的repeat_start。因此，如果我有一个事件每周二和周四重复一次，那么repeat_interval将是604800（7天），并且会有2个repeat_starts和2个repeat_intervals。表格将如下所示：
ID event_id meta_key meta_value
1 1 repeat_start 1298959200 -- This is for the Tuesday repeat
2 1 repeat_interval_1 604800
3 1 repeat_start 1299132000 -- This is for the Thursday repeat
4 1 repeat_interval_3 604800
5 2 repeat_start 1299132000
6 2 repeat_interval_5 1 -- Using 1 as a value gives us an event that only happens once
那么，如果你有一个遍历每一天并获取该天事件的日历，查询将如下所示：
SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
AND (
( CASE ( 1299132000 - EM1.`meta_value` )
WHEN 0
THEN 1
ELSE ( 1299132000 - EM1.`meta_value` )
END
) / EM2.`meta_value`
) = 1
LIMIT 0 , 30
用当前日期的Unix时间戳替换{current_timestamp}（去掉时间，因此小时、分钟和秒值将被设置为0）。
希望这也能帮助其他人！
存储“复杂”的重复模式
对于存储像
事件A每个月在3日重复一次，从2011年3月3日开始
或者
事件A每个月第2周的星期五重复一次，从2011年3月11日开始
这样的复杂模式，建议采用此方法。我建议与上述系统结合使用以获得最大的灵活性。用于此的表应该是这样的：
ID NAME
1 Sample Event
2 Another Event
还有一个名为events_meta的表，格式如下：
ID event_id meta_key meta_value
1 1 repeat_start 1299132000 -- March 3rd, 2011
2 1 repeat_year_1 *
3 1 repeat_month_1 *
4 1 repeat_week_im_1 2
5 1 repeat_weekday_1 6
repeat_week_im 表示当前月份的第几周，可能是1到5。 repeat_weekday 表示一周中的第几天，即1-7。
假设现在你正在循环处理日期/周以创建日历中的月视图，则可以组合如下查询：
SELECT EV . *
FROM `events` AS EV
JOIN `events_meta` EM1 ON EM1.event_id = EV.id
AND EM1.meta_key = 'repeat_start'
LEFT JOIN `events_meta` EM2 ON EM2.meta_key = CONCAT( 'repeat_year_', EM1.id )
LEFT JOIN `events_meta` EM3 ON EM3.meta_key = CONCAT( 'repeat_month_', EM1.id )
LEFT JOIN `events_meta` EM4 ON EM4.meta_key = CONCAT( 'repeat_week_im_', EM1.id )
LEFT JOIN `events_meta` EM5 ON EM5.meta_key = CONCAT( 'repeat_weekday_', EM1.id )
WHERE (
EM2.meta_value =2011
OR EM2.meta_value = '*'
)
AND (
EM3.meta_value =4
OR EM3.meta_value = '*'
)
AND (
EM4.meta_value =2
OR EM4.meta_value = '*'
)
AND (
EM5.meta_value =6
OR EM5.meta_value = '*'
)
AND EM1.meta_value >= {current_timestamp}
LIMIT 0 , 30
结合上述方法，这种方法可以覆盖大多数重复/循环事件模式。如果我遗漏了任何内容，请留下评论。
- Brandon
7
1在
AND ( ( CASE ( 1299132000 - EM1.meta_value ) WHEN 0 THEN 1 ELSE ( 1299132000 - EM1.meta_value) END ) / EM2.meta_value ) = 1中，这个/ EM2.meta_value放错了吗？ - Murali Murugesan36需要注意的是，不应该在重复间隔中使用硬编码值，例如一天中的86400秒，因为它没有考虑到夏令时。更合适的做法是动态地实时计算这些值，并存储 "interval = daily" 和 "interval_count = 1" 或 "interval = monthly" 和 "interval_count = 1"。 - Corey Ballou
1@Brandon Wamboldt 我该如何从重复事件中删除一个实例出现？ - Pugazhenthi
很酷的模式，但为什么不为每个重复事件创建一个事件行呢？这是一个事件。您可能会对该事件进行一些特定的修改...或者想要有关先前事件的历史报告？ - Mittchel
@MuraliMurugesan 你是对的，它放错了位置。 - julio
显示剩余2条评论
207
尽管当前被接受的答案对我很有帮助，但我想分享一些有用的修改，可以简化查询并提高性能。
"简单"重复事件
处理定期重复的事件，例如：
Repeat every other day
Repeat every week on Tuesday
你应该创建两个表，一个叫做events，如下所示:
ID NAME
1 Sample Event
2 Another Event
还有一个名为events_meta的表，就像这样：
ID event_id repeat_start repeat_interval
1 1 1369008000 604800 -- Repeats every Monday after May 20th 2013
1 1 1369008000 604800 -- Also repeats every Friday after May 20th 2013
假设repeat_start是一个不带时间的Unix时间戳日期(1369008000对应2013年5月20日)，而repeat_interval是两个事件之间以秒为单位的时间间隔(604800代表7天)。
通过在日历中循环每一天，您可以使用以下简单查询获取重复事件：
SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE (( 1299736800 - repeat_start) % repeat_interval = 0 )
只需将Unix时间戳（1299736800）替换日历中的每个日期。
请注意使用取模（% 符号）。此符号类似于常规除法，但返回“余数”而非商，因此在当前日期是重复间隔从重复开始的精确倍数时，余数为 0。
性能比较
这比先前建议的基于“meta_keys”的答案要快得多，如下所示：
SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
AND (
( CASE ( 1299132000 - EM1.`meta_value` )
WHEN 0
THEN 1
ELSE ( 1299132000 - EM1.`meta_value` )
END
) / EM2.`meta_value`
) = 1
如果你运行 EXPLAIN 这个查询，你会注意到它需要使用一个连接缓冲区：
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
| 1 | SIMPLE | EM1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 1 | SIMPLE | EV | eq_ref | PRIMARY | PRIMARY | 4 | bcs.EM1.event_id | 1 | |
| 1 | SIMPLE | EM2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+
在上述解决方案中，需要使用缓冲区。
"复杂"模式
您可以添加对更复杂类型的支持，以支持这些类型的重复规则：
Event A repeats every month on the 3rd of the month starting on March 3, 2011
Event A repeats second Friday of the month starting on March 11, 2011
您的事件表可以完全相同：
ID NAME
1 Sample Event
2 Another Event
然后，要为这些复杂规则添加支持，请像这样向events_meta添加列：
ID event_id repeat_start repeat_interval repeat_year repeat_month repeat_day repeat_week repeat_weekday
1 1 1369008000 604800 NULL NULL NULL NULL NULL -- Repeats every Monday after May 20, 2013
1 1 1368144000 604800 NULL NULL NULL NULL NULL -- Repeats every Friday after May 10, 2013
2 2 1369008000 NULL 2013 * * 2 5 -- Repeats on Friday of the 2nd week in every month
请注意，您只需指定一个repeat_interval或一组repeat_year，repeat_month，repeat_day，repeat_week和repeat_weekday数据中的任何一个。
这使得同时选择两种类型非常简单。只需循环遍历每一天并填入正确的值（例如2013年6月7日的1370563200，然后是年份、月份、日期、星期数和工作日）：
SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE (( 1370563200 - repeat_start) % repeat_interval = 0 )
OR (
(repeat_year = 2013 OR repeat_year = '*' )
AND
(repeat_month = 6 OR repeat_month = '*' )
AND
(repeat_day = 7 OR repeat_day = '*' )
AND
(repeat_week = 2 OR repeat_week = '*' )
AND
(repeat_weekday = 5 OR repeat_weekday = '*' )
AND repeat_start <= 1370563200
)
这将返回所有重复在第二周星期五的事件以及每个星期五都会重复的任何事件，因此它将同时返回事件ID 1和2：
ID NAME
1 Sample Event
2 Another Event
*在上述 SQL 中，我使用了 PHP Date 的默认工作日索引，因此星期五是“5”。
- Andy Hoffner
17
7太棒了，谢谢！你有任何想法如何编码“每两个月的第一个星期一”或“每三个月的第一个星期一”等吗？ - Jordan Lev
7我同意这很棒。然而，我遇到了Jordan Lev也遇到的困境。repeat_interval字段对于重复月份并不适用，因为有些月份比其他月份更长。此外，你如何限制重复事件的持续时间？例如，每2个月的第一个星期一进行8个月。该表应该有某种结束日期。 - Abinadi
3提示：对于复杂的模式，可以消除“repeat_interval”列，并在随后的列中表示它（即“repeat_year”等）。 对于第一行，在2013年5月20日之后每个星期一重复的情况可以通过在“repeat_weekday”中放置1并在其他列中放置“*”来表示。 - musubi
2@Abinadi 关于“每两个月的第一个星期一，持续8个月”的问题，我认为我们可以使用repeat_month来实现，例如填写“1,3,5,7,9,11”；在这种情况下，我们还需要添加结束日期。 - Stony
3@OlivierMATROT @milos 的想法是明确设置您想要固定的字段，其余字段使用通配符
*。因此，对于“每月3日”，只需将repeat_day设置为3，其他repeat字段设置为*（将repeat_interval保留为空），并将repeat_start设置为2011年3月3日的Unix时间戳作为锚定日期。 - Andy Hoffner显示剩余12条评论
34
升级：用日期替代时间戳
作为对被ahoffner优化后接受的答案的小升级 - 可以使用日期格式而不是时间戳。 优点如下：
- 在数据库中使用可读日期
- 无需考虑年份大于2038和时间戳的问题
- 消除了需要小心处理基于季节性调整日期的时间戳的需求，例如在英国，6月28日比12月28日早一个小时，因此从日期中推导出时间戳可能会破坏递归算法。
要实现此操作，请将 DB repeat_start 更改为存储类型为“date”，并且 repeat_interval 现在保存天数而不是秒数。 例如，7表示每7天重复一次。
更改 SQL 行：
WHERE (( 1370563200 - repeat_start) % repeat_interval = 0 )
致：
WHERE ( DATEDIFF( '2013-6-7', repeat_start ) % repeat_interval = 0)
其它所有的都保持不变。简单易懂！
- user3781087
2
那么，如果我想让我的事件每年重复一次呢？repeat_interval 应该存储 365 天吗？如果这一年有 366 天怎么办？ - Tarida George
3如果事件是每年一次，则将repeat_interval设置为NULL，将repeat_year设置为 *。根据重复方式不同，您可以设置repeat_month和repeat_day（例如3月11日）或者设置repeat_month、repeat_week和repeat_weekday来选择4月的第二个星期二。请注意，翻译时保证内容准确无误，通俗易懂，且无更改原意。 - jerrygarciuh
27
对于所有对此感兴趣的人，现在你只需复制并粘贴即可在几分钟内开始。我尽可能地采纳了评论中的建议。如果我漏掉了什么，请告诉我。
“复杂版本”：
事件
+----------+----------------+ | ID | NAME | +----------+----------------+ | 1 | 样本事件1 | | 2 | 第二个事件 | | 3 | 第三个事件 | +----------+----------------+
事件元数据
+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+ | ID | event_id | repeat_start | repeat_interval | repeat_year | repeat_month | repeat_day | repeat_week | repeat_weekday | +----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+ | 1 | 1 | 2014-07-04 | 7 | NULL | NULL | NULL | NULL | NULL | | 2 | 2 | 2014-06-26 | NULL | 2014 | * | * | 2 | 5 | | 3 | 3 | 2014-07-04 | NULL | * | * | * | * | 5 | +----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+
SQL代码：
CREATE TABLE IF NOT EXISTS `events` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `events`
--
INSERT INTO `events` (`ID`, `NAME`) VALUES
(1, 'Sample event'),
(2, 'Another event'),
(3, 'Third event...');
CREATE TABLE IF NOT EXISTS `events_meta` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`event_id` int(11) NOT NULL,
`repeat_start` date NOT NULL,
`repeat_interval` varchar(255) NOT NULL,
`repeat_year` varchar(255) NOT NULL,
`repeat_month` varchar(255) NOT NULL,
`repeat_day` varchar(255) NOT NULL,
`repeat_week` varchar(255) NOT NULL,
`repeat_weekday` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `events_meta`
--
INSERT INTO `events_meta` (`ID`, `event_id`, `repeat_start`, `repeat_interval`, `repeat_year`, `repeat_month`, `repeat_day`, `repeat_week`, `repeat_weekday`) VALUES
(1, 1, '2014-07-04', '7', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL'),
(2, 2, '2014-06-26', 'NULL', '2014', '*', '*', '2', '5'),
(3, 3, '2014-07-04', 'NULL', '*', '*', '*', '*', '1');
此内容也可作为MySQL导出文件（便于访问）。
PHP示例代码index.php：
<?php
require 'connect.php';
$now = strtotime("yesterday");
$pushToFirst = -11;
for($i = $pushToFirst; $i < $pushToFirst+30; $i++)
{
$now = strtotime("+".$i." day");
$year = date("Y", $now);
$month = date("m", $now);
$day = date("d", $now);
$nowString = $year . "-" . $month . "-" . $day;
$week = (int) ((date('d', $now) - 1) / 7) + 1;
$weekday = date("N", $now);
echo $nowString . "<br />";
echo $week . " " . $weekday . "<br />";
$sql = "SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE ( DATEDIFF( '$nowString', repeat_start ) % repeat_interval = 0 )
OR (
(repeat_year = $year OR repeat_year = '*' )
AND
(repeat_month = $month OR repeat_month = '*' )
AND
(repeat_day = $day OR repeat_day = '*' )
AND
(repeat_week = $week OR repeat_week = '*' )
AND
(repeat_weekday = $weekday OR repeat_weekday = '*' )
AND repeat_start <= DATE('$nowString')
)";
foreach ($dbConnect->query($sql) as $row) {
print $row['ID'] . "\t";
print $row['NAME'] . "<br />";
}
echo "<br /><br /><br />";
}
?>
PHP示例代码connect.php：
<?
// ----------------------------------------------------------------------------------------------------
// Connecting to database
// ----------------------------------------------------------------------------------------------------
// Database variables
$username = "";
$password = "";
$hostname = "";
$database = "";
// Try to connect to database and set charset to UTF8
try {
$dbConnect = new PDO("mysql:host=$hostname;dbname=$database;charset=utf8", $username, $password);
$dbConnect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}
// ----------------------------------------------------------------------------------------------------
// / Connecting to database
// ----------------------------------------------------------------------------------------------------
?>
这里提供了 PHP 代码（为了更好的可读性）：
index.php
以及
connect.php
现在，设置这个应该只需要几分钟。不是几小时。:)
- Alex
3
2如何查询以获取日期范围内的所有重复事件？也就是说，获取2014年10月1日至2014年12月30日之间的所有重复事件。感谢您的帖子。 - Well Wisher
@Wellwisher - 重复执行...直到和临时表。http://stackoverflow.com/questions/34407833/calendar-recurring-repeating-events-find-events-in-date-range - Brad Kent
2感谢您提供的代码。但是我必须指出，您的数据库/查询实现有些令人不安，而且非常低效。例如，为什么要对这样简单的列使用varchar（255）（正如@OlivierMATROT所提到的，您可以使用整数，即使不是，为什么是255？）。如果您要重复查询30次，为什么不使用语句或存储过程呢？只是为了提醒那些即将实施此代码的人。 - Rony
23
尽管推荐的解决方案可行，但我试图使用Full Calendar实现它需要为每个视图进行90次以上的数据库调用(因为它加载当前、上一个和下一个月)，这让我感到不太满意。我发现了一个递归库 https://github.com/tplaner/When ，你只需将规则存储在数据库中，然后查询一次即可检索所有相关规则。希望这能帮助其他人，因为我花了很多时间寻找一个好的解决方案。编辑：此库适用于PHP。
- Tim Ramsey
1
PHP开发人员的好提示 - 一个简单的库，只做它需要做的事情！ - Rico Leuthold
17
为什么不使用类似于Apache定时任务的机制呢？http://en.wikipedia.org/wiki/Cron
对于日历调度，我会使用略微不同的“位”值来适应标准日历重复事件 - 而不是 [星期（0-7），月份（1-12），月中的日期（1-31），小时（0-23），分钟（0-59）]
-- 我会使用类似如下的内容 [年（每N年重复一次），月份（1-12），月中的日期（1-31），月中的第N周（1-5），星期（0-7）]
希望这能有所帮助。
- Vladimir
3
7我认为星期选择太多了。选1-7或0-6似乎更准确。 - Abinadi
2使用cron来存储重复任务是不错的选择，但问题在于查找非常困难。 - Stony
1cron的表达能力有限，因为它是无状态的（仅将当前/假设日期/时间与模式进行比较），因此它无法表示某些常见的业务/人类模式，例如“每三天”或“每7小时”，这需要记住上次发生的情况。这并不明显；您可能会认为在crontab中只需说day/3或hour/7，但是在月/日的末尾，您会有少于3或7的“剩余”天/小时；可能会导致灾难性的结果。 - Jaime Guerrero
5
我为此开发了一种玄学编程语言。它的最大优点是无模式且独立于平台。您只需编写一个选择器程序来安排日程，其语法受此处描述的规则集限制 -
这是一种完全不同的方法，可能具有其自身的一些缺点。
https://github.com/tusharmath/sheql/wiki/Rules
规则是可扩展的，您可以根据所需执行的重复逻辑添加任何类型的自定义，而无需担心模式迁移等问题。这是一种完全不同的方法，可能具有其自身的一些缺点。
- tusharmath
4
这很像MySQL事件，它们存储在系统表中。您可以查看结构并确定哪些列是不需要的：
EVENT_CATALOG: NULL
EVENT_SCHEMA: myschema
EVENT_NAME: e_store_ts
DEFINER: jon@ghidora
EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 5
INTERVAL_FIELD: SECOND
SQL_MODE: NULL
STARTS: 0000-00-00 00:00:00
ENDS: 0000-00-00 00:00:00
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2006-02-09 22:36:06
LAST_ALTERED: 2006-02-09 22:36:06
LAST_EXECUTED: NULL
EVENT_COMMENT:
- Valentin Kuzub
3
@Rogue Coder
太棒了！
你可以简单地使用取模运算符（mysql中的MOD或%）来使你的代码最后变得简单：
而不是：
AND (
( CASE ( 1299132000 - EM1.`meta_value` )
WHEN 0
THEN 1
ELSE ( 1299132000 - EM1.`meta_value` )
END
) / EM2.`meta_value`
) = 1
Do:
$current_timestamp = 1299132000 ;
AND ( ('$current_timestamp' - EM1.`meta_value` ) MOD EM2.`meta_value`) = 1")
为了进一步说明，我们可以包括不会永久重复的事件。可以添加类似于“repeat_interval_1_end”的内容来表示最后一个“repeat_interval_1”的日期。然而，这使得查询更加复杂，我真的无法想出如何做到这一点...也许有人可以帮忙！
- dorogz
1
你提供的两个例子非常简单，它们可以表示为一个简单的时间间隔（第一个是四天，第二个是14天）。如何建模完全取决于您重复事件的复杂性。如果您上面的内容真的很简单，那么请存储开始日期和重复间隔的天数。
然而，如果您需要支持以下内容：
Event A 从2011年3月3日开始，每月在该月的第三天重复
或者
Event A 从2011年3月11日开始，每月重复第二个星期五
那么这就是一个更复杂的模式。
然而，如果您需要支持以下内容：
Event A 从2011年3月3日开始，每月在该月的第三天重复
或者
Event A 从2011年3月11日开始，每月重复第二个星期五
那么这就是一个更复杂的模式。
- Adam Robinson
1
1我可能会在以后添加你刚刚提到的更复杂的规则，但现在不会。我该如何建模SQL查询以获取2011年3月7日的事件，以便它可以获取我的重复事件？ - Brandon
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
1299132000被硬编码了吗？如果我需要获取给定结束日期的发生日期和用户，这将会怎样？ - Murali Murugesan