MySQLで一連の処理を関数のように実行できるストアドプロシージャの設定と利用方法

MySQL関連のサイトを見ていた際にストアドプロシージャが云々というページがあった。ストアドプロシージャとは聞き覚えが無い言葉なので調べてみると一連の処理をまとめて名前を付けておき、名前を呼び出すことで実行できるという関数っぽい機能の模様。以下に設定と利用方法のメモ。

リファレンス

リファレンスについては以下を参照。

https://dev.mysql.com/doc/refman/8.0/ja/stored-programs-defining.html

事前準備

以下のようなテーブルを用意した。

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    |                |
+------------+--------------+------+-----+---------+----------------+

設定方法

ストアドプロシージャの追加

DELIMITER //
CREATE PROCEDURE test_procedure(IN name VARCHAR(255), IN score INT)
BEGIN 
    INSERT INTO `test_table`
    (
        `name`,
        `score`,
        `regist_ymd`
    ) 
    VALUES 
    (
        name,
        score,
        now()
    );
END
//
DELIMITER ;

「test_procedure(名前,点数)」を実行するとtest_tableテーブルに「名前,点数,日時」が格納されるという内容になる。

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

正常に設定されているかは以下で確認できる。

SHOW CREATE PROCEDURE test_procedure;

ストアドプロシージャの実行

CALL test_procedure('鈴木', 70);

mysql> select * from test_table;
+----+-----------+-------+---------------------+
| id | name      | score | regist_ymd          |
+----+-----------+-------+---------------------+
|  1 | 佐々木    |    80 | 2024-02-14 15:48:40 |
+----+-----------+-------+---------------------+

正常にデータが格納されている点を確認できる。尚、入力値のエスケープはPHP側でやる必要がありそう(MySQL側の方法が見つからなかった)。

ストアドプロシージャの削除

DROP PROCEDURE test_procedure;

所感

通常PHPからMySQLに対して複数の処理を行う場合はその都度データベースに接続する必要があるが、ストアドプロシージャを使うと1回で済むのでパフォーマンスの向上が見込める。

また、ストアドプロシージャと同じSQL文をPHPから実行する場合、ストアドプロシージャの方が実行速度は速いらしい。

ただPHP側とは切り離されてしまうため、PHPのソースコードを読んでもどのような処理が行われているのか分からないというのはちょっと怖い(後日ソースコードを見たときとか)。

上記を諸々考慮すると今のところ良さそうな使いどころは思い浮かばないところ。

あと、ストアドプロシージャと似た機能でストアドファンクションというものがあるみたいなので後日試してみたいと思う。