mysql在where子句中拆分字符串

Question

我有一个事件系统，对于我的重复事件，我使用了类似 cron 的系统。

重复事件:

+----+----------+--------------+
| id | event_id | repeat_value |
+----+----------+--------------+
|  1 |       11 | *_*_*        |
|  2 |       12 | *_*_2        |
|  3 |       13 | *_*_4/2      |
|  4 |       14 | 23_*_*       |
|  5 |       15 | 30_05_*      |
+----+----------+--------------+

注意:cron 值是 day_month_day of week

事件:

+----+------------------------+---------------------+---------------------+
| id | name                   | start_date_time     | end_date_time       |
+----+------------------------+---------------------+---------------------+
| 11 | Repeat daily           | 2014-04-30 12:00:00 | 2014-04-30 12:15:00 |
| 12 | Repeat weekly          | 2014-05-06 12:00:00 | 2014-05-06 13:00:00 |
| 13 | Repeat every two weeks | 2014-05-08 12:45:00 | 2014-05-08 13:45:00 |
| 14 | Repeat monthly         | 2014-05-23 15:15:00 | 2014-05-23 16:00:00 |
| 15 | Repeat yearly          | 2014-05-30 07:30:00 | 2014-05-30 10:15:00 |
+----+------------------------+---------------------+---------------------+

无论如何，我有一个查询来选择事件:

SELECT *
FROM RepeatEvent
JOIN `Event` 
ON `Event`.`id` = `RepeatEvent`.`event_id`

这会产生:

+----+----------+--------------+----+------------------------+---------------------+---------------------+
| id | event_id | repeat_value | id | name                   | start_date_time     | end_date_time       |
+----+----------+--------------+----+------------------------+---------------------+---------------------+
|  1 |       11 | *_*_*        | 11 | Repeat daily           | 2014-04-30 12:00:00 | 2014-04-30 12:15:00 |
|  2 |       12 | *_*_2        | 12 | Repeat weekly          | 2014-05-06 12:00:00 | 2014-05-06 13:00:00 |
|  3 |       13 | *_*_4/2      | 13 | Repeat every two weeks | 2014-05-08 12:45:00 | 2014-05-08 13:45:00 |
|  4 |       14 | 23_*_*       | 14 | Repeat monthly         | 2014-05-23 15:15:00 | 2014-05-23 16:00:00 |
|  5 |       15 | 30_05_*      | 15 | Repeat yearly          | 2014-05-30 07:30:00 | 2014-05-30 10:15:00 |
+----+----------+--------------+----+------------------------+---------------------+---------------------+

但是，我想在一个月内选择事件。我只会有一定的条件:每天、每周、每两周、每月和每年。
我想在我的 where 子句中添加一种划分重复值字符串的方法，如果它符合以下任何条件以将其显示为结果(repeatEvent 是正在询问的行，search 是正在查找的日期) :

array(3) = string_divide(repeat_value, '_')
daily = array(0)
monthy = array(1)
dayOfWeek = array(2)

if(daily == '*' && month == '*' && dayOfWeek == '*') //returns all the daily events as they will happen
    return repeatEvent

