MySQL Variable usage summary

MySQL Variable usage summary

The learning of SET statements:

Practice of defining User variables using Select
Change the following statement to the form of select:
Set @VAR = (select sum (amount) from penalties);
My changes:
Select @VAR: = (select sum (amount) from penalties);

I can change it, though it is possible. But, in contrast to the book's answers, it is too lame to find such a way. The description does not well understand the nature of the select definition variable.
In select, a user variable is found and initialized if it is not defined.
The original way of the SELECT clause is not affected at all. Just add the user variable. Therefore, the SELECT clause is used in the original way. So like: select SUM (amount) from penalties. Increase the variable to be

: Select @VAR: =sum (amount) from penalties.

Assign the result of sum (amount) to the variable @var:. There is a select in front of the variable, and the user is displaying the variable.


Note section: Terminology classification of MySQL variables:
1. User variables: Start with "@", in the form "@ Variable name"
The user variable is bound to the MySQL client, and the set variable is only valid for the client used by the current user
2. Global variables: When defined, appears in the following two forms, set global variable name or SET @ @global. Variable Name
Effective for all clients. You can set global variables only if you have super permissions

3. Session variable: Valid only for connected clients.

4. Local variables: The action range is between the begin and end statement blocks. The variable that is set in the statement block
The Declare statement is specifically used to define local variables. Set statements are variables of different types, including session variables and global variables

The difference between popular understanding terms:

A user-defined variable is called a user variable. In this sense, session variables and global variables can be user-defined variables. It is only the difference between whether they are valid for the current client or for all clients. Therefore, the user variable includes the session variable and the global variable

The difference between a local variable and a user variable is two points: 1. The user variable starts with "@". Local variables do not have this symbol. 2. Define variables differently. The user variable uses the SET statement, and the local variable uses the Declare statement to define 3. Scope of action. Local variables are only valid between Begin-end statement blocks. After the Begin-end statement block has run, the local variable disappears.

So, finally, the hierarchical relationship between them is: variables include local variables and user variables. User variables include session variables and global variables.


With memo, set @var if no global or session is specified, the user variable is defined by default
There are two ways to define user variables:
1. "=", such as set @a =3,@a:=5
2. ": =". Select often uses this
Summary: Using the Select and set to set the difference, set can use the two forms of setting variables. Select can only use the form ": =" to set the variable
Practice accumulation: User variables will automatically disappear after the MySQL client exits. After I open the client, I use "select @a;" To display a variable value of NULL. Description, undefined variable initialization is null

The problem in practice

Set the configuration impact of constants on Group_concat ():
SET @ @GROUP_CONCAT_MAX_LEN =4
The syntax for setting is mentioned in the manual:
SET [SESSION | GLOBAL] Group_concat_max_len = val;

The following two kinds of forms can achieve the same effect, but what is the difference?

SET @ @global. group_concat_max_len=4;
Global can be omitted, then it becomes: SET @ @GROUP_CONCAT_MAX_LEN = 4;

2011.2.25

The previous understanding was not accurate. Now it is time to summarize the areas that have deepened understanding.

The hierarchical relationship of variables in MySQL is: Roughly including user variables and system variables. System variables include system session variables and system global variables.

This is how I understand each other's differences:

Because user variables are user-defined variables, system variables are variables that are defined and maintained by MySQL. So, the difference between a user variable and a system variable is who is managing those variables. MySQL reads the system variables as soon as it starts (this is done to determine the mechanism or mode in which MySQL runs). Both the system session variable and the user variable disappear after the current client exits. The difference between them can be understood, although it is often seen in the form of "SET @ @varible" To change the value of a system variable, but it does not involve defining system variables. User variables can be defined (initialized) by themselves. The system variable is just changing the value.

Local variables are only defined in the Begin-end statement block and are valid. After executing to the statement block, it disappears. The way of definition has obvious characteristics, using Declare statement.

Why do you see the use of System variables in the form of "@@ 变量 names" and "Variable Names", and how do you understand the difference between the two forms?

The use of system variables can theoretically be used in two forms: 1. Preceded by the symbol "@@" 2. The symbol is omitted. For example I will look at the following form: Current_User. However, the contract system variable to use the "@@ 变量 name" form, is prefixed with the symbol "@@ 变量.

Why is there a situation where there is no sign of current_user? Read the book "SQL for MySQL developers a comprehensive Tutorial and Reference" for a general explanation of the reason for this to be consistent with other SQL products.

MySQL Variable usage summary