MySQL User custom variables

You can first save the value in a user variable and then reference it later, so that you can pass the value from one statement to another. User variables are related to connections . In other words, a client-defined variable cannot be seen or used by other clients. When the client exits, all the variables for that client connection are automatically freed.

The user variable is in the form of @Var_name, where the variable name var_name can be composed of literal numeric characters, '. ', ' _ ' and ' $ ' in the current character set. The default character set is cp1252 (Latin1). You can change the character set with the mysqld --default-character-set option. The user variable name is not case sensitive.

One way to set up a user variable is to execute the SET statement:

SET @var_nameexpr [, @var_nameexpr] ...

For set, you can use = or : = as an assignment. The expr assigned to each variable can be an integer, a real number, a string, or a null value.

You can also assign a value to a user variable by using a statement instead of set. In this case, the quantifier must be: = instead of =, because in a non-set statement = is treated as a comparison operator:

SET @t1 =0, @t2 =0, @t3 = 0;

SELECT @t1: = (@t2: =1) [email protected]:=4, @t1, @t2, @t3;

+----------------------+------+------+------+

| @t1: = (@t2: =1) [Email protected]:=4 | @t1 | @t2 | @t3 |

+----------------------+------+------+------+

|    5 |    5 |    1 | 4 |

+----------------------+------+------+------+

A user variable can be used in an expression. Currently does not include a context that clearly requires literal values, such as the limit clause of a SELECT statement, or the Ignore number lines clause of the LOAD data statement.

If you use a variable that is not initialized, its value is null.

If a user variable is assigned a string value, its character set and proofing rules are the same as the string. The compressibility of User variables (coercibility) is implicit. (That is, the same compressibility (coercibility) for the table column values.)

Note: in a SELECT statement, the expression is sent to the client before it is evaluated. This indicates that an expression containing the variables set in the select list cannot be used in a having, GROUP by, or ORDER BY clause. For example, the following statement cannot be scheduled to work:

SELECT (@aa: =id) as A, (@aa +3) as B having b=5 from tbl_name ;

The HAVING clause references the alias of an expression in the select list, using @aa. Can't expect to work: @aa does not contain the value of the current row, but rather the ID value of the previously selected row.

The general principle is that you do not assign a value to a user variable in one part of the statement and use that variable in other parts of the same statement. The desired result may be obtained, but cannot be guaranteed.

Another problem with setting a variable and using it in the same statement is that the type of the default result of the variable depends on the variable type before the statement. The following example illustrates the point:

SET @a= ' test ';

Tbl_name;

For this SELECT statement, MySQL reports to the client that the 1th column is a string and converts all access to the @a to a string, even if @a is set to a number in line 2nd. After the SELECT statement is executed, @a is treated as a number for the next statement.

To avoid this problem, either set and use the same variable in the same statement, or set the variable to 0, 0.0, or "before use" to define its type.

The unassigned variable has a value of NULL, and the type is a string.

For example:

SET @a='test'; SELECT @a

Results:

Example two:

Custom variable Display line number

Select (@rowNum: [Email protected]+1) as rownum,test2.* from Test2, (select (@rowNum: =0)) b

Results:

MySQL User custom variables