この記事はスタンバイ Advent Calendar 2022の23日目の記事です。
最近、MySQLデータの更新にtrigger機能を検証したので、一般的にはNGとされている機能が実現できたことを記載します
TL;DR
- MySQLのtriggerで自動でデータ更新を行うことができる
- 公式にはできないとされている、trigger内で同一テーブルデータ更新を実施できて、安全であることが理解できる
MySQL triggerとは
-
Tableに対しての行操作（INSERT/UPDATE/DELETE）が行われた際に実行する操作を定義できる
-
triggerの起点となる処理(trigger event)の、前・後のどちらかで処理を行うことが出来る
-
trigger内では、SQL、procedure, functionを実行することが出来る
-
更新前後のカラムデータを取り扱うことができ、
OLD.[カラム名]（更新前データ）、NEW.[カラム名]（更新後データ）として表現をする -
一部制御構文（IF、CASE、FOR等）、DBイベントを操作することが出来る
triggerの使用目的は、テーブル内、テーブル間のデータ整合性を担保するために用いられるものです。処理ロジックがアプリケーションに残らないので嫌がる人も多いですが、データ観点で整合性を担保することを目的とする場合は良い手段であります。
MｙSQL triggerで出来ないこと
- ドキュメントによると、trigger内で同一テーブルの更新を行うことが出来ない。
ストアドファンクションまたはトリガーは、そのストアドファンクションまたはトリガーを呼び出したステートメントによって (読み取りまたは書き込みに) すでに使用されているテーブルを変更できません。
- 正しくは、「同一テーブルに更新クエリを発行することが出来ない」であり、本稿ではtrigger内で同一テーブルのデータを更新する方法を記載する
MySQL Triggerの作成方法
本記事のメインでもある「trigger内で同一テーブルの更新を行う」ことが実現できるテーブル構成で例示をします。
Trigger構文の詳細は、[create trigger構文]を参照してください。
構成テーブル
| テーブル名 | 役割｜ |
|---|---|
| master_star_name | 正しい星の名前が管理されているテーブル |
| start_neme | 星の名前を入力するテーブル |
| star_modify_history | 入力値が間違っている場合に修正した履歴 |
/* 星座マスタ表 */
CREATE TABLE `master_star_name` (
`seiza` varchar(64) NOT NULL,
`rank` varchar(64) NOT NULL,
`star_name` varchar(64) NOT NULL,
PRIMARY KEY (`seiza`,`rank`))
ENGINE = InnoDB;
insert into master_star_name values ('オリオン座','1','ベテルギウス');
insert into master_star_name values ('オリオン座','2','リゲル');
insert into master_star_name values ('おおいぬ座','1','シリウス');
insert into master_star_name values ('こいぬ座','1','プロキオン');
/* 星座入力表 */
CREATE TABLE `star_name` (
`seiza` varchar(64) NOT NULL,
`rank` varchar(64) NOT NULL,
`star_name` varchar(64) NOT NULL,
PRIMARY KEY (`seiza`,`rank`))
ENGINE = InnoDB;
/* 星座名修正履歴表 */
CREATE TABLE `star_modify_history` (
`seiza` varchar(64) NOT NULL,
`rank` varchar(64) NOT NULL,
`original_star_name` varchar(64) NOT NULL,
`fixed_star_name` varchar(64) NOT NULL,
`created_at` datetime(3) NOT NULL)
PRIMARY KEY (`seiza`,`rank`,`created_at`))
ENGINE = InnoDB;
作成trigger
DELIMITER $$
DROP TRIGGER IF EXISTS `tr_insert_star_name`$$
CREATE TRIGGER `tr_insert_star_name` BEFORE INSERT ON `star_name` FOR EACH ROW
BEGIN
SELECT star_name INTO @star_name
FROM master_star_name
WHERE seiza = NEW.seiza AND rank = NEW.rank;
IF NEW.star_name != @star_name THEN
-- 履歴表（他のテーブルに処理をする）
INSERT INTO star_modify_history VALUES (NEW.seiza, NEW.rank, NEW.star_name, @star_name, now());
-- 入力するstar_nameを変更する（execute前なので、値を変更できる！！）
SET NEW.star_name = @star_name;
END IF;
END$$
DELIMITER ;
- triggerの概要は「行処理実行前に、更新データを書き換える」
-
BEFOREの指定が重要。INSERT処理実行前に処理を行うための宣言（一般的にはAFTERを使用するが同一テーブル処理では必ずBEFORE） -
SELECT star_name INTO @star_namemaster表から正しいstar_nameを取得して@star_nameに保管 -
SET NEW.star_name = @star_nameこれが今回の肝。更新後のデータNEW.star_nameを書き換えている - trigger処理はこのまま完了する。直後実行される行処理で書き換わった
NEW.star_nameが適用される - 必要な処理だけが行われるように、適時IF分で制御を入れる。今回はstar_nameが正しければ処理しない
実行してみる
- 間違ったデータを入力する
mysql> insert into star_name values ('オリオン座','1','リゲル');
Query OK, 1 row affected (0.01 sec)
- 結果確認
mysql> select * from star_name;
+-----------------+------+--------------------+
| seiza | rank | star_name |
+-----------------+------+--------------------+
| オリオン座 | 1 | ベテルギウス |
+-----------------+------+--------------------+
1 row in set (0.01 sec)
mysql> select * from star_modify_history;
+-----------------+------+--------------------+--------------------+-------------------------+
| seiza | rank | original_star_name | fixed_star_name | created_at |
+-----------------+------+--------------------+--------------------+-------------------------+
| オリオン座 | 1 | リゲル | ベテルギウス | 2022-12-17 01:21:45.000 |
+-----------------+------+--------------------+--------------------+-------------------------+
1 row in set (0.01 sec)
- 間違った情報を入力しても、マスタにある正しいデータに置き換わっており、履歴表にも登録されている
その他事項
- トランザクションを使用した場合は、行処理、trigger処理がまとめて１トランザクションの扱いになる。
rollbackするとtrigger処理も合わせて戻すことが出来る - 逆にtrigger処理がエラーになると、行処理も合わせてエラー扱いになるので注意が必要
感想
- DB更新クエリの内容を、実行直前に置き換えられるのはかなり驚きである
- データ修正の仕組みを低レイヤーで簡単に実装出来るので、かなり有用な方法である
- ログ等の仕組みが出しにくいので、アプリケーション処理との融合は難しい
- 結局、使い所次第ではある