MySQLストアドプロシージャでレコード更新時も変数値を保持する方法

MySQLのストアドプロシージャでは、変数への値の格納方法を誤ると、テーブルをUPDATEするたびに変数の中身まで書き換わってしまうことがあります。この記事では、その原因を具体的なサンプルコードで確認し、更新前の値を保持し続けるための正しい実装方法を解説します。

準備：サンプルテーブルを作成する

まず、動作確認用のテーブルを作成します。

mysql> create table DemoTable
    (
    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Value int
    );
Query OK, 0 rows affected (0.63 sec)

INSERTコマンドでレコードを追加します。

mysql> insert into DemoTable(Value) values(100);
Query OK, 1 row affected (0.13 sec)

SELECT文で登録内容を確認してみましょう。

mysql> select *from DemoTable;

出力結果

+----+-------+
| Id | Value |
+----+-------+
|  1 |   100 |
+----+-------+
1 row in set (0.00 sec)

問題の確認：UPDATE後に変数値が変わってしまうケース

次のストアドプロシージャは、ユーザー定義変数（@myValue）をテーブルから都度再取得して代入しているため、UPDATEを実行した後に変数値が変わってしまいます。

mysql> DELIMITER //
mysql> CREATE PROCEDURE updateValue100()
BEGIN
   DECLARE myValue int;
   select @myValue :=(select Value from DemoTable where Id=1);
   select @myValue;
   update DemoTable set Value=200 where Id=1;
   select @myValue :=(select Value from DemoTable where Id=1);
   select @myValue;
END
//
Query OK, 0 rows affected (0.21 sec)
mysql> DELIMITER ;

CALLコマンドでこのプロシージャを呼び出します。

mysql> call updateValue100();

出力結果

+-------------------------------------------------------+
| @myValue :=(select Value from DemoTable where Id=1)   |
+-------------------------------------------------------+
| 100                                                   |
+-------------------------------------------------------+
1 row in set (0.00 sec)

+----------+
| @myValue |
+----------+
| 100      |
+----------+
1 row in set (0.01 sec)

+-------------------------------------------------------+
| @myValue :=(select Value from DemoTable where Id=1)   |
+-------------------------------------------------------+
| 200                                                   |
+-------------------------------------------------------+
1 row in set (0.16 sec)

+----------+
| @myValue |
+----------+
| 200      |
+----------+
1 row in set (0.17 sec)

Query OK, 0 rows affected (0.18 sec)

ご覧のとおり、UPDATE後に再度代入を行ったため、@myValue の値は 100 から 200 に変わってしまいました。

解決策：DECLAREによるローカル変数と SELECT〜INTO を使う

変数値が書き換わらないようにするポイントは次の2点です。

• 「@変数名」形式のユーザー定義変数は、代入するたびに値が上書きされるため、更新後の値を再取得すると以前の値は失われる。
• DECLAREで宣言したローカル変数に対して「SELECT ... INTO」で一度だけ値を格納すれば、その後テーブルを更新しても変数の中身は影響を受けない。

これらを踏まえて書き直したプロシージャがこちらです。

mysql> DELIMITER //
mysql> CREATE PROCEDURE keepOldValue()
BEGIN
   DECLARE myValue int;
   SELECT Value INTO myValue FROM DemoTable WHERE Id = 1;
   SELECT myValue AS old_value;
   UPDATE DemoTable SET Value = 200 WHERE Id = 1;
   SELECT myValue AS old_value;
END
//
mysql> DELIMITER ;

このプロシージャでは、最初に読み込んだ時点の値（100）だけをローカル変数に格納しています。そのため、UPDATE後に変数を参照しても、値は元のまま維持されます。

+-----------+
| old_value |
+-----------+
|       100 |
+-----------+
1 row in set (0.00 sec)

+-----------+
| old_value |
+-----------+
|       100 |
+-----------+
1 row in set (0.00 sec)

まとめ

ストアドプロシージャ内である時点の値を固定して保持したい場合は、ユーザー定義変数を繰り返し代入するのではなく、DECLAREで宣言したローカル変数へ「SELECT ... INTO」で一度だけ値をコピーするのが安全です。これにより、後続のUPDATE処理の影響を受けることなく、取得時点の値を使い続けられます。