ストアドプロシージャを使ってMySQLの行の値を1ずつ増減させる方法

MySQLでは、ストアドプロシージャを利用することで、複数の行に対する値の更新処理をひとまとめにして実行できます。本記事では、ある行の値を1減らし（デクリメント）、別の行の値を1増やす（インクリメント）という処理を、ストアドプロシージャで実装する手順を解説します。

1. サンプルテーブルの作成

まず、値を増減させるためのテーブルを作成します。以下がそのクエリです。

mysql> create table IncrementAndDecrementValue
   −> (
   −> UserId int,
   −> UserScores int
   −> );
Query OK, 0 rows affected (0.60 sec)

2. テストデータの挿入

次に、INSERTコマンドを使ってテーブルにレコードを挿入します。

mysql> insert into IncrementAndDecrementValue values(101,20000);
Query OK, 1 row affected (0.13 sec)

mysql> insert into IncrementAndDecrementValue values(102,30000);
Query OK, 1 row affected (0.20 sec)

mysql> insert into IncrementAndDecrementValue values(103,40000);
Query OK, 1 row affected (0.11 sec)

3. 挿入したデータの確認

SELECT文でテーブル内の全レコードを表示し、正しく登録されているか確認しましょう。

mysql> select *from IncrementAndDecrementValue;

実行結果は以下の通りです。

+--------+------------+
| UserId | UserScores |
+--------+------------+
|    101 |      20000 |
|    102 |      30000 |
|    103 |      40000 |
+--------+------------+
3 rows in set (0.00 sec)

4. ストアドプロシージャの作成

ここからが本題です。UserId=101 のスコアを1減らし、UserId=102 のスコアを1増やすストアドプロシージャを作成します。変数に現在の値を読み込み、それをもとにUPDATE文を実行する構造になっています。

mysql> delimiter //

mysql> create procedure IncrementAndDecrementRowValueByOne()
   −> begin
   −> declare first int;
   −> declare second int;
   −> set first = (select UserScores from IncrementAndDecrementValue where UserId = 101);
   −> set second = (select UserScores from IncrementAndDecrementValue where UserId = 102);
   −> update IncrementAndDecrementValue set UserScores = first-1 where UserId = 101;
   −> update IncrementAndDecrementValue set UserScores = second+1 where UserId = 102;
   −> end //
Query OK, 0 rows affected (0.17 sec)

mysql> delimiter ;

ポイントとして、ストアドプロシージャ内で複数のSQL文を定義する際は、あらかじめ delimiter // コマンドで区切り文字を変更しておく必要があります。作成が完了したら delimiter ; で元に戻すのを忘れないようにしましょう。

5. ストアドプロシージャの呼び出し

作成したストアドプロシージャは、CALLコマンドで実行できます。

mysql> call IncrementAndDecrementRowValueByOne();
Query OK, 1 row affected (0.24 sec)

6. 更新結果の確認

最後に、SELECT文で値が正しく更新されたかを確認します。

mysql> select *from IncrementAndDecrementValue;

実行結果は以下の通りです。

+--------+------------+
| UserId | UserScores |
+--------+------------+
|    101 |      19999 |
|    102 |      30001 |
|    103 |      40000 |
+--------+------------+
3 rows in set (0.00 sec)

結果を見ると、UserId=101 のスコアが 20000 から 19999 に1減り、UserId=102 のスコアが 30000 から 30001 に1増えていることがわかります。このように、ストアドプロシージャを使えば、複数の行にまたがる増減処理を一つの単位として簡潔に実行できます。