SQL Variables

As of MySQL 3.23.6, user-defined SQL variables can be assigned values, and you can refer to those variables in other statements later.

User-defined variable names begin with '@' and can consist of alphanumeric characters from the current character set, and the '_', '$', and '.' characters. Variable names are case sensitive.

Variables can be assigned with the = or := operators in SET statements or with the := operator in other statements, such as SELECT. Multiple assignments can be performed in a single statement.

mysql> SET @x = 0, @y = 2; 
mysql> SET @color := 'red', @size := 'large';
mysql> SELECT @x, @y, @color, @size;
+------+------+--------+-------+
| @x   | @y   | @color | @size |
+------+------+--------+-------+
|    0 |    2 | red    | large |
+------+------+--------+-------+
mysql> SELECT @count := COUNT(*) FROM president;
+--------------------+
| @count := COUNT(*) |
+--------------------+
|                 42 |
+--------------------+

Variables can be assigned numeric, string, or NULL values, and can be assigned from arbitrary expressions, including those that refer to other variables.

Variables have a value of NULL until explicitly assigned a value. The values do not persist across sessions with the server. That is, values are lost when a connection terminates.

In SELECT statements that return multiple rows, variable assignments are performed for each row. The final value is the value assigned for the last row.

In MySQL 4.1 and later, variables have the same character set as that for the value they are assigned:

mysql> SET @s = _latin1_de 'abc'; SELECT CHARSET(@s); 
+-------------+
| CHARSET(@s) |
+-------------+
| latin1_de   |
+-------------+