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

Love The Way You Lie 2022-11-28 10:38 83阅读 0赞

本文翻译自：[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/日历重复-重复事件-最佳存储方法][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][PHP Date_s] default weekday indexes, so "5" for Friday* **\*上面SQL中的Sidenote我使用了[PHP Date的][PHP Date_s]默认工作日索引，所以周五为“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:* **优点是：**

1.  *readable dates in the database* **数据库中的可读日期**
2.  *no issue with the years > 2038 and timestamp* **没有问题> 2038年和时间戳**
3.  *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]* **[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导出][MySQL export] （便于访问）**

*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 . " ";
 echo $week . " " . $weekday . " ";
 
 
 
 $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'] . " ";
 }
 
 echo " ";
 }
 ?>

*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][index.php]**  
*and* **和**  
*[connect.php][]* **[connect.php][]**  
*Now setting this up should take you minutes.* **现在设置它应该花费你几分钟。** *Not hours.* **不是几个小时** *:)* **:)**

[Calendar Recurring_Repeating Events - Best Storage Method]: https://oldbug.net/q/lKuW/Calendar-Recurring-Repeating-Events-Best-Storage-Method
[https_stackoom.com_question_lKuW_-_-]: https://stackoom.com/question/lKuW/%E6%97%A5%E5%8E%86%E9%87%8D%E5%A4%8D-%E9%87%8D%E5%A4%8D%E4%BA%8B%E4%BB%B6-%E6%9C%80%E4%BD%B3%E5%AD%98%E5%82%A8%E6%96%B9%E6%B3%95
[PHP Date_s]: http://php.net/manual/en/function.date.php
[https_github.com_tusharmath_sheql_wiki_Rules]: https://github.com/tusharmath/sheql/wiki/Rules
[MySQL export]: http://pastebin.com/EUq00uxX
[index.php]: http://pastebin.com/WySwGRBW
[connect.php]: http://pastebin.com/emfttihJ