我正在构建一个自定义事件系统，如果你有一个重复的事件，看起来像这样:
事件A从2011年3月3日开始每4天重复一次
or
赛事B从2011年3月1日开始，每两周在周二举行一次
我如何将其存储在数据库中，使其易于查找。如果有大量的事件，我不希望出现性能问题，而且在呈现日历时，我必须遍历每一个事件。
当前回答
虽然目前接受的答案对我来说有很大的帮助，但我想分享一些有用的修改，它们可以简化查询并提高性能。
“简单”重复事件
处理定期发生的事件，例如:
Repeat every other day
or
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)。
注意模数(%符号)的使用。此符号类似于常规除法，但返回“余数”而不是商，因此当当前日期是repeat_interval与repeat_start的精确倍数时，则返回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
or
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数据。
这使得同时选择两种类型非常简单。只需要循环每天并填写正确的值(1370563200表示2013年6月7日，然后是年、月、日、周数和工作日，如下所示):
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日期的默认工作日索引，所以“5”为星期五
其他回答
对于所有对此感兴趣的人，现在你可以在几分钟内复制粘贴开始学习。我尽可能地采纳了评论中的建议。如果我遗漏了什么，请告诉我。
“复杂版”:
事件
+----------+----------------+ | ID | NAME | +----------+----------------+ | 1 | Sample event 1 | | 2 | Second event | | 3 | Third event | +----------+----------------+
events_meta
+----+----------+--------------+------------------+-------------+--------------+------------+-------------+----------------+ | 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示例代码:
<?
// ----------------------------------------------------------------------------------------------------
// 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 设置这个需要几分钟。没有时间。：）
增强:用日期替换时间戳
作为对公认答案的一个小改进，随后由ahoffner改进-可以使用日期格式而不是时间戳。优点是:
数据库中可读的日期 年份> 2038和时间戳没有问题 删除时需要小心基于季节性调整日期的时间戳，即在英国，6月28日比12月28日早一个小时开始，因此从日期中获得时间戳可能会破坏递归算法。
要做到这一点，将DB repeat_start更改为'date'类型，并且repeat_interval现在保存天数而不是秒。即重复7天。
修改SQL行:
WHERE (( 1370563200 - repeat_start) % repeat_interval = 0 )
to:
WHERE ( DATEDIFF( '2013-6-7', repeat_start ) % repeat_interval = 0)
其他一切都保持不变。简单的!
存储“简单”重复模式
对于我的基于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_interval。表格看起来是这样的:
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
将{current_timestamp}替换为当前日期的unix时间戳(减去时间，因此小时、分钟和秒的值将被设置为0)。
希望这也能帮助到其他人!
存储“复杂”重复模式
此方法更适合存储复杂的模式，例如
事件A从2011年3月3日开始，每月3日重复发生
or
事件A从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之间。在星期几中重复_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
这种方法与上述方法相结合，可以覆盖大多数重复/重复事件模式。如果我遗漏了什么，请留下评论。
@Rogue编码器
这太棒了!
你可以简单地使用模运算(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”的日期。然而，这使得查询更复杂，我真的不知道如何做到这一点…
也许有人可以帮忙!
虽然目前接受的答案对我来说有很大的帮助，但我想分享一些有用的修改，它们可以简化查询并提高性能。
“简单”重复事件
处理定期发生的事件，例如:
Repeat every other day
or
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)。
注意模数(%符号)的使用。此符号类似于常规除法，但返回“余数”而不是商，因此当当前日期是repeat_interval与repeat_start的精确倍数时，则返回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
or
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数据。
这使得同时选择两种类型非常简单。只需要循环每天并填写正确的值(1370563200表示2013年6月7日，然后是年、月、日、周数和工作日，如下所示):
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日期的默认工作日索引，所以“5”为星期五
推荐文章
- 使用电子邮件地址为主键?
- 在日历应用程序中建模重复事件的最佳方法是什么?
- 第一次设计数据库:我是否过度设计了?
- 我应该在SQL varchar(长度)中考虑电话的最长的全球电话号码是什么
- System.currentTimeMillis() vs. new Date() vs. Calendar.getInstance().getTime()
- “防止保存需要重新创建表的更改”的负面影响
- 多语言数据库的模式
- 在实体框架的1:1关系中，关联的主体结束意味着什么
- 外键有什么问题?
- 什么是数据库的标准形式，你能举例说明吗?
- 客户端ip地址的最大长度
- 关系数据库设计模式?
- ON [PRIMARY]是什么意思?
- 如何在数据库中表示继承?
- 标签或标签的推荐SQL数据库设计
