MySQL :: MySQL 8.0 リファレンスマニュアル

MySQL 8.0 リファレンスマニュアル / 言語構造 / ユーザー定義変数

9.4 ユーザー定義変数

あるステートメントのユーザー定義変数に値を格納し、後で別のステートメントで参照できます。これにより、あるステートメントから別のステートメントに値を渡すことができます。

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

ユーザー定義変数はセッション固有です。あるクライアントで定義されたユーザー変数は、他のクライアントでは表示または使用できません。 (例外: パフォーマンススキーマ user_variables_by_thread テーブルへのアクセス権を持つユーザーは、すべてのセッションのすべてのユーザー変数を表示できます。) 所定のクライアントセッションのすべての変数は、クライアントが終了すると自動的に解放されます。

ユーザー変数名では大/小文字は区別されません。名前の最大長は 64 文字です。

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

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

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

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

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

ユーザー変数に割り当てられた 16 進数またはビット値は、バイナリ文字列として扱われます。 16 進数またはビット値を数値としてユーザー変数に割り当てるには、数値コンテキストで使用します。たとえば、0 を追加するか、CAST(... AS UNSIGNED) を使用します:

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

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

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

MySQL 8.0.22 以降、プリペアドステートメント内のユーザー変数への参照のタイプは、ステートメントが最初に準備されたときに決定され、それ以降にステートメントが実行されるたびにこのタイプが保持されます。同様に、ストアドプロシージャ内のステートメントで使用されるユーザー変数の型は、ストアドプロシージャが最初に起動されたときに決定され、後続の起動のたびにこの型が保持されます。

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

以前のリリースの MySQL では、SET 以外のステートメントでユーザー変数に値を割り当てることができました。この機能は、下位互換性のために MySQL 8.0 でサポートされていますが、MySQL の将来のリリースで削除される予定です。

この方法で割当てを行う場合は、:= を割当て演算子として使用する必要があります。= は、SET 以外のステートメントで比較演算子として扱われます。

ユーザー変数を含む式の評価順序が定義されていません。たとえば、SELECT @a, @a:=@a+1 が最初に@a を評価してから割当てを実行する保証はありません。

また、変数のデフォルトの結果タイプは、ステートメントの先頭のタイプに基づきます。変数がステートメントの先頭にあるあるある型の値を保持していて、別の型の新しい値も割り当てられている場合、これは意図しない影響を与える可能性があります。

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

HAVING、GROUP BY および ORDER BY では、選択式リストの値が割り当てられている変数を参照する場合、式はクライアントで評価されるため、予期したとおりに機能しないため、前の行の失効したカラム値を使用できます。

ユーザー変数は、データ値を提供するためのものです。これらは、テーブル名やデータベース名が想定されるコンテキストなどでの識別子または識別子の一部として、または 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「プリペアドステートメント」,をご覧ください。

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」と呼ぶことがあります。

関連キーワード: 　変数, ユーザー, ステートメント, 定義, col, リテラル, 識別子, rows, リファレンス, var

PREV HOME UP NEXT