User-Defined Variables

User-defined variables (or, more simply, "user variables") can be assigned values, and you can refer to those variables in other statements later.

User-defined variable names begin with '@' and may consist of alphanumeric characters from the current character set, and the '_', '$', and '.' characters. User variable names are case sensitive before MySQL 5.0, and not case sensitive from 5.0.

User variables can be assigned values 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 member;
+--------------------+
| @count := COUNT(*) |
+--------------------+
|                102 |
+--------------------+

User variables can be assigned numeric, string, or NULL values, and can be assigned from arbitrary expressions, including those that refer to other variables. If you access a user variable that has not yet been assigned a value explicitly, its value is NULL.

User 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.

As of MySQL 4.1.1, string-valued user variables have the same character set and collation as those of the value they are assigned:

mysql> SET @s = CONVERT('abc' USING latin2) COLLATE latin2_czech_cs;
mysql> SELECT CHARSET(@s), COLLATION(@s);
+-------------+-----------------+
| CHARSET(@s) | COLLATION(@s)   |
+-------------+-----------------+
| latin2      | latin2_czech_cs |
+-------------+-----------------+