(mysql.info.gz) Variables
Info Catalog
(mysql.info.gz) Legal names
(mysql.info.gz) Language Structure
(mysql.info.gz) System Variables
9.3 User Variables
==================
MySQL supports user variables as of version 3.23.6. You can store a
value in a user variable and refer to it later, which allows you to
pass values from one statement to another. User variables are
connection-specific. That is, a variable defined by one client cannot
be seen or used by other clients. All variables for a client
connection are automatically freed when the 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
ISO-8859-1 (Latin1). This may be changed with the
`--default-character-set' option to `mysqld'. Character sets.
User variable names are not case sensitive beginning with MySQL 5.0.
Before that, they are case sensitive.
One way to set a user 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 where expressions are allowed. This does
not currently include contexts that explicitly require a number, such
as in the `LIMIT' clause of a `SELECT' statement, or the `IGNORE number
LINES' clause of a `LOAD DATA' statement.
If you refer to a variable that has not been initialized, its value is
`NULL'.
* 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 will _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' will not contain the value of the current row, but the
value of `id' from the previous selected row.
The general rule is to never assign _and_ use the same variable in the
same statement.
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 will report to the client that
column one is a string and convert all accesses of `@a' to strings,
even though @a is set to a number for the second row. After the
`SELECT' statement executes, `@a' will be 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.
An unassigned variable has a value of `NULL' with a type of string.
Info Catalog
(mysql.info.gz) Legal names
(mysql.info.gz) Language Structure
(mysql.info.gz) System Variables
automatically generated byinfo2html