2018/06/05

MySQL TriggerのNEWとOLDを使いこなす

MySQL 




Trigger(トリガー)とは

MySQLには Trigger トリガー という機能があり、 レコードが追加・更新・削除されたタイミングで別のSQLを発行することができます


別のSQLとは、もう少し詳しく言うとストアドプロシージャ。

JavaやPHPなどのプログラミング言語を用いずとも、 条件分岐や繰り返しといったプログラムを書くことができます


トリガーの構文(MySQL)

CREATE
  [DEFINER = { user | CURRENT_USER }]
  TRIGGER trigger_name
  trigger_time trigger_event
  ON tbl_name FOR EACH ROW
  trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }


詳しくは MySQL 13.1.19 CREATE TRIGGER 構文を参照してください。


トリガーの使い道

トリガーは、データが変更されたときを検知して処理を実行するので、いろいろと使い道はありそうですよね。

私の考えるものは次の通りです。

  • ログ
  • アプリケーションロジック
  • データ移行
  • 集計

ちなみに、私個人的にはトリガーはおすすめしません。

※タイトルに「使いこなす」とか書いちゃってますが、正直使いたくはないです(笑)

デメリットが大きくて、システム開発には向いていないと思っているのです。

もしよければこちらの記事も参考にしてみてください。

『MySQL Triggerのデメリットが大きいので使わないで欲しい』


まずは、トリガーの使い方やNEWとOLDの説明をします。


トリガー特有のNEWとOLD

トリガーには、普通のSQLにはない NEWOLDという構文があります。


トリガー自体、アプリケーションロジック担当者だとなかなか見ないですよね。たぶん。

まぁ、私が無知なだけな気もしますので、NEWとOLDについてまとめてみます。


NEWはトリガー実行時にこれから入れる値を扱う

NEWという単語を読んで字のごとく、INSERTやUPDATEしたときの、新しい値を扱う一時テーブル(?)のことです。

※一時テーブル?か何かはDBAが詳しく説明してくれるはず。。


NEW.カラム名という書き方で値を参照できます。


新しい値を扱いますので、当然ながらDELETEトリガーでは使えません。

トリガー定義するときにエラーになります。


OLDはトリガー実行時に前の値を扱う

こちらもOLDという単語を読んで字のごとく、UPDATEやDELETEをしたときの、古い値を扱う一時テーブル(?)です。


OLD.カラム名という書き方で値を参照できます。


古い値を扱いますので、当然ながらINSERTトリガーでは使えません。

こちらもトリガー定義するときにエラーになります。


NEWとOLDの意味まとめ

構文 意味 INSERT UPDATE DELETE
NEW トリガー実行時の新しい値 ○ 使える ○ 使える × 使えない
OLD トリガー実行時の古い値 × 使えない ○ 使える ○ 使える


トリガーのNEWとOLDのサンプル

簡単なサンプルでトリガーの動作を確認してみましょう。

次の流れで進めていきます。

  1. テーブル作成
  2. INSERTトリガー作成
  3. UPDATEトリガー作成
  4. DELETEトリガー作成
  5. データ投入してテスト

テーブル作成

まずは、二つのテーブルを作成してみます。

トリガーを設定するテーブルと、そのトリガーから操作するためのテーブルです。


用意するテーブル

  • ユーザテーブル
  • 履歴テーブル

ユーザテーブルにレコードが作られたりしたタイミングで、履歴テーブルにも書き込むイメージですね。


-- DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` ( 
  `user_id` int (11) NOT NULL COMMENT 'ユーザID'
  , `name` varchar (4) NOT NULL COMMENT '名前'
  , PRIMARY KEY (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = 'ユーザ'; 

-- DROP TABLE IF EXISTS `user_log`;

CREATE TABLE `user_log` ( 
  `id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'ID'
  , `user_id` int (11) NOT NULL COMMENT 'ユーザID'
  , `old_name` varchar (4) DEFAULT NULL COMMENT '名前(変更前)'
  , `new_name` varchar (4) DEFAULT NULL COMMENT '名前(変更後)'
  , PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = 'ユーザ履歴'; 


