9.4 ユーザー定義変数

あるステートメント内のユーザー定義変数に値を格納し、あとから別のステートメントでこれを参照できます。これにより、あるステートメントから別のステートメントに値を渡すことができます。ユーザー定義変数はセッション固有です。つまり、あるクライアントが定義したユーザー変数を、ほかのクライアントが表示したり、使用したりすることはできません。所定のクライアントセッションのすべての変数は、クライアントが終了すると自動的に解放されます。

ユーザー変数は @var_name と記述されます。ここで変数名 var_name は、英数字文字、「.」、「_」、および「$」から構成されます。ユーザー変数名を文字列や識別子として引用符で囲めば、ほかの文字も含めることができます (@'my-var'、@"my-var"、@`my-var` など)。

ユーザー変数名では大文字と小文字を区別しません。

ユーザー定義変数を設定する方法の 1 つに、SET ステートメントを発行する方法が挙げられます。

SET @var_name = expr [, @var_name = expr] ...

SET では、= または := のどちらかを割り当て演算子として使用できます。

SET 以外のステートメントで、ユーザー変数に値を割り当てることもできます。この場合、= は SET 以外のステートメントでは比較演算子 = として扱われるので、割り当て演算子にはこちらではなく、:= を使用する必要があります。

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 | 
+------+------+------+--------------------+

ユーザー変数には、限定された一連のデータ型の値 (整数、小数、浮動小数点、バイナリ文字列、非バイナリ文字列、または NULL 値) を割り当てることができます。10 進値と実数値の割り当てでは、値の精度やスケールは維持されません。許可されている型以外の型の値は、許可されている型に変換されます。たとえば、時間を表すデータ型や空間データ型の値は、バイナリ文字列に変換されます。

ユーザー変数に非バイナリ (文字) 文字列値を割り当てた場合、その変数には文字列と同じ文字セットと照合順序が含まれます。ユーザー変数の強制性は暗黙的です。(これはテーブルカラム値と同等の強制性です。)

ユーザー変数に割り当てられたビット値は、バイナリ文字列として扱われます。ビット値を数値としてユーザー変数に割り当てるには、CAST() または +0 を使用します。

mysql> SET @v1 = b'1000001';
mysql> SET @v2 = CAST(b'1000001' AS UNSIGNED), @v3 = b'1000001'+0;
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+

結果セットでユーザー変数の値が選択された場合、それは文字列としてクライアントに返されます。

初期化されていない変数を参照する場合、その値は NULL で、型は文字列です。

ユーザー変数は、式が許可されているほとんどのコンテキストで使用できます。これには現在、SELECT ステートメントの LIMIT 句の中や、LOAD DATA ステートメントの IGNORE N LINES 句の中など、リテラル値を明示的に要求するコンテキストは含まれません。

一般的なルールとして、SET ステートメント以外では、同じステートメント内で、ユーザー変数に値を割り当ててその値を読み取ることは決してしないでください。たとえば、変数を増分する場合、次のようにしても問題ありません。

SET @a = @a + 1;

SELECT などのほかのステートメントでは、予想した結果が得られることもありますが、これは保証されません。次のステートメントでは、MySQL が最初に @a を評価し、続いて 2 番目の割り当てを行います。

SELECT @a, @a:=@a+1, ...;

ただし、ユーザー変数を含む式の評価の順序は、定義されていません。

SET 以外の同じステートメント内で変数に値を割り当てて、その値を読み取る場合に生じるもう 1 つの問題は、変数のデフォルトの結果型がステートメントの開始時の型に基づくということです。次の例でこれについて説明します。

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

この SELECT ステートメントの場合、MySQL は、カラム 1 が文字列であり、@a が 2 行目の数値に設定されていても、@a のすべてのアクセスを文字列に変換すると、クライアントにレポートします。SELECT ステートメントの実行後、@a は次のステートメントの数値と見なされます。

この動作による問題を回避するには、単一のステートメント内で同じ変数に値を割り当ててその値を読み取ることを行わないか、使用する前に変数を 0、0.0、または '' に設定して、その型を定義してください。

SELECT ステートメントでは、それぞれの選択式は、クライアントに送信されるときにのみ評価されます。つまり、HAVING、GROUP BY、または ORDER BY 句では、選択式リストで値を割り当てられた変数を参照しても、予想どおりには機能しないということです。

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

HAVING 句での b の参照は、@aa を使用する選択リスト内の式のエイリアスを参照します。これは予想どおりには機能しません。@aa には、現在の行ではなく、以前に選択した行の id の値が含まれます。

ユーザー変数は、データ値を提供するためのものです。これらは、テーブル名やデータベース名が想定されるコンテキストなどでの識別子または識別子の一部として、または SELECT などの予約語として、SQL ステートメントの中で直接使用することはできません。これは、次の例に示すように、変数が引用符で囲まれている場合でも同じです。

mysql> SELECT c1 FROM t;
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql> SET @col = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @col FROM t;
+------+
| @col |
+------+
| c1   |
+------+
1 row in set (0.00 sec)

mysql> SELECT `@col` FROM t;
ERROR 1054 (42S22): Unknown column '@col' in 'field list'

mysql> SET @col = "`c1`";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @col FROM t;
+------+
| @col |
+------+
| `c1` |
+------+
1 row in set (0.00 sec)

識別子を提供するためにユーザー変数を使用できないというこの原則の例外が、あとから実行する準備されたステートメントとして使用するために文字列を構築している場合です。この場合、ユーザー変数はステートメントの一部を提供するために使用できます。次の例は、その方法を示しています。

mysql> SET @c = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @s = CONCAT("SELECT ", @c, " FROM t");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql> EXECUTE stmt;
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

詳細は、セクション13.5「準備済みステートメントのための SQL 構文」を参照してください。

PHP 5 を使用して次に示すように、同じような手法をアプリケーションプログラムの中で使用することによって、プログラム変数を使用する SQL ステートメントを作成できます。

<?php
  $mysqli = new mysqli("localhost", "user", "pass", "test");

  if( mysqli_connect_errno() )
    die("Connection failed: %s\n", mysqli_connect_error());

  $col = "c1";

  $query = "SELECT $col FROM t";

  $result = $mysqli->query($query);

  while($row = $result->fetch_assoc())
  {
    echo "<p>" . $row["$col"] . "</p>\n";
  }

  $result->close();

  $mysqli->close();
?>

この方法で SQL ステートメントを作成することを「ダイナミック SQL」と呼ぶことがあります。