MySQLのストアドプロシージャとトリガーの確認

2024.09.16

今回はMySQLの機能の１つであるストアドプロシージャとトリガーについて、紹介します。
MySQLのストアドプロシージャとトリガーは、どちらもデータベース内でプログラムを実行するための強力な機能です。

MySQLの概要については、以下の記事で紹介していますので、ご参考ください。

MySQLを理解する〜概要〜
MySQLは、世界で最も広く使用されているオープンソースのリレーショナルデータベース管理システム(RDBMS)の1つです。この記事では、MySQLの概要について調べた内容を共有します。特徴オープンソース: GNU General Publi...
infnotes.com
2024.09.15

ストアドプロシージャ

ストアドプロシージャは、データベース内に保存され、後で呼び出すことができる一連のSQL文です。

主な特徴

  • SQL文を連続で実行したり、変数、条件分岐、繰り返し構文を使用できる。
  • パラメータを受け取り、結果を返すことができる。
  • トランザクション制御が可能。
  • エラーハンドリングが可能。

制約

  • LOCK TABLES、UNLOCK TABLESなどの特定のステートメントは使用できない。
  • 明示的または暗黙的なコミットやロールバックを実行するステートメントは使用できない。

使用例

基本構文

ストアドプロシージャ作成、確認、呼び出しの基本構文を紹介します。

ストアドプロシージャ作成

ストアドプロシージャ作成にはCREATE PROCEDUREコマンドを利用します。

コマンド文法は以下の通りです。
BEGIN〜ENDで囲まれた部分に実行したいSQL文を記載します。

DELIMITER //
CREATE PROCEDURE プロシージャ名()
BEGIN
  -- SQL文をここに記述
END;
//

DELIMITERの役割について

DELIMITERとは、区切り文字を変更するコマンドです。
通常MySQLの区切り文字には;（セミコロン）が利用されていますが、ストアドプロシージャやトリガー定義内でセミコロンを使用する必要がある場合、MySQLのSyntaxエラーが発生します。

このような場合は、DELIMITERを使用して一時的に区切り文字を変更することによって、エラーを回避することが可能です。

DELIMITERを変更後、ストアドプロシージャやトリガーを設定した後は、DELIMITERを元に戻すことを忘れないように注意しましょう。

DELIMITER ;
ストアドプロシージャ確認

確認コマンドはSHOW PROCEDURE STATUS、SHOW CREATE PROCEDUREコマンドを利用します。

ストアドプロシージャの一覧表示

SHOW PROCEDURE STATUS;

特定のストアドプロシージャの詳細確認

SHOW CREATE PROCEDURE プロシージャ名;
ストアドプロシージャ呼び出し

ストアドプロシージャ呼び出しにはCALLコマンドを利用します。
コマンド文法は以下の通りです。

CALL プロシージャ名(引数1, 引数2, ...);

実行例

コマンドの実行例を紹介します。

今回は生徒のテストの点数の合計値を算出するSQL文のストアドプロシージャを作成し、実際に実行して想定の値が返ってくることを確認します。

実行環境

実行環境は以下の通りです。

OSCentOS Stream release 9
MySQL8.4.2 MySQL Community Server
データベース名my_database
テーブル名students

テーブルstudentsのカラム情報は以下の通りです。
各生徒に対して、国語、算数、理科、社会の教科の得点を登録するカラムを作成しています。

mysql> desc students;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| user_id     | int         | NO   | PRI | NULL    |       |
| name        | varchar(50) | YES  |     | NULL    |       |
| language    | int         | YES  |     | NULL    |       |
| mathematics | int         | YES  |     | NULL    |       |
| science     | int         | YES  |     | NULL    |       |
| society     | int         | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql>

テーブルstudentsの登録データは以下の通りです。

mysql> select * from students;
+---------+--------+----------+-------------+---------+---------+
| user_id | name   | language | mathematics | science | society |
+---------+--------+----------+-------------+---------+---------+
|       1 | taro   |       25 |          10 |      55 |      70 |
|       2 | hanako |       80 |          50 |      10 |      23 |
|       3 | ichiro |       99 |          76 |      87 |      66 |
+---------+--------+----------+-------------+---------+---------+
3 rows in set (0.01 sec)

mysql>
コマンド実行

実際にコマンドを実行してみます。

ストアドプロシージャ作成

最初に、テーブルstudentsから生徒の４教科の合計得点を算出するストアドプロシージャを作成します。
テーブルstudentsの属性name（生徒の氏名）を引数にして、引数に指定した生徒の合計得点を値として返す処理をしています。

mysql> DELIMITER //
mysql> CREATE PROCEDURE score_sum(IN student VARCHAR(50))
    -> BEGIN
    ->   SELECT (language + mathematics + science + society)
    ->   FROM students
    ->   WHERE name = student;
    -> END;
    -> //
Query OK, 0 rows affected (0.04 sec)