INSERTトリガー作成

次に、INSERTされたときに実行されるトリガーを作成してみます。

ここでNEWを使ってみましょう。

OLDは使えませんので注意してください。


-- DROP TRIGGER IF EXISTS `trigger_user_log_insert`;
DELIMITER //
CREATE TRIGGER `trigger_user_log_insert` AFTER INSERT ON `user`
  FOR EACH ROW
  BEGIN
    INSERT INTO `user_log`
  SET
    `user_id` = NEW.`user_id`
    , `new_name` = NEW.`name`;
  END;
//
DELIMITER; 


UPDATEトリガー作成

UPDATEされたときに実行されるトリガーも作成してみます。

NEWとOLDの両方が使えますね。


-- DROP TRIGGER IF EXISTS `trigger_user_log_update`;
DELIMITER //
CREATE TRIGGER `trigger_user_log_update` AFTER UPDATE ON `user`
  FOR EACH ROW
  BEGIN
    INSERT INTO `user_log`
  SET
    `user_id` = NEW.`user_id`
    , `old_name` = OLD.`name`
    , `new_name` = NEW.`name`;
  END; 
//
DELIMITER; 


DELETEトリガー作成

DELETEされたときに実行されるトリガーも作成してみます。

NEWは使えず、OLDのみ使うことができます。


-- DROP TRIGGER IF EXISTS `trigger_user_log_delete`;
DELIMITER //
CREATE TRIGGER `trigger_user_log_delete` AFTER DELETE ON `user`
  FOR EACH ROW
  BEGIN
    INSERT INTO `user_log`
  SET
    `user_id` = OLD.`user_id`
    , `old_name` = OLD.`name`;
  END; 
//
DELIMITER; 


ちなみに、業務システムではほとんどの場合でDELETEは使いません。

間違ってデータを削除してしまったときに、戻せないと困るからです。

削除フラグのようなものを用意して、論理削除するのが一般的です。


他にも、運用を続けているとログなどのデータ容量が肥大化してしまい、削除しないといけないケースも出てきます。

しかしその際はTRUNCATEやDROP PARTITIONなど高速で削除することを検討しますし、トリガーで検知する何かでもないような気がします。

仮に検知対象だとしても、性能面を考慮して、トリガーではない何かを使う気もします。


話が逸れました......。


データ投入してテスト

テーブルとトリガーを作成できたので、最後にデータを投入してテストをしてみましょう。


-- TRUNCATE `user`;
-- TRUNCATE `user_log`;

INSERT INTO `user` SET `user_id` = 1, `name` = 'one';
INSERT INTO `user` SET `user_id` = 2, `name` = 'two';
UPDATE `user` SET `name` = 'i' WHERE `user_id` = 1;
DELETE FROM `user` WHERE `user_id` = 1;


INSERTが2レコード分、UPDATEが1レコード分、DELETEが1レコード分、履歴テーブルに書き込まれる想定です。


いざ実行!


mysql> select * from user;
+---------+------+
| user_id | name |
+---------+------+
|       2 | two  |
+---------+------+
1 row in set (0.00 sec)

mysql> select * from user_log;
+----+---------+----------+----------+
| id | user_id | old_name | new_name |
+----+---------+----------+----------+
|  1 |       1 | NULL     | one      |
|  2 |       2 | NULL     | two      |
|  3 |       1 | one      | i        |
|  4 |       1 | i        | NULL     |
+----+---------+----------+----------+
4 rows in set (0.00 sec)


NEWとOLDの値が、それぞれ履歴テーブルに書き込まれていることが確認できましたね。


これで、Hello Worldは完了です。




このエントリーをはてなブックマークに追加