DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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