MySQLのストアドプロシージャとトリガー：書き方と使いどころ

ストアドプロシージャ（stored procedure）は、複数の SQL をひとまとめにして名前をつけ、データベースの中に保存しておく仕組みです。トリガー（trigger）は、テーブルに INSERT・UPDATE・DELETE が起きたときに自動で動く SQL です。ネットショップの在庫を例に、注文を受けて在庫を減らすプロシージャと、在庫が変わるたびに履歴を残すトリガーを作ります。後半では、トリガーが動かない場面や保守で困る点と、アプリ側に書いたほうがよい処理の見分け方を表にまとめました。

以下の結果は MySQL 8.0 で実際に実行したものです。

このシナリオで考える

商品の在庫を持つ products、注文を記録する orders、在庫の変更履歴を残す stock_history の3つのテーブルを使います。消しゴムは在庫0から始めます。

CREATE TABLE products (
  id    INT PRIMARY KEY,
  name  VARCHAR(50) NOT NULL,
  stock INT NOT NULL
);

CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  qty        INT NOT NULL,
  ordered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE stock_history (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  old_stock  INT NOT NULL,
  new_stock  INT NOT NULL,
  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products VALUES
  (1, 'ノート', 10),
  (2, 'ボールペン', 3),
  (3, '消しゴム', 0);

作るものは2つです。注文を受けたら在庫を確かめてから減らし、注文を記録する処理はプロシージャにします。在庫の数が変わったら、どこから変えても履歴に残す処理はトリガーにします。

ストアドプロシージャで注文の処理をまとめる

プロシージャは CREATE PROCEDURE で作り、CALL で呼び出します。引数には向きがあり、IN は値を渡すだけ、OUT は結果を受け取る、INOUT は両方に使えます。

DELIMITER //
CREATE PROCEDURE place_order(
  IN  p_product_id INT,
  IN  p_qty        INT,
  OUT p_order_id   INT
)
BEGIN
  DECLARE v_stock INT;

  START TRANSACTION;

  SELECT stock INTO v_stock
    FROM products
   WHERE id = p_product_id
   FOR UPDATE;

  IF v_stock IS NULL OR v_stock < p_qty THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '在庫が足りません';
  END IF;

  UPDATE products SET stock = stock - p_qty WHERE id = p_product_id;
  INSERT INTO orders (product_id, qty) VALUES (p_product_id, p_qty);
  SET p_order_id = LAST_INSERT_ID();

  COMMIT;
END //
DELIMITER ;

最初と最後の DELIMITER は、mysql コマンドなどのクライアントに、文の区切りを一時的に // に変えるよう伝える命令です。プロシージャの中には ; が何度も出てきます。区切りを変えておかないと、最初の ; で CREATE 文が切れてエラーになります。DELIMITER はクライアント側の命令なので、PHP や Python のドライバーから CREATE 文を送るときは書きません。

中で使っている書き方は次のとおりです。

書き方	意味
DECLARE v_stock INT	プロシージャの中だけで使う変数を作る
SELECT ... INTO v_stock	検索結果を変数に入れる。行がなければ変数は NULL のまま
FOR UPDATE	読んだ行をロックし、同時に来た注文で在庫がずれないようにする
SIGNAL SQLSTATE '45000'	自分でエラーを起こして処理を止める。45000 は利用者が決めるエラー用の番号
LAST_INSERT_ID()	直前の INSERT で振られた AUTO_INCREMENT の番号

FOR UPDATE の動きは「楽観ロックと悲観ロックの違い：FOR UPDATEとversion列」で説明しています。

ノートを4冊注文してみます。

CALL place_order(1, 4, @order_id);
SELECT @order_id;
SELECT * FROM products;

+-----------+
| @order_id |
+-----------+
|         1 |
+-----------+

+----+-----------------+-------+
| id | name            | stock |
+----+-----------------+-------+
|  1 | ノート          |     6 |
|  2 | ボールペン      |     3 |
|  3 | 消しゴム        |     0 |
+----+-----------------+-------+

ノートの在庫が10から6に減り、注文番号の1が @order_id に入りました。次に、3本しかないボールペンを5本注文します。

CALL place_order(2, 5, @order_id);
SELECT id, product_id, qty FROM orders;

ERROR 1644 (45000): 在庫が足りません

+----+------------+-----+
| id | product_id | qty |
+----+------------+-----+
|  1 |          1 |   4 |
+----+------------+-----+

SIGNAL の前に ROLLBACK しているので、失敗した注文は orders に残りません。アプリからは CALL を1回送るだけで、在庫の確認から注文の記録までが終わります。

トリガーで在庫の変更履歴を自動で残す

トリガーは CREATE TRIGGER 名前 {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON テーブル FOR EACH ROW 本体 の形で作ります。FOR EACH ROW のとおり、変更された1行ごとに1回動きます。本体の中では、変更前の行を OLD、変更後の行を NEW で参照します。

イベント	OLD	NEW
INSERT	なし	追加される行
UPDATE	変更前の行	変更後の行
DELETE	削除される行	なし

BEFORE は行を書き込む前に動き、SET NEW.列 = 値 で書き込む値を変えられます。AFTER は書き込みのあとに動くので、別のテーブルへの記録に向いています。今回は AFTER UPDATE を使います。

DELIMITER //
CREATE TRIGGER trg_products_stock_history
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.stock <> OLD.stock THEN
    INSERT INTO stock_history (product_id, old_stock, new_stock)
    VALUES (NEW.id, OLD.stock, NEW.stock);
  END IF;
END //
DELIMITER ;

プロシージャでの注文、UPDATE を直接打った在庫の補充、商品名だけの変更の3つを続けて実行し、履歴を見ます。

CALL place_order(2, 2, @order_id);
UPDATE products SET stock = 20 WHERE id = 3;
UPDATE products SET name = 'ノート（A5）' WHERE id = 1;

SELECT id, product_id, old_stock, new_stock FROM stock_history;

+----+------------+-----------+-----------+
| id | product_id | old_stock | new_stock |
+----+------------+-----------+-----------+
|  1 |          2 |         3 |         1 |
|  2 |          3 |         0 |        20 |
+----+------------+-----------+-----------+

プロシージャ経由の変更も、手で打った UPDATE も履歴に残りました。商品名だけを変えた3つ目は、IF で在庫が変わったときに絞っているので記録されていません。トリガーはテーブルへの書き込みそのものに付くので、アプリでの書き忘れや手作業の修正でも履歴が抜けません。トリガーを使う一番の理由はここにあります。

作成日時・更新日時を入れるだけなら、トリガーより列の DEFAULT のほうが簡単です。こちらは「MySQLでcreated_at・updated_atを自動で入れる」で説明しています。

よくある落とし穴

トリガーの中から、トリガーが付いているテーブル自身は更新できません。orders への INSERT のたびに orders を UPDATE しようとすると、INSERT ごと失敗します。

CREATE TRIGGER trg_orders_bad
AFTER INSERT ON orders
FOR EACH ROW
  UPDATE orders SET qty = qty WHERE id = NEW.id;

INSERT INTO orders (product_id, qty) VALUES (1, 1);

ERROR 1442 (HY000): Can't update table 'orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

追加する行の値を変えたいときは、BEFORE INSERT のトリガーで SET NEW.列 = 値 と書きます。ほかにも、次の場面でつまずきやすくなります。

場面	起きること
外部キーの ON DELETE CASCADE で子の行が消える	子テーブルの DELETE トリガーは動かない
TRUNCATE TABLE で全行を消す	DELETE トリガーは動かない
トランザクションの途中でプロシージャを呼ぶ	中の START TRANSACTION で、それまでの変更が暗黙にコミットされる
トリガーの中でエラーが出る	元の INSERT・UPDATE・DELETE も失敗する（InnoDB の場合）
mysqldump でバックアップする	トリガーは入るが、プロシージャは --routines を付けないと入らない

トリガーは SHOW CREATE TABLE の結果にも出てこないので、あることを忘れがちです。information_schema で一覧を出せます。

SELECT trigger_name, event_manipulation, action_timing, event_object_table
  FROM information_schema.triggers
 WHERE trigger_schema = DATABASE();

SELECT routine_name, routine_type
  FROM information_schema.routines
 WHERE routine_schema = DATABASE();

+----------------------------+--------------------+---------------+--------------------+
| TRIGGER_NAME               | EVENT_MANIPULATION | ACTION_TIMING | EVENT_OBJECT_TABLE |
+----------------------------+--------------------+---------------+--------------------+
| trg_products_stock_history | UPDATE             | AFTER         | products           |
| trg_orders_bad             | INSERT             | AFTER         | orders             |
+----------------------------+--------------------+---------------+--------------------+

+--------------+--------------+
| ROUTINE_NAME | ROUTINE_TYPE |
+--------------+--------------+
| place_order  | PROCEDURE    |
+--------------+--------------+

どこに書くかの判断

保守で困るのは、処理がアプリのコードとデータベースの2か所に分かれることです。プロシージャやトリガーは git で差分を追いにくく、単体テストも書きにくくなります。アプリの担当者が知らないまま値が書き換わる、というトラブルも起きます。迷ったら次の表で決めます。

やりたいこと	向いている書き方
変更履歴や監査ログを漏れなく残す	トリガー
複数のアプリや手作業が同じテーブルを書き換える	トリガー
何本もの SQL を1回の通信で実行したい（夜間の集計など）	プロシージャ
割引の計算など、よく変わる業務ルール	アプリ側
値の範囲や必須のチェック	CHECK 制約・NOT NULL
作成日時・更新日時	列の DEFAULT・ON UPDATE

まとめ

ストアドプロシージャは、在庫の確認から注文の記録までのような一連の SQL を、CALL 1回で動かすときに使います。トリガーは、どこから書き換えられても必ず残したい記録に向いています。作るときは DELIMITER で区切りを変え、トリガーでは OLD と NEW で変更前後の行を参照します。CASCADE や TRUNCATE では動かないこと、mysqldump には --routines が要ることも覚えておくと、履歴が抜けたりバックアップからプロシージャが消えたりするのを防げます。

参考リンク

• MySQL 8.0 Reference Manual: CREATE PROCEDURE and CREATE FUNCTION Statements
• MySQL 8.0 Reference Manual: Trigger Syntax and Examples
• MySQL 8.0 Reference Manual: SIGNAL Statement
• MySQL 8.0 Reference Manual: Restrictions on Stored Programs

アイキャッチ画像: Photo by Brett Jordan on Unsplash