背景と必要性
バックエンド開発を始めたばかりの頃、今でも忘れられない苦いシステム障害を経験しました。ユーザーの会員ランクを更新する際、ORMを使って数万件ものレコードを一括でメモリ（RAM）上に取得し、コード側のforループで各オブジェクトを処理しながらデータベースへ1件ずつUPDATEを発行していたのです。数百件程度のテストデータでは問題なく軽快に動作していました。しかし、50万人以上のユーザーを抱える本番環境へデプロイした瞬間、Out Of Memory（OOM）でアプリケーションが即座にクラッシュ。数十万回ものネットワークラウンドトリップによって帯域が逼迫し、データベースサーバーのCPU使用率は100%に張り付きました。
そもそも、なぜ大量のデータを一度バックエンド側に引き込み、再び書き戻す必要があったのでしょうか。この処理は、Stored Procedure（ストアドプロシージャ）とCursor（カーソル）を活用すれば、データベースエンジン側だけで完結させることができます。プロシージャは一連の処理をMySQLサーバー内部で実行するため、ネットワーク転送のオーバーヘッドをほぼゼロに抑えられます。そしてカーソルは、検索結果を1行ずつ順次処理するためのポインタとして機能します。通常のUPDATE ... JOIN文だけでは対応しきれない複雑な分岐ロジックが存在する場合に、その真価を発揮します。
とはいえ、データベース上でのバッチ更新には常にリスクが伴います。適切なException Handler（例外ハンドラ）を実装していないと、たった1件の外部キー違反や重複キーエラーが発生しただけでトランザクションがハングしたり、データの中途半端な更新（不整合）が発生したりします。私自身、深夜3時にスクリプトが途中でエラー停止し、徹夜でデータリストア作業に追われた経験があります。その教訓から得た鉄則は、「一括処理を行うすべてのプロシージャは必ずトランザクション内に収め、自動ロールバックハンドラと詳細なログ記録を備えなければならない」ということです。
テスト環境の構築
クエリを実行する前に、ローカル環境またはステージング環境に独立したテスト用データベースを用意してください。絶対に本番環境で直接試してはいけません。ここでは、顧客のランク昇格とロイヤルティポイント付与の処理を題材として進めます：
-- 1. デモ用データベースの作成
CREATE DATABASE IF NOT EXISTS itfromzero_store;
USE itfromzero_store;
-- 2. 顧客テーブル
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
tier VARCHAR(20) DEFAULT 'STANDARD',
total_spent DECIMAL(12, 2) DEFAULT 0.00,
bonus_points INT DEFAULT 0,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 3. バッチ処理履歴ログテーブル
CREATE TABLE process_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
procedure_name VARCHAR(100),
records_affected INT,
status VARCHAR(20),
error_message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 4. テスト用サンプルデータの投入
INSERT INTO customers (name, tier, total_spent, bonus_points) VALUES
('Nguyen Van A', 'STANDARD', 1500000.00, 0),
('Tran Thi B', 'STANDARD', 5200000.00, 0),
('Le Van C', 'VIP', 12000000.00, 100),
('Pham Thi D', 'STANDARD', 800000.00, 0),
('Hoang Van E', 'STANDARD', 6100000.00, 0);詳細な構成と設計
安全にカーソルを走査するプロシージャには、密接に連携する4つの要素が必要です：
- DELIMITER：ステートメントの終了区切り文字を変更します（例：
$$）。これにより、プロシージャ本文内のセミコロンでSQLの実行が途切れるのを防ぎます。 - Cursor：
SELECT文の結果セットを取得し、1行ずつ順次処理するためのカーソルです。 - CONTINUE HANDLER FOR NOT FOUND：カーソルが最後の行までデータを読み切ったことを検知するフラグです。
- EXIT HANDLER FOR SQLEXCEPTION：安全を担保するセーフティネットです。予期せぬエラーが発生した場合、直ちにロールバックを実行し、エラーの詳細をログに記録します。
MySQLにおける変数宣言の順序ルール
MySQLでは、BEGIN ... ENDブロック内の宣言順序を厳格に守る必要があります：
- ローカル変数（
DECLARE v_variable INT...） - カーソル（
DECLARE cur_name CURSOR FOR...） - ハンドラ（
DECLARE CONTINUE/EXIT HANDLER...）
カーソルやハンドラの後にローカル変数を宣言すると、MySQLは構文エラーERROR 1337 (42000): Variable or condition declaration after cursor or handler declarationを発生させます。
プロシージャの実装
DELIMITER $$
CREATE PROCEDURE sp_process_loyalty_upgrade(
OUT p_total_updated INT,
OUT p_status_code VARCHAR(20)
)
proc_label: BEGIN
-- 1. データ処理用変数の宣言
DECLARE v_done INT DEFAULT 0;
DECLARE v_id INT;
DECLARE v_spent DECIMAL(12, 2);
DECLARE v_new_points INT;
DECLARE v_new_tier VARCHAR(20);
DECLARE v_counter INT DEFAULT 0;
-- エラー発生時のSQLエラーコード・メッセージ格納用変数
DECLARE v_error_code CHAR(5) DEFAULT '00000';
DECLARE v_error_msg TEXT;
-- 2. ランク昇格対象となる顧客を取得するカーソルの宣言
DECLARE cur_customers CURSOR FOR
SELECT id, total_spent FROM customers WHERE tier = 'STANDARD';
-- 3. カーソルの読み込み完了を検知するハンドラ
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
-- 4. 予期せぬ例外（SQLEXCEPTION）を捕捉するハンドラ
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
v_error_code = RETURNED_SQLSTATE,
v_error_msg = MESSAGE_TEXT;
ROLLBACK;
INSERT INTO process_logs (procedure_name, records_affected, status, error_message)
VALUES ('sp_process_loyalty_upgrade', v_counter, 'FAILED', CONCAT('Code: ', v_error_code, ' - ', v_error_msg));
SET p_total_updated = v_counter;
SET p_status_code = 'ERROR';
END;
-- 5. トランザクションの開始
START TRANSACTION;
-- 6. カーソルを開く
OPEN cur_customers;
read_loop: LOOP
FETCH cur_customers INTO v_id, v_spent;
-- 全レコードの処理が完了したらループを抜ける
IF v_done = 1 THEN
LEAVE read_loop;
END IF;
-- 利用金額に応じたランク分けおよびポイント計算ロジック
IF v_spent >= 5000000.00 THEN
SET v_new_tier = 'GOLD';
SET v_new_points = 500;
ELSEIF v_spent >= 1000000.00 THEN
SET v_new_tier = 'SILVER';
SET v_new_points = 100;
ELSE
SET v_new_tier = 'STANDARD';
SET v_new_points = 10;
END IF;
-- 1行ずつデータを更新
UPDATE customers
SET tier = v_new_tier, bonus_points = bonus_points + v_new_points
WHERE id = v_id;
SET v_counter = v_counter + 1;
END LOOP;
-- 7. カーソルを閉じてメモリを解放
CLOSE cur_customers;
-- 8. コミットして変更を確定
COMMIT;
-- 完了ログを記録
INSERT INTO process_logs (procedure_name, records_affected, status, error_message)
VALUES ('sp_process_loyalty_upgrade', v_counter, 'SUCCESS', NULL);
SET p_total_updated = v_counter;
SET p_status_code = 'SUCCESS';
END proc_label$$
DELIMITER ;テストとモニタリング
実行と結果の検証
MySQLクライアントから2つの出力パラメータを指定してプロシージャを実行します：
CALL sp_process_loyalty_upgrade(@updated_count, @status);
-- 戻り値（処理結果ステータス）を確認
SELECT @updated_count AS total_updated, @status AS status;
-- 更新後の実データを確認
SELECT id, name, tier, total_spent, bonus_points FROM customers;処理履歴を確認するために、ログテーブルも忘れずに確認しておきましょう：
SELECT * FROM process_logs ORDER BY id DESC LIMIT 5;カーソル使用時のリソース監視
MySQLのカーソルは基本的にread-only（読み取り専用）かつasensitive（非センシティブ）です。カーソルを開くと、MySQLは内部的にメモリ上またはディスク上に一時テーブル（temporary table）を作成することが多く、対象テーブルが数百万行に及ぶ場合、メモリ使用量が数秒で急上昇する恐れがあります。
プロシージャがサーバーリソースを過度に圧迫していないか確認するには、以下のステータス変数とプロセスを監視します：
-- メモリおよびディスク上に作成された一時テーブル数を確認
SHOW STATUS LIKE 'Created_tmp%tables';
-- リソースを占有している実行中プロセスの特定
SHOW FULL PROCESSLIST;本番環境へ導入する際の実践的なベストプラクティス：
- 巨大テーブルの全行走査を避ける：5万行を超えるようなテーブルでは、単一のカーソルですべてを処理するのではなく、ID範囲やページネーションを用いてチャンク（1回あたり1,000〜5,000行程度）に分割して処理します。
- メモリの確実な解放：ループ終了後は必ず速やかに
CLOSE cur_name;を実行し、MySQLの一時作業メモリを解放してください。 - 集合指向（Set-based）のSQLを最優先する：10万行の更新処理を行う場合、単一の
UPDATE ... CASE WHEN ...文であれば約150msで完了するのに対し、カーソルによる1行ごとのループ処理では10〜15秒かかることがあります。カーソルの採用は、複雑なビジネスロジックの分岐やレコードごとの外部関数呼び出しがどうしても不可欠なケースに限定すべきです。
