(mysql.info) user-variables

Info Catalog

(mysql.info) legal-names

(mysql.info) language-structure

(mysql.info) comments

 
 9.3 User-Defined Variables
 ==========================
 
 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 `@VAR_NAME', where the variable name
 VAR_NAME may consist of alphanumeric characters from the current
 character set, ‘`.'’, ‘`_'’, and ‘`$'’. The default
 character set is `latin1' (cp1252 West European). This may be changed
 with the -default-character-set option to `mysqld'. See 
 character-sets. 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`').
 
 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 `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 in contexts where expressions are allowed.
 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.
 
 If a user variable is assigned a string value, it also 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.)
 
 * 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 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.
 
 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:
 
      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 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.
 
 If you refer to a variable that has not been initialized, it has a
 value of `NULL' and a type of string.
 

Info Catalog

(mysql.info) legal-names

(mysql.info) language-structure

(mysql.info) comments