События (events) в MySQL (планировщик заданий аля CRON)

Ноябрь30

Предисловие

Наверное в 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)

Ну вроде все. Если есть вопросы пишите, буду отвечать.