События (events) в MySQL (планировщик заданий аля CRON)
Предисловие
Наверное в 99% случаев если необходимо делать операции над данными в БД использовалась связка PHP и CRONа и в скрипте описывались все необходимые запросы. В MySQL 5.1.6 появился планировщик. Теперь можно не терзая указанную выше связку, если она не требует чего-то больше, что не умеет делать MySQL.
Для работы вам понадобится:
сервер БД 5.1.6 и выше; права пользователю на использование этой фичи.
Проверка работоспособности планировщика
Первое, что надо сделать — это удостоверится, что планировщик включен. Это можно сделать по команде
SHOW VARIABLES LIKE '%event%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+
Получается он у меня выключен, ну что же можем включить:
SET GLOBAL event_scheduler = ON;
тем самым мы включили его до перезагрузки сервера!!! Что бы планировщик был постоянно включен необходимо в файле конфигурации поставить параметр: event_scheduler=1.
Теперь придумаем задание для того, что бы на его примере всё пояснить.
ЗАДАНИЕ
Например мы имеем таблицу users и нам каждый день в 00-00 необходимо выбирать всех, кто родился в этот день и записывать данные в табл users_dr (ФИО и пол). Можно конечно обойтись и простой выборкой по текущему дню, но если сайт с высокой нагрузкой, то делать выборку каждый раз это будет смерть БД.
Создание планировщика
Оператор планировщика имеет следующий синтаксис:
CREATE [DEFINER = { user | CURRENT_USER }] EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] DO sql_statement; schedule: AT timestamp [+ INTERVAL interval] ... | EVERY interval [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...] interval: quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Много букв, но ничего, разберем на нашем примере.
DELIMITER ;; DROP EVENT IF EXISTS `test_user_dr;; CREATE EVENT `test_user_dr` ON SCHEDULE EVERY 1 DAY STARTS '2009-07-19 00:00:00' ON COMPLETION PRESERVE ENABLE COMMENT 'таблица дней рождений' DO BEGIN DROP TABLE IF EXISTS `test`.`users_dr`; CREATE TABLE `test`.`users_dr` SELECT `fio`,`sex` FROM `test`.`users` WHERE `dr`=DATE_FORMAT(NOW(),"%d-%m")=DATE_FORMAT(`dr`,"%d-%m") ORDER BY `fio` ASC; END;; DELIMITER ;
Теперь разберем.
Т.к. в планировщике используется больше одного запроса, то эти запросы необходимо ограничивать директивами BEGIN и END, а для того, что бы MySQL не начал выполнять операции внутри планировщика, нам надо переопределить символ обозначающий конец запроса, по дефолту это ; Оператором DELIMITER ;; мы указываем, что теперь две косые есть конец запроса.
На всякий случай удаляем планировщик DROP EVENT IF EXISTS `test_user_dr` // Создаем новый планировщик `test_user_dr` с параметрами ON SCHEDULE EVERY 1 DAY STARTS ‘2009-07-19 00:00:00’, означает каждый в 00-00-00 начиная с указанной даты, параметр DAY — интервал, может принимать и другие параметры, о них написано выше. Далее говорим ON COMPLETION PRESERVE ENABLE — включаем этот планировщик и если он завершиться ОКОНЧАТЕЛЬНО, то его из памяти не удаляем. Наш планировщик никогда не завершается, т.к. нет директивы ENDS timestamp, но если бы он была и было написано ON COMPLETION NOT PRESERVE, то всё задание удалилось. С комментарием, думаю, все понятно. Далее после слов DO BEGIN идут запросы, которые будут выполняться планировщиком и заканчивает директивой END.
Теперь планировщик будет каждый день начиная с 19 июля 2009 года в полночь делать таблицу пользователей у которых ДР в текущий день.
Удаление планировщика
Тут все просто, используем оператор
DROP EVENT [IF EXISTS] event_name</pre> В нашем случае <pre lang="sql">DROP EVENT IF EXISTS `test_user_dr`;</pre> <h2>Изменение планировщика</h2> <pre lang="sql">ALTER [DEFINER = { user | CURRENT_USER }] EVENT event_name [ON SCHEDULE schedule] [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_event_name] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] [DO sql_statement]
Например надо изменить время срабатывания, ну например чтобы таблицы ДР обновлялась 2 раза в сутки, тогда следует написать
ALTER EVENT `test_user_dr` ON SCHEDULE EVERY 12 HOUR;
Список планировщиков
Список заданий можно посмотреть 2-мя способами:
1-й покажет все данные по всем планировщикам для БД test
SHOW EVENTS FROM `test`\G; *************************** 1. row *************************** Db: test Name: test_user_dr Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 12 Interval field: HOUR Starts: 2009-07-19 00:00:00 Ends: NULL Status: ENABLED Originator: 0 character_set_client: cp1251 collation_connection: cp1251_general_ci Database Collation: cp1251_general_ci 1 row in set (0.00 sec)
2-й Показывает полностью данные для указанного задания
SHOW CREATE EVENT `test_user_dr`\G; *************************** 1. row *************************** Event: test_user_dr sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION time_zone: SYSTEM Create Event: CREATE EVENT `test_user_dr` и весь текст планировщика... character_set_client: cp1251 collation_connection: cp1251_general_ci Database Collation: cp1251_general_ci 1 row in set (0.00 sec)
Ну вроде все. Если есть вопросы пишите, буду отвечать.
Ссылки
Статья просмотренна 82284 раз, зашло посетителей 28761