MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another.
User variables are written as
@
, where the
variable name var_name
var_name
consists of
alphanumeric characters, .
,
_
, and $
. 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`
).
User-defined variables are session specific. A user variable defined by one client cannot be seen or used by other clients. All variables for a given client session are automatically freed when that client exits.
User variable names are not case-sensitive.
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.
User variables can be assigned a value from a limited set of data
types: integer, decimal, floating-point, binary or nonbinary
string, or NULL
value. Assignment of decimal
and real values does not preserve the precision or scale of the
value. A value of a type other than one of the permissible types
is converted to a permissible type. For example, a value having a
temporal or spatial data type is converted to a binary string.
If a user variable is assigned a nonbinary (character) string value, it 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.)
Hexadecimal or bit values assigned to user variables are treated
as binary strings. To assign a hexadecimal or bit value as a
number to a user variable, use it in numeric context. For example,
add 0 or use CAST(... AS UNSIGNED)
:
mysql>SET @v1 = X'41';
mysql>SET @v2 = X'41'+0;
mysql>SET @v3 = CAST(X'41' AS UNSIGNED);
mysql>SELECT @v1, @v2, @v3;
+------+------+------+ | @v1 | @v2 | @v3 | +------+------+------+ | A | 65 | 65 | +------+------+------+ mysql>SET @v1 = b'1000001';
mysql>SET @v2 = b'1000001'+0;
mysql>SET @v3 = CAST(b'1000001' AS UNSIGNED);
mysql>SELECT @v1, @v2, @v3;
+------+------+------+ | @v1 | @v2 | @v3 | +------+------+------+ | A | 65 | 65 | +------+------+------+
If the value of a user variable is selected in a result set, it is returned to the client as a string.
If you refer to a variable that has not been initialized, it has a
value of NULL
and a type of string.
User variables may be used in most contexts where expressions are
permitted. 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
clause of a N
LINESLOAD DATA
statement.
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
the latter is treated as the comparison operator
=
in statements
other than
SET
:
mysql>SET @t1=1, @t2=2, @t3:=4;
mysql>SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+ | @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 | +------+------+------+--------------------+ | 1 | 2 | 4 | 7 | +------+------+------+--------------------+
As a general rule, other than in
SET
statements, you should never assign a value to a user variable and
read the value within the same statement. For example, to
increment a variable, this is okay:
SET @a = @a + 1;
For other statements, such as
SELECT
, you might get the results
you expect, but this is not guaranteed. In the following
statement, you might think that MySQL evaluates
@a
first and then performs an assignment
second:
SELECT @a, @a:=@a+1, ...;
However, the order of evaluation for expressions involving user variables is undefined.
Another issue with assigning a value to a variable and reading the
value within the same
non-SET
statement is that the default result type of a variable is based
on its type 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 assign a value
to and read the value of 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.
In a SELECT
statement, each select
expression is evaluated only when sent to the client. This means
that in a HAVING
, GROUP BY
,
or ORDER BY
clause, referring to a variable
that is assigned a value in the select expression list 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.
User variables are intended to provide data values. They cannot be
used directly in an SQL statement as an identifier or as part of
an identifier, such as in contexts where a table or database name
is expected, or as a reserved word such as
SELECT
. This is true even if the
variable is quoted, as shown in the following example:
mysql>SELECT c1 FROM t;
+----+ | c1 | +----+ | 0 | +----+ | 1 | +----+ 2 rows in set (0.00 sec) mysql>SET @col = "c1";
Query OK, 0 rows affected (0.00 sec) mysql>SELECT @col FROM t;
+------+ | @col | +------+ | c1 | +------+ 1 row in set (0.00 sec) mysql>SELECT `@col` FROM t;
ERROR 1054 (42S22): Unknown column '@col' in 'field list' mysql> SET @col = "`c1`"; Query OK, 0 rows affected (0.00 sec) mysql>SELECT @col FROM t;
+------+ | @col | +------+ | `c1` | +------+ 1 row in set (0.00 sec)
An exception to this principle that user variables cannot be used to provide identifiers, is when you are constructing a string for use as a prepared statement to execute later. In this case, user variables can be used to provide any part of the statement. The following example illustrates how this can be done:
mysql>SET @c = "c1";
Query OK, 0 rows affected (0.00 sec) mysql>SET @s = CONCAT("SELECT ", @c, " FROM t");
Query OK, 0 rows affected (0.00 sec) mysql>PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.04 sec) Statement prepared mysql>EXECUTE stmt;
+----+ | c1 | +----+ | 0 | +----+ | 1 | +----+ 2 rows in set (0.00 sec) mysql>DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
See Section 13.5, “Prepared Statements”, for more information.
A similar technique can be used in application programs to construct SQL statements using program variables, as shown here using PHP 5:
<?php $mysqli = new mysqli("localhost", "user", "pass", "test"); if( mysqli_connect_errno() ) die("Connection failed: %s\n", mysqli_connect_error()); $col = "c1"; $query = "SELECT $col FROM t"; $result = $mysqli->query($query); while($row = $result->fetch_assoc()) { echo "<p>" . $row["$col"] . "</p>\n"; } $result->close(); $mysqli->close(); ?>
Assembling an SQL statement in this fashion is sometimes known as “Dynamic SQL”.