LoginSignup
97
95

More than 5 years have passed since last update.

@suin(suin@TypeScriptが好き)inCraftsman Software

トリガーの開発方法＆デバッグ方法

Posted at

MySQLのトリガー便利です。PHPでトランザクションを張って同期…というのもありだけど、データベースのレイヤで関係が担保されているのは開発者にとっても安心です。

しかし、トリガーの開発方法についての情報は少ない感じがあります。手探りで自己流なところがあると思いますが、トリガーの開発方法を整理できるくらいに自分の中でルーティンが決まってきたので記事としてまとめておきます。

掲示板のデータベースを例に作ってみよう

お題がないと説明しにくいので、掲示板アプリを例に話を進めます。YY-BBSのような単純な掲示板を思い浮かべてください。
2つのテーブルを用意します。

  • スレッドテーブル
  • 投稿テーブル

もう少しテーブルの関係がイメージしやすいようにもうちょい妄想しちゃいます。

スレッドテーブルと投稿テーブルは一対多の関係にあります。一つのスレッドに複数の投稿が記録できる仕様です。掲示板にはスレッド一覧のページがあります。そこにスレッドに対しての投稿数N件を表示しています。投稿数N件は、現仕様で SELECT COUNT(*) FROM 投稿テーブル WHERE スレID = ? といった具合に、毎回計算して表示するようになっています。

また、一覧ページにはソート機能があって、投稿数の多い少ないでソートできる仕様です。このときもさっきの SELECT文 と JOIN で頑張っています。

さて、掲示板の投稿が増えてきて、若干重いなーと感じてきたので、投稿数N件は予め計算した値をスレッドテーブルに保存するように仕様変更したいと思います。

そんな掲示板のテーブルです。

新規投稿があるときに、PHPで SELECT COUNT(*) するのもありです。洗い替えするのはそのタイミングだけではありません。編集のときは？削除のときは？投稿のスレッド間移動のときは？管理者による投稿の一括削除のときは？などなど、いろいろ出てきそうです。こうなってくるとPHPに手を入れるのが嫌になります。そこでトリガーの出番です！

テーブルを作る

とりあえず、叩き台のテーブルを作ります。

-- スレッドテーブル
CREATE TABLE `bbs_thread` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `post_total` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `bbs_thread` (`id`, `title`, `post_total`)
VALUES
     (1, '【便利】トリガーを試してみるスレ【使いたい】', 0);

-- 投稿テーブル
CREATE TABLE `bbs_post` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) unsigned NOT NULL,
  `username` varchar(100) NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `bbs_post` (`id`, `thread_id`, `username`, `body`)
VALUES
     (1, 1, '名無し', 'MySQLのトリガーってどうよ？'),
     (2, 1, '名無し２', 'ずさー'),
     (3, 1, '名無し３', '3げと');

トリガー開発の手順

トリガーの開発手順は次のように行います。

  1. デバッグ環境の準備
    1. ダンプテーブルの作成
    2. ダンプ関数の作成
  2. ストアドプロシージャの作成
  3. トリガーの作成
  4. トリガーの単体テスト

1. デバッグ環境の準備

PHPであれば var_dump() でデバッグすることができます。MySQLではこのような関数がないようなので、自前で作る必要があります。

1.1 ダンプテーブルの作成

var_dump() は画面出力という形で、変数をダンプしますが、MySQLではテーブルに出力するという形で変数を確認するようにします。
そのため、出力用のテーブルを作るのが第一ステップになります。

