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.11.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. However, if the value of the variable is selected in a
result set, it is returned to the client as a string.
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 has the same character set and collation as the string. The coercibility of user variables is implicit as of MySQL 5.0.3. (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 FROMtbl_nameHAVING 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 order of evaluation for user variables is undefined and may
change based on the elements contained within a given query. In
SELECT @a, @a := @a+1 ..., you might think that
MySQL will evaluate @a first and then do an
assignment second, but changing the query (for example, by adding
a GROUP BY, HAVING, or
ORDER BY clause) may change the order of
evaluation.
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) FROMtbl_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.