You can store a value in a user-defined variable and then refer to it later.
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.

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:


mysql> select @a1;
+-------+
| @a1 |
+-------+
| test1 |
+-------+
1 row in set (0.00 sec)

mysql> SET @a2 := 'test2';
Query OK, 0 rows affected (0.00 sec)

mysql> select @a2;
+-------+
| @a2 |
+-------+
| test2 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT @a3 := 'test3';
+----------------+
| @a3 := 'test3' |
+----------------+
| test3 |
+----------------+
1 row in set (0.00 sec)

mysql> select @a3;
+-------+
| @a3 |
+-------+
| test3 |
+-------+
1 row in set (0.00 sec)

mysql>

variables

User variables can be assigned a value from a limited set of data types:
integer, decimal, floating-point, binary or nonbinary string, or NULL value.

mysql> SET @t1=1, @t2=2, @t3:=4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
| 1 | 2 | 4 | 7 |
+------+------+------+--------------------+
1 row in set (0.00 sec)

mysql>

variables2


mysql> SET @t1='test', @t2='-OK';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @t1, @t2:= concat(@t1,@t2);
+------+-----------------------+
| @t1 | @t2:= concat(@t1,@t2) |
+------+-----------------------+
| test | test-OK |
+------+-----------------------+
1 row in set (0.00 sec)

mysql>

variables3

※ ユーザーVariableは、大文字、小文字の区別はしないので以下のような結果になる。
variable_not_case_sensitive

参考サイト
8.4. User-Defined Variables
MySQL独自のENUM・SET型を使ってみよう