За чистый и ясный код!

Статьи на тему программирования под веб, используя PHP, MySQL, Jquery и многое другое
Просмотр категории - MySQL

Работа с trigger (триггерами) в MySQL

Декабрь2

Предисловие

Триггеры это особые процедуры которые срабатывают при изменении данных в таблицы операторами INSERT, UPDATE и DELETE.

Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы. Получается, что триггер может быть в 6 состояниях.

INSERT (BEFORE | AFTER)
UPDATE (BEFORE | AFTER)
DELETE (BEFORE | AFTER)

Что необходимо для работы

Cервер БД версии 5.0.2 и выше
привилегии на использование этой ф-ции (TRIGGER) начиная с версии 5.1.6, до этого было SUPER, но т.к. все локально используют пользователя root, то опасаться нечего

Добавление триггера

Для того чтобы понять работу триггера необходим пример.

Задание

Необходимо при добавлении записи в табл user, пароль преобразовывать в хеш md5(), также имя и отчество преобразовывать в инициалы.

Решение

mysql> DELIMITER //
mysql> CREATE TRIGGER `test_user_pass` BEFORE INSERT ON `test`.`user`
    -> FOR EACH ROW
    -> BEGIN
    ->  SET NEW.name = LEFT(NEW.name,1);
    ->  SET NEW.otch = LEFT(NEW.otch,1);
    ->  SET NEW.pass = md5(NEW.pass);
    -> END//
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

Теперь вставляем туда запись

mysql> INSERT  INTO `user` SET `fam`='Нагайченко', `name`='Максим', `otch` = 'Валерьевич', `pass` = 'password', `login` = 'maxnag';
Query OK, 1 row affected (0.00 sec)

Что теперь в таблице

mysql> SELECT * FROM `user`;
+----+-------------+------+------+----------------------------------+--------+
| id | fam         | name | otch | pass                             | login  |
+----+-------------+------+------+----------------------------------+--------+
|  1 | Нагайченко  | M    | В    | 5f4dcc3b5aa765d61d8327deb882cf99 | maxnag |
+----+-------------+------+------+----------------------------------+--------+

1 row in set (0.00 sec)

Как видно всего несколькими строчками можно убрать целые методы, которые мы использовали при регистрации нового пользователя. Теперь еще надо создать триггер на UPDATE таблицы, с таким же телом, чтобы пользователь не смог записать полное имя, отчество и пароль не в МД5();

Создание триггера на BEFORE UPDATE

mysql> DELIMITER //
mysql> CREATE TRIGGER `test_user_pass2` BEFORE UPDATE ON `test`.`user`
    -> FOR EACH ROW
    -> BEGIN
    ->  SET NEW.name = LEFT(NEW.name,1);
    ->  SET NEW.otch = LEFT(NEW.otch,1);
    ->  SET NEW.pass = md5(NEW.pass);
    -> END//
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

Обновление записи

mysql> UPDATE `user` SET `fam`='Иванов', `name`='Иван', `otch` = 'Иванович', `pass` = 'пароль', `login` = 'ivan' WHERE id=1;
Query OK, 1 row affected (0.00 sec)

Итог

mysql> SELECT * FROM `user`;
+----+-------------+------+------+----------------------------------+--------+
| id | fam         | name | otch | pass                             | login  |
+----+-------------+------+------+----------------------------------+--------+
|  1 | Иванов      | И    | И    | e242f36f4f95f12966da8fa2efd59992 | ivan   |
+----+-------------+------+------+----------------------------------+--------+

1 row in set (0.00 sec)

Могут возникнуть вопросы, что такое NEW в теле триггера,
NEW — для доступа к новым записям
OLD — для доступа к старым записям

Напимер, если я обновил фамилию, то новое значение мне доступно через NEW.fam, а к старому OLD.fam

Изменение триггера

А вот команды по изменению триггера я не нашел и был удивлен, почитал форумы, так и есть — её просто нет ((

Alter trigger

Удаление триггера

Для удаления триггера используется, как обычно оператор DROP, пример

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

где
schema_name — название БД,
trigger_name — название триггера

Список созданных триггеров

Показать триггер можно с помощью команды

SHOW TRIGGERS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Это полная часть команды, в основном пользуются командами:

SHOW TRIGGERS [FROM db_name] [LIKE 'pattern']
или
SHOW TRIGGERS

Это полная часть команды, в основном пользуются командами:

SHOW TRIGGERS [FROM db_name] [LIKE 'pattern']
или
SHOW TRIGGERS

Вот что мы увидем, когда выполним команду

mysql> SHOW TRIGGERS\G;
*************************** 1. row ***************************
             Trigger: test_user_pass
               Event: INSERT
               Table: user
           Statement: BEGIN
                          SET NEW.name = LEFT(NEW.name,1);
                          SET NEW.otch = LEFT(NEW.otch,1);
                          SET NEW.pass = md5(NEW.pass);
                      END
              Timing: BEFORE
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.01 sec)
Хороший ассортимент выбора книг, электроники, бытовой техники, посуды, подарки, сувениры и много другое Вы можете сделать на сайте компании E5.RU. Купить фильмы на dvd в фирменной упаковке и сделать хороший подарок родным и близким, Вы можете там же.

Статья просмотренна 95527 раз, зашло посетителей 44544

События (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)

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

Ссылки

Официальное руководство

Статья просмотренна 48582 раз, зашло посетителей 17134

 Newer Entries » 

Облако тегов

cli csv dump events form Kohana locale models MySQL mysqldump orm PHP tools trigger validate газ газовый счетчик итоги кеширование переменные

Облако тегов плагина WP Cumulus для WordPress требует для просмотра Flash Player 9 или выше.

Я на твиттере!

  • у твиттера тоже бывают перерывы...

Календарь

Апрель 2018
Пн Вт Ср Чт Пт Сб Вс
« Июл    
 1
2345678
9101112131415
16171819202122
23242526272829
30  

Сейчас на сайте