Oracle 5.0, Reference Manual

Page: 782 / 3232

User-Defined Variables
762
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 as of MySQL 5.0.3. (This is the same
coercibility as for table column values.)
Bit values assigned to user variables are treated as binary strings. To assign a bit value as a number to
a user variable, use
CAST()
[948] or
+0
:
mysql> SET @v1 = b'1000001';
mysql> SET @v2 = CAST(b'1000001' AS UNSIGNED), @v3 = b'1000001'+0;
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 N LINES
clause of a
LOAD DATA
statement.
As a general rule, you should never assign a value to a user variable and read the value within
the same statement. You might get the results you expect, but this is not guaranteed. The order
of evaluation for expressions involving user variables is undefined and may change based on the
elements contained within a given statement; in addition, this order is not guaranteed to be the same
between releases of the MySQL Server. In
SELECT @a, @a:=@a+1, ...
, you might think that
MySQL will evaluate
@a
first and then do an assignment second. However, changing the statement
(for example, by adding a
GROUP BY
,
HAVING
, or
ORDER BY
clause) may cause MySQL to select an
execution plan with a different order of evaluation.
Another issue with assigning a value to a variable and reading the value within the same 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