Variable assignment in mysql, mysql variable assignment

Variable assignment in mysql, mysql variable assignment

Http://www.cnblogs.com/qixuejia/archive/2010/12/21/1913203.html

 

Variables in SQL server must be affirmed first and then assigned values:

The local variables are identified with one @ and the global variables with two @ (common global variables are usually defined );

Declare the local variable Syntax: declare @ variable name data type; for example, declare @ num int;

Value assignment: There are two methods (@ num is the variable name and value is the value)

Set @ num = value; or select @ num = value;

If you want to obtain a field value in the query statement, you can use select to assign values to the variable, as shown below:

Select @ num = field name from table name where ......

Mysql variables do not need to be stated beforehand. You can simply use "@ variable name" when using them.

First usage: set @ num = 1; or set @ num: = 1; // use the variable to save the data. Use the @ num variable directly.

Second usage: select @ num: = 1; or select @ num: = field name from table name where ......

Note that the above two values can be set with "=" or ": =", but ": =" must be used for select"

 

========================

Http://dev.mysql.com/doc/refman/5.7/en/user-variables.html

 

User-Defined Variables

You can store a value in a user-defined variable in one statement and then refer to it later in another statement. This enables you to pass values from one statement to another.

User variables are written@var_name, Where the variable namevar_nameConsists of alphanumeric characters,".","_", And"$". A user variable name can contain in 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. (Exception: A user with access to the Performance Schemauser_variables_by_threadTable can see all user variables for all sessions.) All variables for a given client session are automatically freed when that client exits.

User variable names are not case sensitive. Names have a maximum length of 64 characters as of MySQL 5.7.5. (Length is not constrained before that .)

One way to set a user-defined variable is by issuingSETStatement:

SET @var_name = expr [, @var_name = expr] ...

ForSET, Either=Or:=Can be used as the assignment operator.

You can also assign a value to a user variable in statements otherSET. In this case, the assignment operator must be:=And not=Because the latter is treated as the comparison operator=In non-SETStatements:

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 |+------+------+------+--------------------+

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, orNULLValue. 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. A value havingJSONData type is converted to a string with a character setutf8mb4And a collationutf8mb4_bin.

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 .)

Bit values assigned to user variables are treated as binary strings. To assign a bit value as a number to a user variable, useCAST()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 valueNULLAnd 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 inLIMITClause ofSELECTStatement, orIGNORE N LINESClause ofLOAD DATAStatement.

As a general rule, other than inSETStatements, you shoshould 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, suchSELECT, You might get the results you have CT, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate@aFirst and then do 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-SETStatement is that the default result type of a variable is based on its type at the start of the statement. The following example extends strates this:

mysql> SET @a='test';mysql> SELECT @a,(@a:=20) FROM tbl_name;

For thisSELECTStatement, MySQL reports to the client that column one is a string and converts all accesses@aTo strings, even though @ a is set to a number for the second row. AfterSELECTStatement executes,@aIs 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 variable0,0.0, Or''To define its type before you use it.

InSELECTStatement, each select expression is evaluated only when sent to the client. This means that inHAVING,GROUP BY, OrORDER BYClause, referring to a variable that is assigned a value in the select expression list doesNotWork as expected:

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The referencebInHAVINGClause refers to an alias for an expression in the select list that uses@aa. This does not work as expected:@aaContains the valueidFrom 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 suchSELECT. 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 extends strates 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 preparedmysql> EXECUTE stmt;+----+| c1 |+----+|  0 |+----+|  1 |+----+2 rows in set (0.00 sec)mysql> DEALLOCATE PREPARE stmt;Query OK, 0 rows affected (0.00 sec)