日历重复/重复事件 - 最佳存储方法

最新推荐文章于 2024-06-02 09:39:07 发布

xfxf996

最新推荐文章于 2024-06-02 09:39:07 发布

阅读量1.7k

点赞数

文章标签： database-design calendar

原文链接：https://oldbug.net/q/lKuW/Calendar-Recurring-Repeating-Events-Best-Storage-Method

版权

本文翻译自：Calendar Recurring/Repeating Events - Best Storage Method

I am building a custom events system, and if you have a repeating event that looks like this: 我正在构建一个自定义事件系统，如果你有一个如下所示的重复事件：

Event A repeats every 4 days starting on March 3, 2011 活动A从2011年3月3日起每4天重复一次

or 要么

Event B repeats every 2 weeks on Tuesday starting on March 1, 2011 活动B于2011年3月1日星期二每2周重复一次

How can I store that in a Database in a way that would make it simple to lookup. 如何以一种易于查找的方式将其存储在数据库中。 I don't want performance issues if there are a large number of events, and I have to go through each and every one when rendering the calendar. 如果有大量事件，我不希望出现性能问题，在渲染日历时我必须经历每一个事件。

#1楼

参考：https://stackoom.com/question/lKuW/日历重复-重复事件-最佳存储方法

#2楼

@Rogue Coder @Rogue Coder

This is great! 这很棒！

You could simply use the modulo operation (MOD or % in mysql) to make your code simple at the end: 您可以简单地使用模运算（在mysql中使用MOD或％）来使代码在最后变得简单：

Instead of: 代替：

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")

To take this further, one could include events that do not recur for ever. 为了更进一步，可以包括不会永远复发的事件。

Something like "repeat_interval_1_end" to denote the date of the last "repeat_interval_1" could be added. 可以添加类似“repeat_interval_1_end”的内容来表示最后一个“repeat_interval_1”的日期。 This however, makes the query more complicated and I can't really figure out how to do this ... 然而，这使查询更复杂，我无法弄清楚如何做到这一点......

Maybe someone could help! 也许有人可以帮忙！

#3楼

While the currently accepted answer was a huge help to me, I wanted to share some useful modifications that simplify the queries and also increase performance. 虽然目前接受的答案对我来说是一个巨大的帮助，但我想分享一些有用的修改，以简化查询并提高性能。

"Simple" Repeat Events “简单”重复事件

To handle events which recur at regular intervals, such as: 处理定期重复的事件，例如：

Repeat every other day

or 要么

Repeat every week on Tuesday

You should create two tables, one called events like this: 你应该创建两个表，一个叫做这样的events ：

ID    NAME
1     Sample Event
2     Another Event

And a table called events_meta like this: 还有一个名为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

With repeat_start being a unix timestamp date with no time (1369008000 corresponds to May 20th 2013) , and repeat_interval an amount in seconds between intervals (604800 is 7 days). repeat_start是没有时间的unix时间戳日期（1369008000对应于2013年5月20日）， repeat_interval是间隔之间的秒数（604800是7天）。

By looping over each day in the calendar you can get repeat events using this simple query: 通过循环日历中的每一天，您可以使用此简单查询获得重复事件：

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
WHERE  (( 1299736800 - repeat_start) % repeat_interval = 0 )

Just substitute in the unix-timestamp (1299736800) for each date in your calendar. 只需在日历中的每个日期替换unix-timestamp（1299736800）即可。

Note the use of the modulo (% sign). 注意使用模数（％符号）。 This symbol is like regular division, but returns the ''remainder'' instead of the quotient, and as such is 0 whenever the current date is an exact multiple of the repeat_interval from the repeat_start. 此符号类似于常规除法，但返回“余数”而不是商，因此只要当前日期是repeat_start的repeat_interval的精确倍数，就会返回0。

Performance Comparison 绩效比较

This is significantly faster than the previously suggested "meta_keys"-based answer, which was as follows: 这明显快于之前建议的基于“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

If you run EXPLAIN this query, you'll note that it required the use of a join buffer: 如果你运行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 |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+--------------------------------+

The solution with 1 join above requires no such buffer. 上面有1个连接的解决方案不需要这样的缓冲区。

"Complex" Patterns “复杂”模式

You can add support for more complex types to support these types of repeat rules: 您可以添加对更复杂类型的支持，以支持这些类型的重复规则：

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

Your events table can look exactly the same: 您的事件表看起来完全相同：

ID    NAME
1     Sample Event
2     Another Event

Then to add support for these complex rules add columns to events_meta like so: 然后，为了添加对这些复杂规则的支持，将列添加到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

