第255回　ユーザー定義変数を使ってみよう

MySQL には「ユーザー定義変数（User-defined variables⁠）⁠」と呼ばれる仕組みがあります。@変数名という形で表記でき、セッションごとにスコープを持つため、一度代入した値は接続が続く限り利用できますが、他のセッションから参照することはできません。小さな計算やちょっとした結果の再利用に役立ち、古くから多くのユーザーに親しまれてきました。

今回はこのユーザー定義変数の利用について見ていきましょう。

ユーザー定義変数

ユーザー定義変数は以下のように@変数名 = 値または@変数名 := 値の形式で代入します。定義したユーザー変数はSELECT @変数名で確認できますし、WHERE句の条件としてWHERE id >= @変数名のように利用することも可能です。

変数名は大文字小文字を区別せず、英数字に加えて_、.、$を利用できます。それ以外の文字を含めたい場合は、文字列や識別子としてクオートすれば使用可能です。

なお、変数はあくまで「値」を保持するものであり、WHERE句全体を入れて再利用するような使い方はできません。そのような場合は後述する動的SQLを利用します。

mysql> SET @x = 10;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x + 5;
+--------+
| @x + 5 |
+--------+
|     15 |
+--------+
1 row in set (0.00 sec)

これだけで単純な計算を実行でき、セッション内では同じ値を使い回せます。外部スクリプトやアプリケーションに持ち出さずとも、SQLの中で軽い処理を済ませられるのが便利な点です。

代入に使う演算子については注意が必要です。SET文では=も:=も代入として使えますが、SELECT文など式の中では:=を利用すべきです。=は比較演算子として解釈されるため、次のように意図しない結果になることがあります。

mysql> SET @n = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @n = @n + 1;
+-------------+
| @n = @n + 1 |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

ユーザー変数への代入はSET以外にもSELECT ... INTOを使う方法があります。クエリの結果をそのまま変数に代入できるので便利です。

SELECT id, name INTO @uid, @uname FROM users WHERE id = 1001;

ただし、1行しか返さないことが前提です。複数行が返ると以下のエラーになってしまうので注意してください。

ERROR 1172 (42000): Result consisted of more than one row

ユーザー定義変数でできること

ユーザー定義変数はちょっとした工夫に役立ちます。代表的な利用例を挙げてみます。

集計結果の保持

SELECT COUNT(*) INTO @cnt FROM t_sales;
SELECT CONCAT('全件数は', @cnt, '件です');

集計結果を一時的に保存して使い回す方法です。以下ではt_salesカウントの結果を@cntに保存して表示しています。クエリで取得した件数を変数に入れておけば、以降のクエリで何度も参照できます。複雑な集計を何度も繰り返す必要がなく、SQLを簡潔に保てるという利点があります。

次に、動的SQLの生成です。テーブル名を変数として保持し、それを使ってSQL文を組み立てられます。

動的SQLの生成

SET @tbl := 't_sales';
SET @sql := CONCAT('SELECT COUNT(*) FROM ', @tbl);
PREPARE stmt FROM @sql;
EXECUTE stmt;

このようにすれば、テーブル名や条件を外部から渡して実行することも可能になります。

また、ユーザー定義変数を使って「行番号」や「累積合計」を出力するやり方もあります。行番号を振る場合は次のように書けます。

行番号の生成

SET @rn := 0;
SELECT id, @rn := @rn + 1 AS rownum FROM t_sales ORDER BY id;

累積合計を計算する場合も同様の書き方で実現できます。

累積合計の出力

SET @sum := 0;
SELECT id, amount,@sum := @sum + amount AS running_total FROM (SELECT * FROM t_sales ORDER BY id) s;

このように、ユーザー定義変数を利用すれば、ちょっとした工夫でアプリケーションに頼らずSQLの中だけで処理を完結させることができます。

とはいえSELECT句内での代入はDeprecatedに

ユーザー定義変数の利用には注意すべき点があります。上記のように SELECT句の中で@var := ...とする書き方は、MySQL 8.0.13 から非推奨（Deprecated）となっています。

実際にこの書き方をすると、次のような警告が表示されます。

mysql> select @var := 5;
+-----------+
| @var := 5 |
+-----------+
|         5 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: Setting user variables within expressions is deprecated and will
 be removed in a future release. Consider alternatives: 'SET 
variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
1 row in set (0.00 sec)

では、SELECT句で値を入れたい場合はどのように記述すれば良いでしょうか。単純な代入にはSETやSELECT ... INTOを使えば問題ありません。これらは今後も利用可能です。一方で、行番号や累積合計といった順序を伴う処理には、MySQL 8.0から導入されたウィンドウ関数を利用するのが望ましいでしょう。

performance_schemaからユーザー定義変数を覗く

ユーザー定義変数は通常はセッションごとに閉じており、他の接続からは参照できません。しかし、performance_schema.user_variables_by_threadを利用すると、どのスレッド（＝セッション）でどんなユーザー変数が利用されているかを観察できます。

mysql> SELECT *, CONVERT(VARIABLE_VALUE USING utf8mb4) AS var_value FROM performance_schema.user_variables_by_thread;

このテーブルには次のような情報が含まれています。

THREAD_ID：セッションを識別するスレッドID
VARIABLE_NAME：ユーザー変数名
VARIABLE_VALUE：現在の値

これを使えば、たとえば「特定のアプリケーションがどんなユーザー変数を利用しているか」「⁠不要に変数が残っていないか」といった調査が可能です。

まとめ

ユーザー定義変数自体は依然として便利な仕組みであり、集計値の一時保存や動的SQLの生成など、実用的な場面で利用し続けられます。しかし、SELECT句内の代入による小技は非推奨であり、すでに警告が出る状態になっています。今後も使い続けると、将来のバージョンで動かなくなるリスクがある点に注意してください。

ユーザー定義変数は、MySQLの歴史の中で多くの人に使われてきた便利な道具です。しかし今は標準SQLとしてより整ったウィンドウ関数が存在し、互換性や信頼性の観点からもそちらに移行すべき時代になっています。古いコードを読み解くために知識として押さえつつ、新しい開発では正しい機能を積極的に活用していきましょう。

より詳細については公式ドキュメントを参考にしてください。

MySQL :: MySQL 8.4 Reference Manual :: 11.4 User-Defined Variables