Mysql variable usage Summary

 

Learning set statements:

 

Use select to define user Variables

Change the following statement to select:

Set @ VAR = (select sum (amount) from penalties );

My modifications:

Select @ VAR: = (select sum (amount) from penalties );

 

I can change it like this, though. However, the answer in the comparison book found that this method was too bad. It means that the essence of variables defined by select is not well understood.

In the select statement, a user variable is found. If it is not defined, it will be initialized.

The original method of the select clause is not affected at all. Only user variables are added. Therefore, the select clause is still used in the original way. For example: select sum (amount) from penalties. Add a variable

 

Now: select @ VAR: = sum (amount) from penalties.

 

Assign the result of sum (amount) to the variable @ VAR :. There is select before the variable, so the user will display the variable.

 

 

Note: Glossary of mysql variables:

1. User variable: Start with "@", in the form of "@ variable name"

The user variable is bound to the mysql client. The set variable takes effect only for the client currently in use.

2. GLOBAL variables: The global variables are defined in the following two forms: set GLOBAL variable name or set @ global. variable name.

Valid for all clients. You can set global variables only when you have super permissions.

 

3. session variable: valid only for connected clients.

 

4. Local variable: The scope of the variable is between the in and end statement blocks. Variable set in the statement Block

The declare statement is used to define local variables. The set statement is used to set different types of variables, including session variables and global variables.

 

Differences between terms:

User-Defined variables are called user variables. In this case, both session variables and global variables can be User-Defined variables. The difference is that they are effective for the current client or for all clients. Therefore, user variables include session variables and global variables.

The differences between local variables and user variables are as follows: 1. User variables start. The local variable does not have this symbol. 2. Different variables are defined. User variables use the set statement, and local variables use the declare statement to define 3. scope of action. The local variable is valid only between the in-end statement blocks. After the begin-end statement block is run, the local variable disappears.

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

 

 

NOTE: If set @ var does not specify GLOBAL or SESSION, user variables are defined by default.

You can define user variables in two ways:

1. "=", such as set @ a = 3, @ a: = 5

2. ": = ". Select is often used in this way

Conclusion: The difference between using select and set to set variables is that set can be set in the above two forms. Select can only set variables in the form of ": = ".

Practice accumulation: User variables automatically disappear after the mysql client exits. Then I open the client and use "select @ a;" to display the changed value as null. The initialization of undefined variables is null begin.

Actual Problems

Set the configuration impact of Constants on group_concat:

SET @ GROUP_CONCAT_MAX_LEN = 4

The syntax mentioned in the manual is as follows:

SET [SESSION | GLOBAL] group_concat_max_len = val;

 

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

 

SET @ global. GROUP_CONCAT_MAX_LEN = 4;

Global can be omitted, so it becomes: SET @ GROUP_CONCAT_MAX_LEN = 4;

2011.2.25

Previous understandings are not very accurate. Now, we will summarize the areas after deep understanding.

The hierarchical relationship between variables in mysql is: User variables and system variables. System variables include system session variables and system global variables.

In this way, I understand the differences between them:

Because user variables are User-Defined variables, and system variables are variables defined and maintained by mysql. Therefore, the difference between user variables and system variables is who is managing these variables. When mysql is started, it reads system variables (the purpose is to determine the mechanism or mode of mysql running ). Both the system session variables and user variables disappear after the current client exits. The differences between them can be understood as follows. Although we often see that the form of "set @ varible" changes the value of system variables, it does not involve defining system variables. User variables can be customized (initialized ). The system variable is only changing the value.

Local variables are defined and valid only in the begin-end statement block. After the statement block is executed, it disappears. The definition method has obvious characteristics. Use the declare statement.

Why does the system variable appear in the form of "@ variable name" and "variable name?

In theory, system variables can be used in two forms: 1. the symbol "@" is preceded by the symbol. 2. The symbol is omitted. For example, I will see the following format: CURRENT_USER. However, it is agreed that the system variables should be in the form of "@ variable name", that is, adding the symbol "@" in front "@@".

Why does CURRENT_USER have no symbols? Read the general reason described in SQL For MySQL Developers A Comprehensive Tutorial and Reference to be consistent with other SQL products.

 

Author: hj714886063