mysql>
ストアドプロシージャ作成確認

ストアドプロシージャが作成されたか確認します。
実行結果より、正常に作成されてます。

mysql> SHOW CREATE PROCEDURE score_sum;
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure                                                                                                                                                                        | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| score_sum | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `score_sum`(IN student VARCHAR(50))
BEGIN
  SELECT (language + mathematics + science + society)
  FROM students
  WHERE name = student;
END | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.02 sec)

mysql>
ストアドプロシージャ呼び出し

作成したストアドプロシージャを呼び出してみます。
引数は「taro」を指定します。

実行結果は以下の通りです。
実行結果からname：taroの合計得点が値として返ってきていることが確認できました。

mysql> CALL score_sum('taro');
+----------------------------------------------+
| (language + mathematics + science + society) |
+----------------------------------------------+
|                                          160 |
+----------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

トリガー

トリガーは、特定のテーブルに対するINSERT、UPDATE、DELETE操作の前後に自動的に実行されるプログラムです。

主な特徴

  • テーブルに関連付けられ、特定のイベント（INSERT、UPDATE、DELETE）が発生すると自動的に実行される。
  • BEFOREまたはAFTERトリガーとして定義可能。
  • 1つのテーブルに対して、同じタイミングと同じイベントの2つのトリガーは定義できない。
  • すべてのトリガーは行トリガーとして動作する。

制約

  • トリガー内でのステートメントの失敗は、トランザクションテーブルの場合ロールバックを引き起こす。
  • 非トランザクションテーブルの場合、エラー発生前の変更は有効なまま残る。

使用例

基本構文

トリガーの作成、確認、呼び出しの基本構文を紹介します。

トリガー作成

トリガー作成にはCREATE TRIGGERコマンドを利用します。

コマンド文法は以下の通りです。

  • BEFORE/AFTERで、トリガーを実行するタイミングを指定します。
  • INSERT/UPDATE/DELETEで、どの操作に対してトリガーを実行するか指定します。
  • FOR EACH ROWは、影響を受ける各行に対してトリガーが実行されることを示します。
  • BEGIN〜ENDの間にトリガーの処理内容を記述します。
DELIMITER //
CREATE TRIGGER トリガー名
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON テーブル名
FOR EACH ROW
BEGIN
  -- トリガーの処理内容
END //

DELIMITERの役割について

DELIMITERとは、区切り文字を変更するコマンドです。
通常MySQLの区切り文字には;（セミコロン）が利用されていますが、ストアドプロシージャやトリガー定義内でセミコロンを使用する必要がある場合、MySQLのSyntaxエラーが発生します。

このような場合は、DELIMITERを使用して一時的に区切り文字を変更することによって、エラーを回避することが可能です。

DELIMITERを変更後、ストアドプロシージャやトリガーを設定した後は、DELIMITERを元に戻すことを忘れないように注意しましょう。

DELIMITER ;
トリガー確認

確認コマンドはSHOW TRIGGERSコマンドを利用します。

すべてのトリガーを表示する場合

SHOW TRIGGERS;

特定のデータベースのトリガーを表示する場合

SHOW TRIGGERS FROM データベース名;
または
SHOW TRIGGERS IN データベース名;

実行例

コマンドの実行例を紹介します。

今回は先ほどのストアドプロシージャで利用したテーブルstudentsの監査ログの作成し、データが更新された時刻を別のテーブルに記録する処理を作成します。

実行環境

実行環境は以下の通りです。

OSCentOS Stream release 9
MySQL8.4.2 MySQL Community Server
データベース名my_database
テーブル名students
監査ログ用テーブル名students_audit

テーブルstudentsの登録データは以下の通りです。

mysql> select * from students;
+---------+--------+----------+-------------+---------+---------+
| user_id | name   | language | mathematics | science | society |
+---------+--------+----------+-------------+---------+---------+
|       1 | taro   |       25 |          10 |      55 |      70 |
|       2 | hanako |       80 |          50 |      10 |      23 |
|       3 | ichiro |       99 |          76 |      87 |      66 |
+---------+--------+----------+--------

監査ログ用テーブル：students_auditのカラム情報は以下の通りです。

mysql> desc students_audit;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| user_id     | int         | NO   | PRI | NULL    |       |
| name        | varchar(50) | YES  |     | NULL    |       |
| change_date | datetime    | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>
コマンド実行

実際にコマンドを実行してみます。

トリガー作成

最初に、テーブルstudentsのデータが更新された際に、別テーブルに更新ログを記録するトリガーを作成します。

実行条件として、テーブルstudentsのデータ更新（UPDATE）を指定しています。（AFTER UPDATE ON students）

処理として、監査ログ用テーブル：students_auditに対象の生徒の氏名と更新日時を格納する処理（BEGIN〜ENDで囲まれた部分）を実行します。

