MySQLのトリガーを使用して特定のテーブルの操作を行った際に別テーブルにログを残す方法
2024/03/13
MySQLにトリガーという機能があるらしくどのようなものか調べたところ、事前に「指定したテーブルでINSERT / UPDATE / DELETEが行われたら〇〇というSQL文を実行する」というものらしく、管理画面等の操作ログなんかに使えそう。以下に導入方法をメモ。
リファレンス
https://dev.mysql.com/doc/refman/8.0/ja/triggers.html
やりたいこと
test_tableにデータがINSERTされたらlog_tableに「INSERT」と「実行日時」を保存したい。各テーブルは以下のような設計。
mysql> SHOW COLUMNS FROM `test_table`; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | score | int | NO | | NULL | | | regist_ymd | datetime | NO | | NULL | | +------------+--------------+------+-----+---------+----------------+ mysql> SHOW COLUMNS FROM `log_table`; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | log | varchar(255) | NO | | NULL | | | regist_ymd | datetime | NO | | NULL | | +------------+--------------+------+-----+---------+----------------+
トリガーについて
基本構文
CREATE TRIGGER 【トリガー名】 { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON 【トリガーが発火するテーブル】 FOR EACH ROW 【発火内容】;
トリガーの追加
CREATE TRIGGER insert_trigget AFTER INSERT ON `test_table` FOR EACH ROW INSERT INTO `log_table` ( `log`, `regist_ymd` ) VALUES ( 'INSERT', now() );
今回はINSERTのみ設定したがUPDATE / DELETEもそれぞれ別で登録可。
トリガーの確認
SHOW TRIGGERS;
トリガーの実行
INSERT INTO `test_table` (`name`, `score`, `regist_ymd`) VALUES ("鈴木", 80, now()); mysql> SELECT * FROM `test_table`; +----+--------+-------+---------------------+ | id | name | score | regist_ymd | +----+--------+-------+---------------------+ | 1 | 鈴木 | 80 | 2024-02-15 18:13:49 | +----+--------+-------+---------------------+ mysql> SELECT * FROM `log_table`; +----+--------+---------------------+ | id | log | regist_ymd | +----+--------+---------------------+ | 1 | INSERT | 2024-02-15 18:13:49 | +----+--------+---------------------+
test_tableにINSERTすることでlog_tableへもデータが登録された点が確認できる。
トリガーの削除
DROP TRIGGER insert_trigget;
その他
Adminerを利用している場合以下から設定できるみたい。
時間(BEFORE / AFTER)やイベント(INSERT / UPDATE / DELETE)をGUIで選択でき、テキストエリアには以下の発火内容を入力すると登録できる。
INSERT INTO `log_table` ( `log`, `regist_ymd` ) VALUES ( 'INSERT', now() );
関連記事
-
-
MySQLで全角半角を区別せずに検索する方法(collate utf8_unicode_ci)
あるシステムでMySQLの検索時に「WHERE test_column coll ...
-
-
MySQLでphpMyAdminやAdminer上だけ文字化けする場合の対応方法
レンタルサーバーのプログラムでWebページ上では文字化けしていないのに、phpM ...
-
-
MySQLでIPアドレスを数値として入れると変な値になる場合の対処方法
MySQLでIPアドレスをINET_ATON関数を用いてINT型に変換し格納する ...
-
-
SortableJSを使ってデータの並べ替え&保存
あるシステムを拝見した際にtableタグ内に並んでいる項目をドラッグ&ド ...
-
-
MySQLでデータベース単位とテーブル単位のデータサイズ(容量)の調べ方
VPSの各種ファイルを整理している際に「MySQLも不要なデータを削除すればいい ...