CREATE TABLE `dump` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` text,
  `value` text,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

1.2 ダンプ用プロシージャの作成

出力先ができたので、今度はvar_dump()のような出力関数を作ります。MySQLではストアドプロシージャで出力関数を実装するといいでしょう。

DELIMITER $$
CREATE PROCEDURE `dump`(IN $name TEXT, IN $value TEXT)
     BEGIN
          /**
           * デバッグ用プロシージャ
           * @param TEXT $name 変数名
           * @param TEXT $value 変数値
           */

          -- テーブルにダンプする
          INSERT INTO `dump` SET `name` = $name, `value` = $value, `created` = NOW();
     END$$
DELIMITER ;

試しに呼んでみよう。

mysql> CALL dump('hoge', 'ほげ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM dump;
+----+------+--------+---------------------+
| id | name | value  | created             |
+----+------+--------+---------------------+
|  1 | hoge | ほげ   | 2011-10-17 13:52:33 |
+----+------+--------+---------------------+
1 row in set (0.00 sec)

ちゃんと入っている。

2. ストアドプロシージャの作成

2.1 下準備

いきなりトリガーを実装してもいいけど、次の理由から実装はストアドプロシージャにしてトリガーはそのラッパーという位置づけがおすすめです。

  • ストアドプロシージャにしとくと再利用性が高くなる。トリガーAとトリガーBで同じ処理が使える利点があります。また、トリガーを作ってさあデプロイってときにもプロシージャが再利用できちゃう。
  • 同じイベントのトリガーは一つのテーブルに一つまで。トリガーに直ガキすると、新しいトリガーを追加するときにトリガーがモンスター化して、メンテが大変になります。ストアドプロシージャをラップする形にしておくと、見た目もスッキリ。メンテも楽。

ということで、ストアドプロシージャを実装します。

ストアドプロシージャのテンプレートはこんなかんじ。

DROP PROCEDURE IF EXISTS {プロシージャ名};

DELIMITER $$
CREATE PROCEDURE {プロシージャ名}(IN $id INT)
     main:BEGIN
          /**
           * かくかくしかじかするプロシージャ
           * @param INT $id ほげほげID
           */

          -- ここに処理を実装する
     END;
$$
DELIMITER ;

ストアドプロシージャ名は個人的に次のような名づけルールがあります。

  • 挿入系: insert_{テーブル名}_{カラム名}
  • 更新系: update_{テーブル名}_{カラム名}
  • 削除系: delete_{テーブル名}_{カラム名}

テーブル名カラム名は影響を与える先を指すようにします。呼び出しもとではありません。たとえば、掲示板のスレッドテーブル(bbs_thread)に、投稿数カラム(post_total)があるとして、投稿数カラムを更新するプロシージャ（更新系）は update_bbs_thread_post_total という名前にします。（テーブルごとに接頭辞が規約で決まっていて、カラム名がある程度ユニークな場合にはテーブル名を省くこともあります）

最初に、DROP PROCEDURE IF EXISTS があるのは、開発中になんどもプロシージャを書き換えるからです。DROP PROCEDURE IF EXISTS と CREATE PROCEDURE はセットに(1ファイル)しておくとmysqlコマンドを叩くだけでプロシージャを更新できます。

変数名が$で始まっているのは変数であるということを分かりやすく（PHPの影響もあるが）するためです。また、変数名がカラム名とかぶると、しばしばドハマリするのでカラム名に使わない文字である$を使って衝突を防ぐという意味合いがあります。

main:BEGIN の main はラベルです。
ストアドプロシージャの途中で処理を抜けたいときに LEAVE main; とすることでプロシージャの処理を終わらせることができます。これは main ラベルの処理を去る（LEAVE)という意味です。PHPの return のようなことができるわけです。
条件によってうまく LEAVE main を使えば、余計なクエリーを発行しなくていいので、リソースの節約になります。

2.2ストアドプロシージャ内で使う変数の定義

DECLARE で定義するだけです。JavaScriptの var やVBAの Dim だと思えば簡単！

          DECLARE $total INT DEFAULT 0; -- 合計

DECLARE で定義した変数はローカルスコープになります。

ここまでの作業でこんなストアドプロシージャができます。

DROP PROCEDURE IF EXISTS update_bbs_thread_post_totalupdate_bbs_thread_post_total;;

DELIMITER $$
CREATE PROCEDURE update_bbs_thread_post_total(IN $id INT)
     main:BEGIN
          /**
           * スレッドの投稿数を更新するプロシージャ
           * @param INT $id スレッドID
           */

          -- ローカル変数定義
          DECLARE $post_total INT DEFAULT 0; -- 投稿件数

     END;
$$
DELIMITER ;

2.3 変数への代入

変数（入れ物）ができたので、投稿テーブルから投稿数をカウントして代入する処理を書きます。
カウントするクエリーはこんな感じですね。

          -- 投稿件数をカウントする
          SELECT
               COUNT(*)
               FROM bbs_post
               WHERE
                    thread_id = 1
          ;

これをちょっといじります。
手始めに、thread_id はプロシージャの引数を使うようにします。

          -- 投稿件数をカウントする
          SELECT
               COUNT(*)
               FROM bbs_post
               WHERE
                    thread_id = $id
          ;

最後に、代入するように INTO 句を追加します。

          -- 投稿件数をカウントする
          SELECT
               COUNT(*) INTO $post_total
               FROM bbs_post
               WHERE
                    thread_id = $id
          ;

これで代入されるようになります。

これを先程のプロシージャに組み込むとこのような感じになります。

DROP PROCEDURE IF EXISTS update_bbs_thread_post_totalupdate_bbs_thread_post_total;;

DELIMITER $$
CREATE PROCEDURE update_bbs_thread_post_total(IN $id INT)
     main:BEGIN
          /**
           * スレッドの投稿数を更新するプロシージャ
           * @param INT $id スレッドID
           */

          -- ローカル変数定義
          DECLARE $post_total INT DEFAULT 0; -- 投稿件数

          -- 投稿件数をカウントする
          SELECT
               COUNT(*) INTO $post_total
               FROM bbs_post
               WHERE
                    thread_id = $id
          ;

     END;
$$
DELIMITER ;

2.4 代入が上手く入ってるかダンプしてみよう

代入まで実装したので、正しく代入されているかダンプしてみます。
ダンプは先ほど作った dump() プロシージャを使います。

下の2行を END の直前に追加します。

          -- ダンプしてみる
          call dump('$post_total', $post_total);

プロシージャ全体はこんな感じになります。

DROP PROCEDURE IF EXISTS update_bbs_thread_post_total;

DELIMITER $$
CREATE PROCEDURE update_bbs_thread_post_total(IN $id INT)
     main:BEGIN
          /**
           * スレッドの投稿数を更新するプロシージャ
           * @param INT $id スレッドID
           */

          -- ローカル変数定義
          DECLARE $post_total INT DEFAULT 0; -- 投稿件数

          -- 投稿件数をカウントする
          SELECT
               COUNT(*) INTO $post_total
               FROM bbs_post
               WHERE
                    thread_id = $id
          ;

          -- ダンプしてみる
          call dump('$post_total', $post_total);

     END;
$$
DELIMITER ;

では早速プロシージャを走らせてみます。
プロシージャは CALL で呼び出します。

CALL update_bbs_thread_post_total(1);

つぎに結果を確認します。

SELECT * FROM dump;

dump テーブルに期待通りの出力が入っていれば成功です。

mysql> SELECT * FROM dump;
+----+-------------+--------+---------------------+
| id | name        | value  | created             |
+----+-------------+--------+---------------------+
|  1 | hoge        | ほげ   | 2011-10-17 13:21:49 |
|  2 | $post_total | 3      | 2011-10-17 13:22:59 |
+----+-------------+--------+---------------------+
2 rows in set (0.00 sec)

ちなみに、

CALL update_bbs_thread_post_total(1);
SELECT * FROM dump;

はセットで実行すると、すぐ結果が見れるので効率UPです。

2.5 更新してみる

下の UPDATE 文を追加します。

          -- 投稿件数を反映する
          UPDATE bbs_thread
               SET post_total = $post_total
               WHERE id = $id
          ;

実行後すぐさま反映されているか確認するには、dump プロシージャの引数を SELECT 文にすればOKです。

          -- 反映を確認
          call dump('bbs_thread.post_total',
               (SELECT post_total FROM bbs_thread WHERE id = $id)
          );

プロシージャの全体像と完成形

DROP PROCEDURE IF EXISTS update_bbs_thread_post_total;

DELIMITER $$
CREATE PROCEDURE update_bbs_thread_post_total(IN $id INT)
     main:BEGIN
          /**
           * スレッドの投稿数を更新するプロシージャ
           * @param INT $id スレッドID
           */

          -- ローカル変数定義
          DECLARE $post_total INT DEFAULT 0; -- 投稿件数

          -- 投稿件数をカウントする
          SELECT
               COUNT(*) INTO $post_total
               FROM bbs_post
               WHERE
                    thread_id = $id
          ;

          -- ダンプしてみる
          call dump('$post_total', $post_total);

          -- 投稿件数を反映する
          UPDATE bbs_thread
               SET post_total = $post_total
               WHERE id = $id
          ;

          -- 反映を確認
          call dump('bbs_thread.post_total',
               (SELECT post_total FROM bbs_thread WHERE id = $id)
          );

     END;
$$
DELIMITER ;

これでひと通りの処理が実装されました。
ためしに走らせてみよう。

CALL update_bbs_thread_post_total(1);
SELECT * FROM dump;

+----+-----------------------+--------+---------------------+
| id | name                  | value  | created             |
+----+-----------------------+--------+---------------------+
|  1 | hoge                  | ほげ   | 2011-10-17 13:21:49 |
|  2 | $post_total           | 3      | 2011-10-17 13:22:59 |
|  3 | $post_total           | 3      | 2011-10-17 13:24:49 |
|  4 | bbs_thread.post_total | 3      | 2011-10-17 13:24:49 |
+----+-----------------------+--------+---------------------+
4 rows in set (0.00 sec)

実装が済んだので、デバッグ用の dump() は用済みです。すべて消してしまいます。

DROP PROCEDURE IF EXISTS update_bbs_thread_post_total;

DELIMITER $$
CREATE PROCEDURE update_bbs_thread_post_total(IN $id INT)
     main:BEGIN
          /**
           * スレッドの投稿数を更新するプロシージャ
           * @param INT $id スレッドID
           */

          -- ローカル変数定義
          DECLARE $post_total INT DEFAULT 0; -- 投稿件数

          -- 投稿件数をカウントする
          SELECT
               COUNT(*) INTO $post_total
               FROM bbs_post
               WHERE
                    thread_id = $id
          ;

          -- 投稿件数を反映する
          UPDATE bbs_thread
               SET post_total = $post_total
               WHERE id = $id
          ;

     END;
$$
DELIMITER ;

3. トリガーの作成

次は、トリガーの作成になります。トリガーの実装はシンプルです。具体的な実装はストアドプロシージャでやっているので、トリガー部分ではそれを CALL するだけです。

今回の例では、次の3つのトリガーを作ります。

DROP TRIGGER IF EXISTS tr_bbs_post_after_insert;
DROP TRIGGER IF EXISTS tr_bbs_post_after_update;
DROP TRIGGER IF EXISTS tr_bbs_post_after_delete;

DELIMITER $$

-- 投稿テーブルINSERT後
CREATE TRIGGER tr_bbs_post_after_insert
     AFTER INSERT ON bbs_post FOR EACH ROW
     BEGIN
          CALL update_bbs_thread_post_total(NEW.thread_id);
     END;
$$

-- 投稿テーブルUPDATE後
CREATE TRIGGER tr_bbs_post_after_update
     AFTER UPDATE ON bbs_post FOR EACH ROW
     BEGIN
          CALL update_bbs_thread_post_total(OLD.thread_id);
          CALL update_bbs_thread_post_total(NEW.thread_id);
     END;
$$

-- 投稿テーブルDELETE後
CREATE TRIGGER tr_bbs_post_after_delete
     AFTER DELETE ON bbs_post FOR EACH ROW
     BEGIN
          CALL update_bbs_thread_post_total(OLD.thread_id);
     END;
$$

DELIMITER ;

4. トリガーの単体テスト

トリガーはアプリケーション全体において、下位レイヤーという位置づけになります。なので、トリガーがバグっているとそれだけで大惨事になります。トリガーがバグっていないことを保証するために、テストコードつきの単体テストをおすすめします。MySQLで単体テストを行うことは難儀なので、PHPUnitを使ってテストするといいでしょう。

まとめ

MySQLのトリガー便利です。しかし、トリガーの開発方法についての情報は少ない感じがあります。そこで、この記事では次の手順でトリガーを開発する方法を紹介しました。

  1. デバッグ環境の準備
    1. ダンプテーブルの作成
    2. ダンプ関数の作成
  2. ストアドプロシージャの作成
  3. トリガーの作成
  4. トリガーの単体テスト

トリガーの単体テストでPHPUnitを紹介しましたが、PHPUnitを使った詳しい単体テストの手法は紹介できなかったので、いつか気が向いたら書こうとおもいます。

最後に、フィードバックなど歓迎します。もっといい方法、おすすめの方法があれば教えて下さい。

97
95
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
97
95