MySQLでtriggerの機能を使って履歴テーブルを作成する

はじめに

MySQLでトリガーを使って履歴テーブルを作成するということを実践してみたいので備忘録として書いておく。
大抵のWebアプリケーションの場合、アプリケーション側で履歴テーブルの制御をすることが多い。
というのも、DB側で制御の処理をさせるとシステム構成が複雑になりがちのため。
しかし、便利である機能なので一番簡単かつ実用的な履歴テーブルを作成するということを行う。

環境

1
2
3
Windows 10 Pro 
Docker Desktop 4.22.1 (118664)
MySQL 8.1.0

準備

今回、検証するにあたって、Docker を使用する。
下記のリポジトリに、データベースの勉強用リポジトリとして色々入れているので一緒に試せるようにした。
https://github.com/katsuobushiFPGA/db-study

構築

ER図

今回は以下のテーブルがベースとして存在し、reservations テーブルに対しての履歴テーブルを作成する。

※ホテルの予約システムを想定する。

1
2
3
- customers (顧客情報テーブル)
- rooms (部屋情報テーブル)
- reservations (予約テーブル)

下記のようなER図となる。

履歴テーブルの作成

MySQL では簡単にテーブルをコピーできる手段として、

1
CREATE TABLE new_tbl LIKE orig_tbl;

※ MySQL のリファレンスの下記を参照

13.1.20.3 CREATE TABLE … LIKE ステートメント https://dev.mysql.com/doc/refman/8.0/ja/create-table-like.html

CREATE TABLE と LIKE を使うことで、テーブル定義の内容をコピーして新たにテーブルを作成することができる。
これを使用して履歴テーブルを作成してみる。

実行するSQLは下記となる。

1
CREATE TABLE reservation_histories LIKE reservations;

実行して、ERは下記となる。

データの挿入

次は、実データのコピーを行う。

MySQL のリファレンスの下記を使用する。

13.2.6.1 INSERT … SELECT ステートメント
https://dev.mysql.com/doc/refman/8.0/ja/insert-select.html

現在の reservations と reservation_histories のテーブルの中身を確認する。

reservations

1
2
3
4
5
6
7
mysql> select * from reservations;
+----------------+-------------+-------------+---------------+----------------+------------+
| reservation_id | customer_id | room_number | check_in_date | check_out_date | total_cost |
+----------------+-------------+-------------+---------------+----------------+------------+
|              1 |           1 | 101         | 2023-09-10    | 2023-09-15     |     500.00 |
+----------------+-------------+-------------+---------------+----------------+------------+
1 row in set (0.00 sec)

reservation_histories

1
2
mysql> select * from reservation_histories;
Empty set (0.00 sec)

下記のSQLで実データをreservation_histories に入れる。

1
INSERT INTO reservation_histories SELECT * FROM reservations;

1
2
3
4
5
6
7
mysql> select * from reservation_histories;
+----------------+-------------+-------------+---------------+----------------+------------+
| reservation_id | customer_id | room_number | check_in_date | check_out_date | total_cost |
+----------------+-------------+-------------+---------------+----------------+------------+
|              1 |           1 | 101         | 2023-09-10    | 2023-09-15     |     500.00 |
+----------------+-------------+-------------+---------------+----------------+------------+
1 row in set (0.00 sec)

これで実データがコピーできた！

トリガーの作成

最後の仕上げとして、トリガーを作成する。

今回はトリガーの契機(実行タイミング)は AFTER かつ INSERT とする。
なので、reservations のテーブルにデータが挿入 された後に reservation_histories に何かしらの処理を加えるということになる。

※要件によってトリガーの契機は変わるので、今回の場合は上記のような条件で作成する。

実際に、トリガーを作成すると下記のSQLとなる。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
DELIMITER //

CREATE TRIGGER insert_reservation_history AFTER INSERT ON reservations
FOR EACH ROW 
BEGIN
    INSERT INTO reservation_histories
    SET
      reservation_id = NEW.reservation_id,
      customer_id = NEW.customer_id,
      room_number = NEW.room_number,
      check_in_date = NEW.check_in_date,
      check_out_date = NEW.check_out_date,
      total_cost = NEW.total_cost;