Note that you simply need to either specify a repeat_interval or a set of repeat_year , repeat_month , repeat_day , repeat_week , and repeat_weekday data. 请注意，您只需指定repeat_interval 或一组repeat_year ， repeat_month ， repeat_day ， repeat_week和repeat_weekday数据。

This makes selection of both types simultaneously very simple. 这使得同时选择两种类型非常简单。 Just loop through each day and fill in the correct values, (1370563200 for June 7th 2013, and then the year, month, day, week number and weekday as follows): 只需遍历每一天并填写正确的值（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
  )

This returns all events that repeat on the Friday of the 2nd week, as well as any events that repeat every Friday, so it returns both event ID 1 and 2: 这将返回在第二周的星期五重复的所有事件，以及每周五重复的任何事件，因此它返回事件ID 1和2：

ID    NAME
1     Sample Event
2     Another Event

*Sidenote in the above SQL I used PHP Date's default weekday indexes, so "5" for Friday *上面SQL中的Sidenote我使用了PHP Date的默认工作日索引，所以周五为“5”

Hope this helps others as much as the original answer helped me! 希望这能帮助别人，就像原来的答案帮助我一样！

#4楼

Enhancement: replace timestamp with date 增强功能：将时间戳替换为日期

As a small enhancement to the accepted answer that was subsequently refined by ahoffner - it is possible to use a date format rather than timestamp. 作为随后由ahoffner提炼的已接受答案的一个小改进 - 可以使用日期格式而不是时间戳。 The advantages are: 优点是：

readable dates in the database 数据库中的可读日期
no issue with the years > 2038 and timestamp 没有问题> 2038年和时间戳
removes need to be careful with timestamps that are based on seasonally adjusted dates ie in the UK 28th June starts one hour earlier than 28th December so deriving a timestamp from a date can break the recursion algorithm. 删除需要注意基于季节性调整日期的时间戳，即英国6月28日比12月28日提前一小时开始，因此从日期得到时间戳可以打破递归算法。

to do this, change the DB repeat_start to be stored as type 'date' and repeat_interval now hold days rather than seconds. 要执行此操作，请将DB repeat_start更改为“date”类型，并将repeat_interval保存为days而不是seconds。 ie 7 for a repeat of 7 days. 即7天重复7天。

change the sql line: 更改sql行：

WHERE (( 1370563200 - repeat_start) % repeat_interval = 0 )

to: 至：

WHERE ( DATEDIFF( '2013-6-7', repeat_start ) % repeat_interval = 0)

everything else remains the same. 其他一切都是一样的。 Simples! Simples！

#5楼

I developed an esoteric programming language just for this case. 我为这种情况开发了一种深奥的编程语言。 The best part about it is that it is schema less and platform independent. 关于它的最好的部分是它更少架构和平台独立。 You just have to write a selector program, for your schedule, syntax of which is constrained by the set of rules described here - 你只需编写一个选择器程序，为你的日程安排，其语法受到这里描述的规则集的约束 -

https://github.com/tusharmath/sheql/wiki/Rules https://github.com/tusharmath/sheql/wiki/Rules

The rules are extendible and you can add any sort of customization based on the kind of repetition logic you want to perform, without worrying about schema migrations etc. 规则是可扩展的，您可以根据要执行的重复逻辑类型添加任何类型的自定义，而无需担心架构迁移等。

This is a completely different approach and might have some disadvantages of its own. 这是一种完全不同的方法，可能有其自身的一些缺点。

#6楼

For all of you who are interested in this, now you can just copy and paste to get started within minutes. 对于所有对此感兴趣的人，现在您只需复制并粘贴即可在几分钟内开始使用。 I took the advice in the comments as well as I could. 我尽可能地在评论中接受了建议。 Let me know if I'm missing something. 如果我遗失了某些东西，请告诉我。

"COMPLEX VERSION": “复杂版本”：

events 事件

+----------+----------------+
| ID       | NAME           | 
+----------+----------------+
| 1        | Sample event 1 |
| 2        | Second  event  |
| 3        | Third event    |
+----------+----------------+

events_meta 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 code: 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');

also available as MySQL export (for easy access) 也可用作MySQL导出（便于访问）

PHP example code index.php: 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 example code connect.php: 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
// ----------------------------------------------------------------------------------------------------
?>

Also the php code is available here (for better readability): 此处还提供了php代码（为了更好的可读性）：
index.php 的index.php
and 和
connect.php connect.php
Now setting this up should take you minutes. 现在设置它应该花费你几分钟。 Not hours. 不是几个小时 :) :)