9.3. User-Defined Variables
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.
User variables are written as
@var_name, where the
variable name var_name may consist of
alphanumeric characters from the current character set,
‘.’,
‘_’, and
‘$’. The default character set is
latin1 (cp1252 West European). This may be
changed with the --default-character-set option
to mysqld. See
Section 5.10.1, “The Character Set Used for Data and Sorting”. A user variable name can contain
other characters if you quote it as a string or identifier (for
example, @'my-var',
@"my-var", or @`my-var`).
Note: User variable names are case sensitive before MySQL 5.0 and
not case sensitive in MySQL 5.0 and up.
One way to set a user-defined variable is by issuing a
SET statement:
SET @var_name = expr [, @var_name = expr] ...
For SET, either = or
:= can be used as the assignment operator. The
expr assigned to each variable can
evaluate to an integer, real, string, or NULL
value.
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> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 |
+----------------------+------+------+------+
| 5 | 5 | 1 | 4 |
+----------------------+------+------+------+
User variables may be used in contexts where expressions are
allowed. This does not currently include contexts that explicitly
require a literal value, such as in the LIMIT
clause of a SELECT statement, or the
IGNORE N LINES
clause of a LOAD DATA statement.
If a user variable is assigned a string value, it also has the
same character set and collation as the string. The coercibility
of user variables is implicit. (This is the same coercibility as
for table column values.)
Note: In a
SELECT statement, each expression is evaluated
only when sent to the client. This means that in a
HAVING, GROUP BY, or
ORDER BY clause, you cannot refer to an
expression that involves variables that are set in the
SELECT list. For example, the following
statement does not work as expected:
mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;
The reference to b in the
HAVING clause refers to an alias for an
expression in the SELECT list that uses
@aa. This does not work as expected:
@aa contains the value of id
from the previous selected row, not from the current row.
The general rule is to never assign a value to a user variable in
one part of a statement and use the same
variable in some other part the same statement. You might get the
results you expect, but this is not guaranteed.
Another issue with setting a variable and using it in the same
statement is that the default result type of a variable is based
on the type of the variable at the start of the statement. The
following example illustrates this:
mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;
For this SELECT statement, MySQL reports to the
client that column one is a string and converts all accesses of
@a to strings, even though @a is set to a
number for the second row. After the SELECT
statement executes, @a is regarded as a number
for the next statement.
To avoid problems with this behavior, either do not set and use
the same variable within a single statement, or else set the
variable to 0, 0.0, or
'' to define its type before you use it.
If you refer to a variable that has not been initialized, it has a
value of NULL and a type of string.
