MYSQL User Defined Variables

In MySQL, you can define your own variables to use in queries. You can store user defined variables in one statement and refer to it in another statement. User defined variables are specfic to a connection session. That is, a user variable defined in one session cannot be seen or used in another session. All variables for a given client connection are automatically freed when that client exits.

User variables are written as @var, where the variable name "var" consists of alphanumeric characters from the current character set, “.”, “_”, and “$”. One way to establish user variables is through a Set statement as follows:

SET @var = expr [, @var = 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. Here is a simple example of how user-defined variables could be applied:

mysql> set @var1=2,@var1=4,@var3=6;
Query OK, 0 rows affected (0.00 sec)
mysql> select @var1,@var2,@var3,@var4:=@var1+@var2+@var3;

The above select statement will return values of 2,4,6,12.