mysqlのトリガーを使用して他テーブルにデータを作成/更新/削除する

2023-03-19T17:51:38+09:00

前提

mysqlのテーブル定義を変えたいけど影響が大きくすぐには難しいという課題がありました。
そのため別テーブルを作成しておいてデータは自動で同期しておきたい、というときにトリガーを使用する方法を見つけたので検証します。
AWSのRDSを使用しているので、mysqlのバージョンは5.7を使用します。

環境

macOS: 12.6
mysql: 5.7

準備

docker-compose.ymlでmysqlを準備

以下のようにdocker-compose.ymlを作成してmysqlを用意します。

docker-compose.yml

version: '3.8'

services:
  db:
    image: mysql:5.7
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: sample_db
      MYSQL_USER: user
      MYSQL_PASSWORD: password
    ports:
      - 3306:3306

データベースに接続

私はdbeaverというツールを用いてデータベースに接続しました。

こちらの情報で接続することができます。

テーブルを作成

データコピー元のテーブルorigin_tableとコピー先のテーブルcopied_tableを作成します。
origin_table.num_dataカラムに入る数字をcopied_table.varchar_dataカラムへコピーしますが、そのとき数字を文字列に変換するようにトリガーで設定します。

（IDのみ大文字になってしまってました…。）

origin_table

CREATE TABLE sample_db.origin_table (
	ID BIGINT auto_increment NOT NULL COMMENT 'ID',
	num_data INTEGER DEFAULT 0 NOT NULL,
	create_user varchar(100) NOT NULL,
	create_datetime DATETIME NOT NULL,
	update_user varchar(100) NOT NULL,
	update_datetime DATETIME NOT NULL,
	CONSTRAINT origin_table_PK PRIMARY KEY (ID)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

copied_table

CREATE TABLE sample_db.copied_table (
	ID bigint(20) auto_increment NOT NULL COMMENT 'ID',
	varchar_data varchar(100) NOT NULL,
	create_user varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
	create_datetime datetime NOT NULL,
	update_user varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
	update_datetime datetime NOT NULL,
	CONSTRAINT `PRIMARY` PRIMARY KEY (ID)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

トリガーの作成

データ作成時に実行されるトリガー

origin_table.num_dataをcopied_table.varchar_dataにコピーする際、IF文で分岐して一度変数に格納するようにしました。これはプログラム内のenumのようなものでデータを入れる想定なので、その通り分岐するようにしています。
NEWはレコードへのSQL文を実行した後の値を指しています。NEW.IDはデータ登録後にできたレコードのIDを指します。

origin_table_insert_trigger

DROP TRIGGER IF EXISTS sample_db.origin_table_insert_trigger;
DELIMITER $$
$$
CREATE TRIGGER origin_table_insert_trigger AFTER INSERT ON origin_table FOR EACH ROW
BEGIN 
	DECLARE varchar_data_tmp varchar(100);
	IF NEW.`num_data`=1 THEN
		SET varchar_data_tmp='ONE' ;
	ELSEIF NEW.`num_data`=2 THEN
		SET varchar_data_tmp='TWO' ;
	ELSE
		SET varchar_data_tmp='OTHER' ;
	END IF;
	
	INSERT INTO copied_table SET
		`ID`=NEW.`ID`,
		`varchar_data`=varchar_data_tmp,
		`create_user`=NEW.`create_user`,
		`create_datetime` =NEW.`create_datetime`,
		`update_user` =NEW.`update_user`,
		`update_datetime`=NEW.`update_datetime`;
END$$
DELIMITER ;

ためしにデータを登録すると以下の通りデータがコピーされます。データの変換もできています。

データ更新時に実行されるトリガー

INSERT文とは異なり、WHERE句でIDを指定しています。

origin_table_update_trigger

DROP TRIGGER IF EXISTS sample_db.origin_table_update_trigger;
DELIMITER $$
$$
CREATE TRIGGER origin_table_update_trigger AFTER UPDATE ON origin_table FOR EACH ROW
BEGIN 
	DECLARE varchar_data_tmp varchar(100);
	IF NEW.`num_data`=1 THEN
		SET varchar_data_tmp='ONE' ;
	ELSEIF NEW.`num_data`=2 THEN
		SET varchar_data_tmp='TWO' ;
	ELSE
		SET varchar_data_tmp='OTHER' ;
	END IF;
	
	UPDATE copied_table SET
		`varchar_data`=varchar_data_tmp,
		`create_user`=NEW.`create_user`,
		`create_datetime` =NEW.`create_datetime`,
		`update_user` =NEW.`update_user`,
		`update_datetime`=NEW.`update_datetime`
	WHERE `ID` =NEW.`ID`;
END$$
DELIMITER ;

ID以外のデータを更新してみます。こちらもデータの変換がしっかりできています。

データ削除時に実行されるトリガー

注意点として、DELETE時に実行されるトリガーはNEWを使用できないことです。NEWはレコードに変化があった後の値なので、削除実行後は存在しなくなるためです。

origin_table_delete_trigger

DROP TRIGGER IF EXISTS sample_db.origin_table_delete_trigger;
DELIMITER $$
$$
CREATE TRIGGER origin_table_delete_trigger AFTER DELETE ON origin_table FOR EACH ROW
BEGIN 	
	DELETE FROM copied_table WHERE `ID`=OLD.`ID`;
END$$
DELIMITER ;

削除してみると該当のデータが消えていることがわかります。

さいごに

データを変換する処理のところは少し骨が折れましたが、これでうまく動作しました。
トリガー定義を資源管理することや、ログなどを適切に管理することができれば、SQL文実行前後で何か処理をしたい場合の有効な方法の1つとなり得ます。ぜひ使っていきたいと思います。

自己紹介

y5347M

バックエンドエンジニアをしています。