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
@, where the
var_name may consist of
alphanumeric characters from the current character set,
$’. The default character set is
latin1 (cp1252 West European). This may be
changed with the
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
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
:= can be used as the assignment operator. The
expr assigned to each variable can
evaluate to an integer, real, string, or
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
SET. In this case, the assignment operator
:= and not
= is treated as a comparison operator in
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
clause of a
SELECT statement, or the
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
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:
SELECT (@aa:=id) AS a, (@aa+3) AS b FROM
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
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:
SELECT @a,(@a:=20) FROM
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
@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
'' to define its type before you use it.
If you refer to a variable that has not been initialized, it has a
NULL and a type of string.