MySQLのトリガーとイベントスケジューラー

概要

MySQLのトリガーとイベントスケジューラーを触った際のメモ

トリガー

• テーブルに対して行操作（INSERT/UPDATE/DELETE）が行われた際に、行われる処理を定義できる
• トリガーの起点となる処理（トリガーイベント）の前・後のどちらで処理を行うか設定することが可能
• トリガー内でSQLの実行、ストアドプロシージャ、ストアドファンクションの実行も可能
• 同一のアクションおよびイベントに対して、複数のトリガーを設定することも可能

トリガーとは、テーブルに関連付けられ、そのテーブルに対して特定のイベントが発生するとアクティブ化される名前付きデータベースオブジェクトのことです。 トリガーを使用する場合には、テーブルに挿入する値のチェックを実行したり、更新にかかわる値の計算を実行したりする場合があります。

• https://dev.mysql.com/doc/refman/8.0/ja/triggers.html

トリガー作成の事前準備

• トリガーを試すためのデータベースとテーブルをあらかじめ作成しておく

/* dbおよび、テーブルの作成 */
mysql> create database sample character set utf8mb4;
mysql> create table users (id int auto_increment primary key, number int, index(number));
mysql> create table items (id int auto_increment primary key, number int, index(number));

作成

• トリガーの構文

  • https://dev.mysql.com/doc/refman/8.0/ja/trigger-syntax.html

• トリガーアクションタイム（トリガー発生のタイミング）

  • BEFORE
  • AFTER

• トリガーイベント（トリガー発生きっかけとなる、テーブルに対する操作

  • INSERT
  • DELETE
  • UPDATE

• usersテーブルに対して insert処理が行われる前に、新たに挿入されるレコードのnumber の数を合計する処理

mysql> delimiter //
mysql> create trigger create_items before insert on users
    -> for each row
    -> begin
    ->   insert into items values(null, new.number); /* new は新たに insert されるレコードを示す */
    -> end
    -> //
mysql> delimiter ;

• 作成したトリガーの確認

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: create_items
               Event: INSERT
               Table: users
           Statement: begin
insert into items values(new.id, new.number); 
end
              Timing: BEFORE
             Created: 2021-08-13 22:02:16.64
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

トリガーの起動

• usersテーブルに対して insert の処理を行うことで、トリガーが起動し、itemsテーブルにレコードが insert される

mysql> insert into users values(null, 1);
mysql> select * from items;
+----+--------+
| id | number |
+----+--------+
|  1 |      1 |
+----+--------+
1 row in set (0.00 sec)

トリガーとトランザクション

トリガーは、START TRANSACTION、COMMIT、ROLLBACK など、トランザクションを明示的または暗黙的に開始したり終了したりするステートメントを使用できません。 (ROLLBACK to SAVEPOINT はトランザクションを終了しないため、許可されます。)

• https://dev.mysql.com/doc/refman/8.0/ja/trigger-syntax.html

トリガの挙動

• BEFOREトリガー

BEFORE トリガーが失敗した場合、対応する行に対する操作は実行されません。

BEFORE トリガーは、行を挿入または変更しようとする試行 によってアクティブ化され、その試行がその後成功するかどうかには関係ありません。

• AFTERトリガー

AFTER トリガーは、すべての BEFORE トリガーと行操作の実行が成功した場合にのみ実行されます。

BEFORE または AFTER トリガーのどちらかの実行中にエラーが発生すると、トリガーの呼び出しを起こしたステートメント全体が失敗します。

トリガーの制約

• https://dev.mysql.com/doc/refman/8.0/ja/stored-program-restrictions.html

トランザクションテーブルの場合、ステートメントの失敗により、ステートメントが実行したすべての変更がロールバックされます。 トリガーの失敗はステートメントの失敗を招くので、トリガーの失敗はロールバックも引き起こします。 非トランザクションテーブルの場合、このようなロールバックは行えないので、ステートメントが失敗しても、エラーの時点以前に実行されたすべて変更は有効なままです。

イベントスケジューラー

• スケジュールに従って実行するタスク。unixでいうcronをMySQLで設定できる
• 時間トリガーとも呼ばれるが、厳密にはトリガーとは異なるらしい
  • https://dev.mysql.com/doc/refman/8.0/ja/event-scheduler.html

MySQL イベントスケジューラは、イベント、つまりスケジュールに従って実行するタスクのスケジュール設定および実行を管理します。 次の説明では、イベントスケジューラを取り上げ、次のセクションに分かれています。

作成

• https://dev.mysql.com/doc/refman/8.0/ja/events-syntax.html

mysql> delimiter //
mysql> create event create_sample_data 
    -> on scheduel
    ->  every 10 second
    -> do
    -> begin
    ->   insert into sample.users values (null, 1);
    -> end
    -> //
mysql> delimiter ;

• 作成したイベントの確認

mysql> show events \G
*************************** 1. row ***************************
                  Db: sample
                Name: create_sample_data
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: SECOND
              Starts: 2021-08-13 21:30:35
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

停止方法

• イベントスケジューラー全体を停止する

show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

set GLOBAL event_scheduler=OFF;

show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF    |
+-----------------+-------+
1 row in set (0.00 sec)

• 特定の イベントのみを無効化する

mysql> alter event create_test_data DISABLE;
mysql> show events \G
*************************** 1. row ***************************
                  Db: sample
                Name: create_sample_data
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 10
      Interval field: SECOND
              Starts: 2021-08-13 21:30:35
                Ends: NULL
              Status: DISABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)