mysql> DELIMITER //
mysql> CREATE TRIGGER audit_log
    -> AFTER UPDATE ON students
    -> FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO students_audit (user_id, name, change_date)
    ->   VALUES (NEW.user_id, NEW.name, NOW());
    -> END //
Query OK, 0 rows affected (0.03 sec)

mysql>
トリガー確認

作成したトリガーを確認します。
実行結果より、正常に作成されてます。

mysql> SHOW TRIGGERS FROM my_database;
+-----------+--------+----------+--------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger   | Event  | Table    | Statement                                                                                                    | Timing | Created                | sql_mode                                                                                                              | Definer        | character_set_client | collation_connection | Database Collation |
+-----------+--------+----------+--------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| audit_log | UPDATE | students | BEGIN
  INSERT INTO students_audit (user_id, name, change_date)
  VALUES (NEW.user_id, NEW.name, NOW());
END | AFTER  | 2024-09-16 13:23:58.56 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+--------+----------+--------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.03 sec)

mysql>
トリガー実行確認

作成したトリガーは実行されるか確認します。

実行のトリガーとなるテーブル：studentsのデータを更新し、監査ログが監査ログ用テーブル：students_auditに登録されることを確認します。

更新内容はname：taroの国語の点数（属性language）を40に更新しています。

実行結果からテーブル：students_auditに監査ログが登録されていることが確認できました。

mysql> select * from students_audit;
Empty set (0.01 sec)

mysql>
mysql> UPDATE students SET language = 40 WHERE name='taro';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>
mysql> select * from students;
+---------+--------+----------+-------------+---------+---------+
| user_id | name   | language | mathematics | science | society |
+---------+--------+----------+-------------+---------+---------+
|       1 | taro   |       40 |          10 |      55 |      70 |
|       2 | hanako |       80 |          50 |      10 |      23 |
|       3 | ichiro |       99 |          76 |      87 |      66 |
+---------+--------+----------+-------------+---------+---------+
3 rows in set (0.01 sec)

mysql>
mysql> select * from students_audit;
+---------+------+---------------------+
| user_id | name | change_date         |
+---------+------+---------------------+
|       1 | taro | 2024-09-16 13:33:40 |
+---------+------+---------------------+
1 row in set (0.01 sec)

mysql>

ストアドプロシージャとトリガーの違い

ストアドプロシージャとトリガーの主な違いは以下の通りです。
違いを理解し、適切な状況で使い分けることが重要です。

比較観点ストアドプロシージャトリガー
呼び出し方明示的に呼び出す必要がある特定のイベント(INSERT、UPDATE、DELETE)が発生すると自動的に実行される
パラメータ入力パラメータの設定可能入力パラメータの設定不可
戻り値設定可能設定不可
トランザクション制御トランザクション制御(COMMIT、ROLLBACK)が可能トランザクション制御(COMMIT、ROLLBACK)が不可能
用途主に一般的なデータ操作や複雑な処理で利用主にデータの整合性維持や監査ログの生成などで利用
実行タイミング任意のタイミング特定のテーブル操作の前後
スコープデータベース全体特定のテーブル

共通の注意点

  • ストアドプロシージャとトリガーの両方で、DEFINER属性が重要な役割を果たす。これにより、実行時の権限が決定される。
  • MySQLのストアドプロシージャとトリガーは、他の商用DBMSと比べると機能が限定的。
  • 大規模なシステムでの多用は推奨されないが、部分的な使用は効果的な場合がある。

DEFINER属性とは

DEFINERはストアドオブジェクト(ストアドプロシージャ、関数、トリガー、ビューなど)の実行権限を制御する重要な属性です。

DEFINER属性に指定されたアカウントの権限でストアドオブジェクトが実行されます。
値を指定しない場合、デフォルトでCURRENT_USER()の値が使用されます。

セキュリティリスクの観点から、DEFINER属性に指定するアカウントは、必要最小限の権限を持つアカウントを選択します。
rootなどの高権限アカウントをDEFINERに指定するのは避けましょう。

まとめ

今回はMySQLの機能の１つであるストアドプロシージャとトリガーについて、紹介しました。

これらの機能を適切に使用することで、データベース操作の一貫性を保ち、アプリケーションロジックの一部をデータベース側に移動させることができます。

ただし、パフォーマンスや保守性を考慮しながら、適切に設計・実装することが重要です。

ご参考になれば、幸いです。

以上になります。ありがとうございました。

参考

https://dev.mysql.com/doc/refman/8.0/ja/stored-program-restrictions.html
https://qiita.com/gohandesuyo/items/db88cc60f89a7ba18445
https://www.php.cn/ja/faq/418510.html
https://itmanabi.com/stored-procedure/
https://dev.mysql.com/doc/refman/8.0/ja/create-procedure.html
https://proengineer.internous.co.jp/content/columnfeature/7078

コメント

タイトルとURLをコピーしました