MySQLでトリガ使って別テーブルに自動でコピる

はい、おつカレーさまさまです。桑野です。
ブログを更新できなくて落ち込んだりもするけれど私は元気です。

もんごさんとかもんご野郎とかよく言われる昨今ですが最近久しぶりにMySQLを触っているのでその話を。
そーいえばMySQLのトリガ設定とかほぼやったことなかったので、メモ書き。

トリガとは

トリガというのは、特定のテーブルにある操作（UPDATE,INSERT,DELETE)が行われた時にイベント駆動的に実行される機構のことですね。

トリガ作成

CREATE TRIGGER で作成されます。

トリガの設定

下準備

GRANT でトリガを設定するユーザにTRIGGER権限を与えておきましょう。

GRANT TRIGGER ON `user`.* TO 'dbuser'@'192.168.0.%' IDENTIFIED BY 'dbpass';

元テーブル定義

mysql> CREATE TABLE `usermaster` (
	  `user_name` varchar(255) NOT NULL,
	  `user_id` int(10) NOT NULL,
	  `type` int(10) DEFAULT NULL,
	  `status` int(10) DEFAULT NULL,
	  `upd_datetime` datetime NOT NULL,
	  PRIMARY KEY (`user_id`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 

複製先テーブル作成

同じテーブル定義のテーブルを作成します。

mysql> CREATE TABLE usermaster_cs LIKE usermaster;

TRIGGER設定

注意

• 変更前の行のデータはOLDに、更新、挿入される新しいデータはNEWで表現される
• TRIGGERは [AFTER / BEFORE] * [INSERT/UPDATE/DELETE]の条件で1つしか設定できない
• もし複数SQL実行したいならBEGIN〜ENDでまとめよう

TRIGGER作成[INSERT]

AFTER / BEFORE で、イベント実行後、前にTRIGGERを実行するか設定できます。
これはINSERT用。適当に書いちゃったけど良い書き方ないかなぁ。

mysql> 	DELIMITER |
mysql> 	CREATE TRIGGER userm_insert AFTER INSERT
	 ON usermaster FOR EACH ROW
	 BEGIN
	 INSERT INTO usermaster_cs SET 
	   `user_name`=NEW.`user_name`, 
	   `user_id`=NEW.`user_id`, 
	   `type`=NEW.`type`, 
	   `status`=NEW.`status`, 
	   `upd_datetime`=NEW.`upd_datetime`;
	 END;
	|
mysql> 	DELIMITER ;

TRIGGER作成[UPDATE]

こっちはUPDATE用、大して変わらん。

mysql> DELIMITER |
mysql> CREATE TRIGGER userm_update AFTER UPDATE
	 ON usermaster FOR EACH ROW
	 BEGIN
	 UPDATE usermaster_cs SET 
	   `user_name`=NEW.`user_name`, 
	   `user_id`=NEW.`user_id`, 
	   `type`=NEW.`type`, 
	   `status`=NEW.`status`, 
	   `upd_datetime`=NEW.`upd_datetime`
	   WHERE `user_id`=OLD.`user_id`;
	 END;
	|	
mysql> DELIMITER ;

テスト実行

INSERT確認

ますたーでINSERT

mysql> INSERT INTO usermaster SET 
	   user_name="kuwano", 
	   user_id=1000000, 
	   type=7, 
	   status=1, 
	   upd_datetime="2014-01-17 00:00:00";

すれーぶで確認

はいっとるはいっとる。

mysql> SELECT * FROM usermaster_cs WHERE user_name="kuwano";

UPDATE確認

ますたーでUPDATE

mysql> UPDATE usermaster SET 
	   user_name="mongo", 
	 WHERE user_id=1000000;

すれーぶで確認

かわっとるかわっとる。

mysql> SELECT * FROM usermaster_cs WHERE user_name="kuwano";
mysql> SELECT * FROM usermaster_cs WHERE user_name="mongo";

運用系

トリガの削除

DROP TRIGGERで行うます。
IF EXISTSでuserm_insertトリガがあるなら削除です。

mysql> DROP TRIGGER IF EXISTS userm_insert ;

トリガの設定確認

トリガの設定確認がしたい場合はSHOW TRIGGERSで。

mysql> SHOW TRIGGERS;
(snip)

| userm_insert | INSERT | usermaster | 
 BEGIN
 UPDATE usermaster_cs SET 
   `user_name`=NEW.`user_name`, 
   `user_id`=NEW.`user_id`, 
   `type`=NEW.`type`, 
   `status`=NEW.`status`, 
   `upd_datetime`=NEW.`upd_datetime`
 END                                                                                   | AFTER  | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | utf8_general_ci    |

| userm_update | UPDATE | usermaster | BEGIN
 UPDATE usermaster_cs SET
   `user_name`=NEW.`user_name`, 
   `user_id`=NEW.`user_id`, 
   `type`=NEW.`type`, 
   `status`=NEW.`status`, 
   `upd_datetime`=NEW.`upd_datetime`
   WHERE `user_id`=OLD.`user_id`;
 END | AFTER  | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | utf8_general_ci    |

余談

実行がそもそも走ってるかどうかよくわからん時はテスト時だけはgeneral_logとか有効にしてみるといいよ。

my.cnfで以下を有効に。

general_log = On
general_log_file=general.log

もしくは、SET GLOBALで。

SET GLOBAL general_log = 'ON';

ではでは三(卍^o^)卍ﾄﾞｩﾙﾙﾙﾙ

実践ハイパフォーマンスMySQL 第3版

JMT 5個 クロックDフリップフロッDIP-20トリガによる元のキー74LS273