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