if(if(daily == '*' && month == '*' && dayOfWeek == search.dayOfWeek) //returns all the events on specific day
    return repeatEvent

if(daily == search.date && month == '*' && dayOfWeek == '*') //returns all the daily events as they will happen
    return repeatEvent

if (contains(dayOfWeek, '/'))
    array(2) = string_divide(dayOfWeek,'/')
    specificDayOfWeek = array(0);
    if(specificDayOfWeek == repeatEvent.start_date.dayNumber)
            if(timestampOf(search.timestamp)-timestampOf(repeatEvent.start_date)/604800 == (0 OR EVEN)
                return repeatEvent

if(daily == search.date && month == search.month && dayOfWeek == '*') //returns a single yearly event (shouldn't often crop up)
    return repeatEvent 

//everything else is either an unknown format of repeat_value or not an event on this day

总而言之，我想运行一个查询，其中重复值在 where 子句中拆分，我可以查询拆分项。我看过游标，但互联网似乎建议不要使用它们。

我可以处理在 PHP 中选择所有重复事件的结果，但是，我想这会很慢。

以下是我想看看四月份的情况:

+----------+--------------+----+------------------------+---------------------+---------------------+
| event_id | repeat_value | id | name                   | start_date_time     | end_date_time       |
+----------+--------------+----+------------------------+---------------------+---------------------+
|       11 | *_*_*        | 11 | Repeat daily           | 2014-04-30 12:00:00 | 2014-04-30 12:15:00 |
+----------+--------------+----+------------------------+---------------------+---------------------+

这是我想看看五月份的情况

+----------+--------------+----+------------------------+---------------------+---------------------+
| event_id | repeat_value | id | name                   | start_date_time     | end_date_time       |
+----------+--------------+----+------------------------+---------------------+---------------------+
|       11 | *_*_*        | 11 | Repeat daily           | 2014-04-30 12:00:00 | 2014-04-30 12:15:00 |
|       12 | *_*_2        | 12 | Repeat weekly          | 2014-05-06 12:00:00 | 2014-05-06 13:00:00 |
|       13 | *_*_4/2      | 13 | Repeat every two weeks | 2014-05-08 12:45:00 | 2014-05-08 13:45:00 |
|       14 | 23_*_*       | 14 | Repeat monthly         | 2014-05-23 15:15:00 | 2014-05-23 16:00:00 |
|       15 | 30_05_*      | 15 | Repeat yearly          | 2014-05-30 07:30:00 | 2014-05-30 10:15:00 |
+----------+--------------+----+------------------------+---------------------+---------------------+

如果看 6 月份，这是我想看到的

+----------+--------------+----+------------------------+---------------------+---------------------+
| event_id | repeat_value | id | name                   | start_date_time     | end_date_time       |
+----------+--------------+----+------------------------+---------------------+---------------------+
|       11 | *_*_*        | 11 | Repeat daily           | 2014-04-30 12:00:00 | 2014-04-30 12:15:00 |
|       12 | *_*_2        | 12 | Repeat weekly          | 2014-05-06 12:00:00 | 2014-05-06 13:00:00 |
|       13 | *_*_4/2      | 13 | Repeat every two weeks | 2014-05-08 12:45:00 | 2014-05-08 13:45:00 |
|       14 | 23_*_*       | 14 | Repeat monthly         | 2014-05-23 15:15:00 | 2014-05-23 16:00:00 |
+----------+--------------+----+------------------------+---------------------+---------------------+

Answer 1

您可以在上面贴上创可贴，但没有人会帮您任何忙告诉您这就是答案。
如果您的 MySQL 数据库可以更改，我强烈建议您使用下划线分割当前列 day_month_day of year到三个单独的列，day , month , 和 day_of_year .我还建议您将格式更改为 INT而不是 VARCHAR .这将使搜索和解析更快，更容易，因为它的设计方式不需要通过复杂的程序翻译成计算机语言......它已经是大部分方式了。
原因如下:
原因一:你的表没有优化
无论您在此阶段选择做什么，您的表都没有优化，并且会变慢。 SQL 不是为了在一列中有多个值而构建的。 SQL 数据库的全部意义在于将值拆分为不同的列和行。
规范化这个表的好处是搜索它会更快，并且您将能够在 MySQL 中构建查询。看看Normalization .这是一个复杂的概念，但是一旦你明白了，你就可以避免创建凌乱而复杂的程序。
原因 2:可以稍微调整您的表格以利用计算机日期/时间功能的强大功能。
计算机遵循基于 Unix 纪元时间的时间。它以秒为单位，始终在您的计算机中运行。事实上，计算机一直在计算这一点，顾名思义，第一台 Unix 计算机被打开。此外，每台计算机和基于计算机的程序/系统都具有内置的快速日期和时间功能。 MySQL没有什么不同。
我还建议将所有这些都存储为整数。 repeat_doy (一年中的一天)很容易成为 smallint或至少一个标准 int , 而不是输入月份和日期，您可以输入一年中实际的 1-365 天。您可以使用 DAY_OF_YEAR(NOW())将其输入 MySQL。要将其作为日期拉回来，您可以使用 MAKEDATE(YEAR(NOW),repeat_doy) .您可以使用 0 或 NULL 代替星号来表示全部。
使用类似 cron 的系统，您可能无论如何都不需要进行这种计算。
相反，在别处测量一年中的某一天可能会更容易(每台计算机和语言都可以做到这一点。在 Unix 中它只是 date "%j" )。
解决方案
将您的一个 repeat_value 拆分为三个单独的值，然后根据 UNIX 时间值将它们全部转换为整数。日为 1-7(或 0-6 为周日至周六)，月为 1-12，一年中的某一天为 1-365(请记住，我们不包括 366，因为我们的年份基于任意的非闰年)。
如果您想在 SELECT 中提取信息以您的原始格式查询，使用更容易concat合并三列而不是尝试在一列上搜索和拆分。您还可以轻松地利用内置的 MySQL 函数来快速将您提取的内容变成真实的、当前的、日子，而无需您付出大量的努力。
要在 SQL 数据库中实现它:

+----+----------+--------------+--------------+------------+
| id | event_id | repeat_day   | repeat_month | repeat_doy |
+----+----------+--------------+--------------+------------+
|  1 |       11 |      *       |       *      |      *     | 
|  2 |       12 |      *       |       *      |      2     |
|  3 |       13 |      *       |       *      |      4/2   |
|  4 |       14 |     23       |       *      |      *     |
|  5 |       15 |     30       |       5      |      *     |
+----+----------+--------------+--------------+------------+

现在，无论您的查询有多复杂，您都应该能够构建一个查询来将所有这些数据放在一起。通过规范化您的表，您将能够充分利用关系数据库的强大功能，而不会遇到麻烦和黑客攻击。
编辑
Hugo Delsing 在下面的评论中提出了一个很好的观点。在我最初的示例中，我为 day_of_year 提供了闰年的修复。我选择忽略 2 月 29 日。更好的解决方案无需修复。拆分 day_of_year至 month和 day带有复合索引。他也有关于周数和周数的建议，但我只是建议您阅读以了解更多详细信息。