END;
//

DELIMITER ;

※ INSERT ~ SELECT が使えるかなと思ったのですがトリガー内だと使用できませんでした・・・。
※ DELIMITER の定義をしないと、トリガー内のセミコロンでSQLが構文エラーになるので注意。(ハマりました)

実行結果↓

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
mysql> DELIMITER //
mysql> 
mysql> CREATE TRIGGER insert_reservation_history AFTER INSERT ON reservations
    -> FOR EACH ROW 
    -> BEGIN
    ->     INSERT INTO reservation_histories
    ->     SET
    ->       reservation_id = NEW.reservation_id,
    ->       customer_id = NEW.customer_id,
    ->       room_number = NEW.room_number,
    ->       check_in_date = NEW.check_in_date,
    ->       check_out_date = NEW.check_out_date,
    ->       total_cost = NEW.total_cost;
    -> END;
    -> //
MITER ;Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> DELIMITER ;
mysql> 

動作確認

まずは、 reservations に INSERTをしてみる。

1
2
INSERT INTO reservations (customer_id, room_number, check_in_date, check_out_date, total_cost)
VALUES (2, '102', '2023-09-10', '2023-09-15', 500.00);

実行結果↓

1
2
3
mysql> INSERT INTO reservations (customer_id, room_number, check_in_date, check_out_date, total_cost)
    -> VALUES (2, '102', '2023-09-10', '2023-09-15', 500.00);
Query OK, 1 row affected (0.02 sec)

reservations テーブル

1
2
3
4
5
6
7
8
mysql> select * from reservations;
+----------------+-------------+-------------+---------------+----------------+------------+
| reservation_id | customer_id | room_number | check_in_date | check_out_date | total_cost |
+----------------+-------------+-------------+---------------+----------------+------------+
|              1 |           1 | 101         | 2023-09-10    | 2023-09-15     |     500.00 |
|              2 |           2 | 102         | 2023-09-10    | 2023-09-15     |     500.00 |
+----------------+-------------+-------------+---------------+----------------+------------+
2 rows in set (0.00 sec)

reservation_histories テーブル

1
2
3
4
5
6
7
8
mysql> select * from reservation_histories;
+----------------+-------------+-------------+---------------+----------------+------------+
| reservation_id | customer_id | room_number | check_in_date | check_out_date | total_cost |
+----------------+-------------+-------------+---------------+----------------+------------+
|              1 |           1 | 101         | 2023-09-10    | 2023-09-15     |     500.00 |
|              2 |           2 | 102         | 2023-09-10    | 2023-09-15     |     500.00 |
+----------------+-------------+-------------+---------------+----------------+------------+
2 rows in set (0.00 sec)

おぉ~できてる！

参考

[MySQL] テーブルをコピーする
https://blog.katsubemakito.net/mysql/copy-table
13.1.20.3 CREATE TABLE … LIKE ステートメント | MySQL:: MySQL8.0リファレンスマニュアル https://dev.mysql.com/doc/refman/8.0/ja/create-table-like.html
13.2.6.1 INSERT … SELECT ステートメント | MySQL:: MySQL8.0リファレンスマニュアル https://dev.mysql.com/doc/refman/8.0/ja/insert-select.html
13.1.22 CREATE TRIGGER ステートメント | MySQL:: MySQL8.0リファレンスマニュアル https://dev.mysql.com/doc/refman/8.0/ja/create-trigger.html
25.3.1 トリガーの構文と例 | MySQL:: MySQL8.0リファレンスマニュアル https://dev.mysql.com/doc/refman/8.0/ja/trigger-syntax.html

おわりに

トリガーは複雑かつメンテナンスが非常に難しい機能であると思うので、実際には殆ど使ったことがない…。
とはいえ、機能としてはあるので理解を深めるために試してみた。
今回は、INSERT のみだったが、UPDATEやDELETEを指定することもできる。
UPDATE や DELETE では、 OLD, NEW の句を使用して、古い値や更新値を参照することで、より高度なことが可能になる。
例えば、ステータスの値が、ある値からある値に変わったなどのこともテーブルの情報として記録できる。