User-Defined Variables

You can store a value in a user-defined variable in one statement and then
refer to it later in another statement. This enables you to pass values from one
statement to another. User-defined variables are connection-specific.
That is, a user variable defined by one client cannot be seen or used by other clients.
All variables for a given client connection are automatically freed when that client exits.

In a SET statement, you may use either the= or the := operator to assign a
value to a user variable.
In a SELECT statement, you have to use the := operator for assigning a value to a variable.

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

For SET, either = or := can be used as the assignment operator.
You can also assign a value to a user variable in statements other than SET.
In this case, the assignment operator must be := and not = because = is treated as a
comparison operator in non-SET statements.

SETでは, 「 =:= 」の2パターンで変数を割り当てられる。
SELECTでは, 「 := 」のみ利用出来る。 「 = 」を利用すると比較になってしまう。

    ○ 正しい例


X 間違い例

User variable names are not case sensitive

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

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

mysql> select @a,@A,@a+@A;


continuing the session

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

mysql> select @a,@A,@a+@A;


When the

    session ends, all its user variables are lost

, and user variables are

    specific to the client connection.

MYSQL user defined variable (ユーザー定義変数)