Статьи

Как создать запланированные события в MySQL

Январь 20, 2019

Это третья и последняя статья в серии об автоматизации баз данных с помощью триггеров и событий . Если вы этого еще не сделали, прочитайте, как создать триггеры в MySQL, где представлены многие из обсуждаемых здесь концепций.

Событие похоже на триггер. Однако вместо того, чтобы запускаться в ответ на изменение данных, можно запланировать запуск событий любое количество раз в течение определенного периода. По сути, это задание cron только для базы данных.

События поддерживаются в MySQL начиная с версии 5.1. Они идеально подходят для задач обслуживания, таких как архивирование данных или генерация отчетов, которые могут быть запланированы в непиковое время.

План нашей базы данных

У нашей базы данных блогов есть проблема. Старые сообщения помечаются как удаленные, а не удаляются из таблицы `blog`. Наш стол будет расти бесконечно и со временем станет медленнее. Мы могли бы очистить старые сообщения, но это навсегда удалило бы их. Поэтому мы будем перемещать записи и связанные с ними записи аудита в архивные таблицы. Архивные таблицы могут увеличиваться, не влияя на скорость основного веб-приложения, и мы можем при необходимости восстановить старые сообщения.

Требуются две архивные таблицы:

  • `blog_archive`: идентично таблице` blog`, за исключением того, что не требует удаленного флага или идентификатора с автоинкрементом.
  • `audit_archive`: идентично таблице` audit`, за исключением того, что временная метка не генерируется автоматически и не требует автоматического увеличения идентификатора.

Следующий SQL создает обе таблицы: 


CREATE TABLE `blog_archive` (
	`id` mediumint(8) unsigned NOT NULL,
	`title` text,
	`content` text,
	PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Blog posts archive';
-- 
CREATE TABLE `audit_archive` (
	`id` mediumint(8) unsigned NOT NULL,
	`blog_id` mediumint(8) unsigned NOT NULL,
	`changetype` enum('NEW','EDIT','DELETE') NOT NULL,
	`changetime` timestamp NOT NULL,
	PRIMARY KEY (`id`),
	KEY `ix_blog_id` (`blog_id`),
	KEY `ix_changetype` (`changetype`),
	KEY `ix_changetime` (`changetime`),
	CONSTRAINT `FK_audit_blog_archive_id` FOREIGN KEY (`blog_id`) REFERENCES `blog_archive` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Запуск планировщика событий MySQL

События MySQL выполняются специальным потоком планировщика событий. По умолчанию он отключен, поэтому используйте следующую команду MySQL, чтобы определить, работает ли он: 

 
SHOW PROCESSLIST;

Если планировщик запущен, будут отображены как минимум две строки, а одна из них будет иметь свое пользовательское поле, равное «event_scheduler». Если возвращается только одна строка, планировщик отключается и события не запускаются.

Вы можете убедиться, что планировщик запускается при запуске MySQL, с параметром командной строки --event-scheduler=ONevent_scheduler=ON

Кроме того, вы можете запустить планировщик из командной строки MySQL: 

 
SET GLOBAL event_scheduler = ON;

Создание события

Нам требуется запланированное мероприятие, которое:

  • Копирует сообщения из `blog` в` blog_archive`, когда для удаленного флага установлено значение 1.
  • Копирует связанные записи аудита для этих постов из Audit в Audit_archive.
  • Физически удаляет заархивированные сообщения из таблицы `blog`. Ссылочная целостность была определена с помощью внешнего ключа, поэтому все связанные записи аудита для этих сообщений также будут удалены.

Предполагая, что у вас есть права MySQL для создания событий, основной синтаксис: 

 
CREATE EVENT `event_name` 
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE] 
[ENABLE | DISABLE | DISABLE ON SLAVE]
DO BEGIN
	-- event body
END;

В расписании могут быть назначены различные настройки, например

  • Запустите один раз в определенную дату / время:
    AT ‘YYYY-MM-DD HH: MM.SS’
    например, AT ‘2011-06-01 02: 00.00’
  • Запустите один раз после истечения определенного периода:
    AT CURRENT_TIMESTAMP + ИНТЕРВАЛ n [ЧАС | МЕСЯЦ | НЕДЕЛЯ | ДЕНЬ | МИНУТА]
    например, AT CURRENT_TIMESTAMP + ИНТЕРВАЛ 1 ДЕНЬ
  • Запускать через определенные промежутки времени навсегда:
    КАЖДЫЙ n [ЧАС | МЕСЯЦ | НЕДЕЛЯ | ДЕНЬ | МИНУТА]
    например, КАЖДЫЙ 1 ДЕНЬ
  • Запускать через определенные интервалы в течение определенного периода:
    КАЖДЫЙ n [ЧАС | МЕСЯЦ | НЕДЕЛЯ | ДЕНЬ | МИНУТА] НАЧИНАЕТСЯ дата КОНЕЦ дата
    например, КАЖДЫЙ 1 ДЕНЬ НАЧИНАЕТСЯ CURRENT_TIMESTAMP + ИНТЕРВАЛ 1 НЕДЕЛЯ КОНЦЫ ‘2012-01-01 00: 00.00’

Событие обычно удаляется после истечения его расписания (ПО ЗАВЕРШЕНИЮ НЕ ЗАХВАТЫВАЕТСЯ). Установите ON COMPLETION PRESERVE, чтобы предотвратить такое поведение. Синтаксическая документация MySQL CREATE EVENT содержит дополнительную информацию.

Теперь мы можем определить наше событие (не забывая сначала установить DELIMITER). Мы настроим его запуск каждую неделю, начиная с воскресного утра: 

 
DELIMITER $$

CREATE 
	EVENT `archive_blogs` 
	ON SCHEDULE EVERY 1 WEEK STARTS '2011-07-24 03:00:00' 
	DO BEGIN
	
		-- copy deleted posts
		INSERT INTO blog_archive (id, title, content) 
		SELECT id, title, content
		FROM blog
		WHERE deleted = 1;
	    
		-- copy associated audit records
		INSERT INTO audit_archive (id, blog_id, changetype, changetime) 
		SELECT audit.id, audit.blog_id, audit.changetype, audit.changetime 
		FROM audit
		JOIN blog ON audit.blog_id = blog.id
		WHERE blog.deleted = 1;
		
		-- remove deleted blogs and audit entries
		DELETE FROM blog WHERE deleted = 1;
	    
	END */$$

DELIMITER ;

Это простой пример, но вы можете добавить больше функциональности, например, перемещать только те сообщения, которые были удалены как минимум 1 месяц назад, и удалять все архивные сообщения старше 1 года. Я надеюсь, что вам понравилась эта серия, и вы рассматриваете триггеры базы данных и события в вашем следующем проекте.

40
Share: