User-Defined Variables Like @x

MySQL Tutorials - Herong's Tutorial Examples

∟System, User-Defined and Stored Procedure Variables

∟User-Defined Variables Like @x

This section provides a quick introduction on user-defined variables, which are used by client sessions to carry information from one command to other later commands.

What Are User-Defined Variables? User-Defined Variables are variables used in client sessions to carry information from one command to other later commands.

To refer to a user-defined variable, you must prefix its name with "@".

There are several ways to use a user-defined variable:

In a "SET" command to assign new values to variables.
In a "SELECT ... INTO" command to assign new values to variables.
In an operation as an operand.

Here are some examples on using user-defined variables.

mysql> set @x := 'HerongYang.com';
Query OK, 0 rows affected (0.00 sec)

mysql> select lower(@domain) into @y;
Query OK, 1 row affected (0.00 sec)

mysql> select @y;
+----------------+
| @y             |
+----------------+
| herongyang.com |
+----------------+

Assignment operation can also be used in the SELECT clause to update variables on the fly.

mysql> set @i := 1;

mysql> select (@i:=@i+1) as i;
+------+
| i    |
+------+
|    2 |
+------+

mysql> select (@i:=@i+1) as i;
+------+
| i    |
+------+
|    3 |
+------+