勉強したことのメモ

Webエンジニア / プログラマが勉強したことのメモ。

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

  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のソースコードを読んでもどのような処理が行われているのか分からないというのはちょっと怖い(後日ソースコードを見たときとか)。

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

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

 - MySQL データベース

  関連記事

お名前.comのレンタルサーバ(SDサーバ)にMySQLデータのインポートする方法

お名前.comのレンタルサーバにそこそこ大きいサイズのMySQLデータを移行した ...

MySQLでLIKE検索時に大文字・小文字・半角・全角全て区別したい

以前にMySQLであいまい検索時に大文字小文字は区別しないが、それ以外は全て区別 ...

mysqldumpが使えないレンタルサーバでMySQLのバックアップ

mysqldumpが使えないレンタルサーバで開発を行うことがあり、尚且つ他社に使 ...

MySQLiでUPDATE文

MySQLiでUPDATE文 ■ソース $mysqli = new mysqli ...

MySQLに保存したデータをJSON形式で取得する方法

MySQLに保存したデータをJSON形式で利用したいということがたまにある(Ja ...