はじめに
MySQLのストアドプロシージャは、データベース内で実行されるSQLコードの集合です。初回実行時にコンパイルされ、セッション中はキャッシュされて再利用されます。今回は、Google Colab上でMySQLを動かし、ストアドプロシージャの基本的な使い方を実際に試してみました。
「ストアドプロシージャって何？」「どうやって使うの？」という方に向けて、実際に動かしながら学べる備忘録にまとめています。
環境
- Google Colab
- MySQL 8.0
- Python 3.x + PyMySQL
事前準備（MySQLセットアップ）
# MySQLサーバーのインストールと起動
!apt update && apt install mysql-server -y
!service mysql start
!mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';"
!pip install pymysql
ストアドプロシージャの基本
1. データベースとテストデータの準備
import pymysql
# データベース接続
connection = pymysql.connect(
host='localhost',
user='root',
password='password',
charset='utf8mb4'
)
try:
with connection.cursor() as cursor:
# データベースとテーブルの準備
cursor.execute("CREATE DATABASE IF NOT EXISTS sp_test")
cursor.execute("USE sp_test")
cursor.execute("""
CREATE TABLE IF NOT EXISTS curry_menu (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
spice_level VARCHAR(20),
price DECIMAL(10,2)
)
""")
# サンプルデータの挿入
sample_data = [
("チキンカレー", "甘口", 800),
("ビーフカレー", "中辛", 900),
("ポークカレー", "中辛", 850),
("野菜カレー", "甘口", 750),
("シーフードカレー", "辛口", 950),
("キーマカレー", "辛口", 800)
]
for data in sample_data:
cursor.execute(
"INSERT IGNORE INTO curry_menu (name, spice_level, price) VALUES (%s, %s, %s)",
data
)
connection.commit()
print("データベースとテーブルの準備完了")
finally:
connection.close()
2. ストアドプロシージャの作成 (今回の主役)
# 再接続
connection = pymysql.connect(
host='localhost',
user='root',
password='password',
database='sp_test',
charset='utf8mb4'
)
try:
with connection.cursor() as cursor:
# 1. 辛さレベル別カレー数を取得するストアドプロシージャ
cursor.execute("DROP PROCEDURE IF EXISTS GetCurryCountBySpice")
cursor.execute("""
CREATE PROCEDURE GetCurryCountBySpice(IN spice_name VARCHAR(20))
BEGIN
SELECT COUNT(*) as curry_count, AVG(price) as avg_price
FROM curry_menu
WHERE spice_level = spice_name;
END
""")
# 2. 価格更新のストアドプロシージャ
cursor.execute("DROP PROCEDURE IF EXISTS UpdateCurryPrice")
cursor.execute("""
CREATE PROCEDURE UpdateCurryPrice(
IN curry_id INT,
IN new_price DECIMAL(10,2),
OUT old_price DECIMAL(10,2)
)
BEGIN
SELECT price INTO old_price FROM curry_menu WHERE id = curry_id;
UPDATE curry_menu SET price = new_price WHERE id = curry_id;
END
""")
connection.commit()
print("ストアドプロシージャの作成完了")
finally:
connection.close()
3. ストアドプロシージャの実行
# ストアドプロシージャの実行
connection = pymysql.connect(
host='localhost',
user='root',
password='password',
database='sp_test',
charset='utf8mb4'
)
try:
with connection.cursor() as cursor:
# 1. 辛さレベル別カレー数の取得
cursor.callproc('GetCurryCountBySpice', ['中辛'])
result = cursor.fetchone()
print(f"中辛カレー: {result[0]}種類, 平均価格: {result[1]:,.0f}円")
# 次の結果セットに移動（重要！）
cursor.nextset()
# 2. 価格更新の実行
cursor.callproc('UpdateCurryPrice', [1, 850, 0])
# OUT パラメータの取得
cursor.execute("SELECT @_UpdateCurryPrice_2")
old_price = cursor.fetchone()[0]
print(f"ID=1のカレーの価格を更新: {old_price:,.0f}円 → 850円")
connection.commit()
finally:
connection.close()
ストアドプロシージャの使用例
条件分岐とループな処理
connection = pymysql.connect(
host='localhost',
user='root',
password='password',
database='sp_test',
charset='utf8mb4'
)
try:
with connection.cursor() as cursor:
# 価格帯分類のストアドプロシージャ
cursor.execute("DROP PROCEDURE IF EXISTS ClassifyCurryPrice")
cursor.execute("""
CREATE PROCEDURE ClassifyCurryPrice()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE curry_id INT;
DECLARE curry_name VARCHAR(100);
DECLARE curry_price DECIMAL(10,2);
DECLARE price_category VARCHAR(10);
DECLARE cur CURSOR FOR SELECT id, name, price FROM curry_menu;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 結果格納用の一時テーブル
DROP TEMPORARY TABLE IF EXISTS temp_curry_categories;
CREATE TEMPORARY TABLE temp_curry_categories (
curry_id INT,
curry_name VARCHAR(100),
price DECIMAL(10,2),
category VARCHAR(10)
);
OPEN cur;
read_loop: LOOP
FETCH cur INTO curry_id, curry_name, curry_price;
IF done THEN
LEAVE read_loop;
END IF;
-- 価格帯カテゴリの判定
IF curry_price >= 900 THEN
SET price_category = 'プレミアム';
ELSEIF curry_price >= 800 THEN
SET price_category = 'レギュラー';
ELSE
SET price_category = 'エコノミー';
END IF;
INSERT INTO temp_curry_categories VALUES (curry_id, curry_name, curry_price, price_category);
END LOOP;
CLOSE cur;
SELECT * FROM temp_curry_categories ORDER BY price DESC;
END
""")
# 実行
cursor.callproc('ClassifyCurryPrice')
results = cursor.fetchall()
print("カレー価格帯分類一覧:")
print("ID | カレー名 | 価格 | カテゴリ")
print("-" * 45)
for row in results:
print(f"{row[0]:2} | {row[1]:16} | {row[2]:5.0f} | {row[3]}")
connection.commit()
finally:
connection.close()
ストアドプロシージャの管理
# ストアドプロシージャの一覧確認と削除
connection = pymysql.connect(
host='localhost',
user='root',
password='password',
database='sp_test',
charset='utf8mb4'
)
try:
with connection.cursor() as cursor:
# 作成されたストアドプロシージャの一覧
cursor.execute("SHOW PROCEDURE STATUS WHERE Db = 'sp_test'")
procedures = cursor.fetchall()
print("作成されたストアドプロシージャ:")
for proc in procedures:
print(f"- {proc[1]}")
# 特定のストアドプロシージャの詳細確認
cursor.execute("SHOW CREATE PROCEDURE GetCurryCountBySpice")
result = cursor.fetchone()
print(f"\nプロシージャ定義:\n{result[2]}")
finally:
connection.close()
ストアドプロシージャのメリット・デメリット
ストアドプロシージャにはいくつかのメリットがあります。まず、事前にコンパイルされることで実行速度が向上し、複数のSQL文を1回の呼び出しで実行できるため、ネットワークの負荷も軽減されます。また、パラメータ化されたクエリの利用により、SQLインジェクションのリスクを抑えることができ、複雑な処理を一度定義すれば複数のアプリケーションから再利用できる点も利点です。
一方で、デメリットも存在します。ストアドプロシージャはデータベースに依存するため、他のDBMSへ移植する際には書き直しが必要になることがあります。また、アプリケーションコードと分離されているため、デバッグ作業が煩雑になりやすく、さらに、バージョン管理の仕組みが整っていない場合は変更履歴の追跡も困難になります。
まとめ
Google ColabでMySQLのストアドプロシージャを試すことで、データベース側での処理の強力さを実感できました。特に複雑な条件分岐やループ処理をデータベース内で実行できるのは大きなメリットです。
ストアドプロシージャは適切に使用すれば、パフォーマンスの向上やセキュリティの強化につながります。ただし、移植性やメンテナンス性の観点から、使用する場面を慎重に検討することが重要です。
実際の開発では、シンプルなCRUD操作はアプリケーション側で、複雑なデータ処理や大量データの操作はストアドプロシージャで、という使い分けが効果だと思われます。
参考情報