我需要创建一个 MySQL 语句来加快更新数据库表中每周重复事件的过程。
现在,用户正在向表中添加记录。有些记录针对单一日期事件,有些记录是在定义的开始日期和结束日期内每 7 天重复一次的事件。两种类型的记录都输入到同一个表中。
每个事件都有在两个字段中输入的事件日期的开始日期和结束日期:“date_start”和“date_end”。对于单个日期事件,用户在“date_start”和“date_end”中输入相同的日期。对于重复事件,他们在“date_start”中输入第一次事件发生的日期,在“date_end”中输入每周发生的最后一次日期。重复事件被标记为“Weekly_Event”,如下面的示例数据所示。
我需要获取该表并制作标记为“Weekly_Event”的所有记录的多个副本。开始日期和结束日期之间每周的原始记录副本一份。
在下面的示例中,记录的开始日期设置为 5 月 3 日,结束日期设置为 6 月 7 日,我需要一个 SQL 语句从该记录创建 6 个新记录:5 月 3 日、5 月 10 日、5 月 17 日、5 月 24 日、5 月 31 日, 6 月 7 日。还有另一个“Weekly_Event”和“Single_Day”事件示例。
MySQL version: 8.0.36
Table Type: InnoDB
Collation: latin1_swedish_ci
CREATE TABLE `original_shows_24` (
`id` int NOT NULL AUTO_INCREMENT,
`status` int NOT NULL DEFAULT '1',
`date_start` date NOT NULL,
`date_end` date NOT NULL,
`name` varchar(200) NOT NULL,
`details` mediumtext NOT NULL,
`url` varchar(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'https://',
`slug` varchar(128) NOT NULL,
`category` varchar(300) NOT NULL,
PRIMARY KEY (`id`),
KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `original_shows_24` (`id`, `status`, `date_start`, `date_end`, `name`, `details`, `url`, `slug`, `category`) VALUES
(1, 1, '2024-05-03', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event', 'Weekly_Event'),
(2, 1, '2024-05-09', '2024-08-18', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event', 'Weekly_Event'),
(3, 1, '2024-05-02', '2024-05-02', 'A regular Single Day Event', 'Details about this event ', 'https://link', 'a-single-day-event', 'Single_Day');
操作完成后,记录大致如下所示。我希望 Weekly_Events 的输出 date_start 和 date_end 相等,因为每个新生成的事件都发生在指定的一天内。
CREATE TABLE `updated_shows_24` (
`id` int NOT NULL AUTO_INCREMENT,
`status` int NOT NULL DEFAULT '1',
`date_start` date NOT NULL,
`date_end` date NOT NULL,
`name` varchar(200) NOT NULL,
`details` mediumtext NOT NULL,
`url` varchar(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'https://',
`slug` varchar(128) NOT NULL,
`category` varchar(300) NOT NULL,
PRIMARY KEY (`id`),
KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `updated_shows_24` (`id`, `status`, `date_start`, `date_end`, `name`, `details`, `url`, `slug`, `category`) VALUES
(1, 0, '2024-05-03', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event', 'Weekly_Event'),
(2, 0, '2024-05-09', '2024-08-18', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event', 'Weekly_Event'),
(3, 1, '2024-05-02', '2024-05-02', 'A regular Single Day Event', 'Details about this event ', 'https://link', 'a-single-day-event', 'Single_Day'),
(101, 1, '2024-05-03', '2024-05-03', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-03', 'Weekly_Event'),
(102, 1, '2024-05-10', '2024-05-10', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-10', 'Weekly_Event'),
(103, 1, '2024-05-17', '2024-05-17', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-17', 'Weekly_Event'),
(104, 1, '2024-05-24', '2024-05-24', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-24', 'Weekly_Event'),
(105, 1, '2024-05-31', '2024-05-31', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-31', 'Weekly_Event'),
(106, 1, '2024-06-07', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-06-07', 'Weekly_Event'),
(107, 1, '2024-05-09', '2024-05-09', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event-2024-05-09', 'Weekly_Event'),
(108, 1, '2024-05-16', '2024-05-16', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event-2024-05-16', 'Weekly_Event');
新事件的“id”应该是唯一的,最好是根据“original_shows_24”中当前的下一个“id”自动生成。在我的示例中,我假设“id”在“original_shows_24”中被其他记录使用最多 100 个。
请注意用于生成重复事件的事件的“状态”。在已处理的记录中设置为“0”(“date_start”不等于“date_end”且“category”=“Weekly_Event”)
最终输出中需要考虑的另一个元素:生成的 slug。slug 字段用于生成该事件的唯一子页面的链接,因此也需要在此操作中进行更新。请注意示例数据中的格式,基本上我需要在生成的每个新记录的数据段末尾附加破折号(“-”)和“date_start”。“slug”中的所有条目都必须是唯一的。
它还需要能够随着月份的变化生成正确的日期。例如,示例数据中的最后一个事件是 6 月 7 日。这是 5 月 31 日之后的 7 天。如果 MySQL 在日期操作中自动处理这一点,那就太好了。
我只创建了“updated_shows_24”来显示输出。如果输出可以放回“original_shows_24”(相应的记录状态从“1”更改为“0”),那就最好了。此外,“updated_shows_24”仅显示另一个重复事件的前两条记录,只是为了说明所需的输出。由于该重复事件的结束日期是 2024 年 8 月 18 日,因此解决方案输出的记录将比两个 IU 所包含的记录多得多。
谢谢你的帮助。
演示版
小提琴
原始数据已被编辑,范围略有缩短。演示中不需要的列也已被删除。
ORDER BY 提供结果的可见性。
category
不使用,因为事件类别可以由date_start
和date_end
等价性确定。