Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use variables in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Learning set sentences:

Practice of using select to define user variables

Change the following statement to select:

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

My revision:

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

I can change it in this way, though. However, comparing the answers in the book, I find that this way is too lame. It shows that the nature of select defining variables is not well understood.

In select, it is found that there is a user variable that, if not defined, is initialized.

The original manner of the select clause is not affected at all. Only user variables have been added. Therefore, use the select clause in the original way. So like: select sum (amount) from penalties. Just add variables.

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

Assign the result of sum (amount) to the variable @ VAR:. The variable is preceded by select, so the user displays the variable.

Notes section: terminology classification of variables:

1. User variable: starts with "@" and takes the form "@ variable name"

The user variable is bound to the mysql client, and the set variable takes effect only for the client used by the current user.

two。 Global variable: when defined, it appears in the following two forms, set GLOBAL variable name or set @ @ global. Variable name

Effective for all clients. Global variables can be set only if you have super permission

3. Session variable: valid only for connected clients.

4. Local variable: the scope of action is between begin and end statement blocks. The variable set in the statement block

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

A popular understanding of the differences between terms:

User-defined variables are called user variables. With this understanding, both session variables and global variables can be user-defined variables. It's just the difference between whether they work for the current client or for all clients. Therefore, user variables include session variables and global variables

The difference between local variables and user variables lies in two points: 1. User variables start with "@". Local variables do not have this symbol. two。 The definition variables are different. The user variable uses the set statement, and the local variable uses the declare statement to define 3. Scope of action. Local variables are valid only between blocks of begin-end statements. After the block of begin-end statements is run, the local variables disappear.

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

Using the memo, if set @ var does not specify GLOBAL or SESSION, the user variable will be defined by default

There are two ways to define user variables:

1. "=", for example, set @ a = 3

2. ":". Select is often used like this

Summary: use select and set to set the difference between variables, set can use the above two forms to set variables. Select can only set variables in the form of ": =".

Practical accumulation: user variables disappear automatically after the mysql client exits. Then I open the client and use "select @ a;" to show that the changed value is null. Note that the initialization of undefined variables is null variables

Problems in practice

Setting the constant affects the configuration of group_concat ():

SET @ @ GROUP_CONCAT_MAX_LEN=4

The syntax for the settings 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's 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

The previous understanding is not very accurate. Now to deepen the understanding of the place to summarize.

The hierarchical relationship of variables in mysql is generally composed of user variables and system variables. System variables include system session variables and system global variables.

This is how I understand the differences between them:

Because user variables are user-defined variables, system variables are variables defined and maintained by mysql. So, the difference between user variables and system variables is who is managing these variables. Mysql reads the system variables as soon as it starts (this is done to determine what mechanism or mode mysql is running under). Both system session variables and user variables disappear after the current client exits. The difference between them can be understood this way, although it is common to see the form "set @ @ varible" to change the value of a system variable, but it does not involve defining a system variable. User variables can be defined (initialized) by themselves. The system variable is just changing the value.

Local variables are defined and valid only in begin-end statement blocks. It disappears after the execution of the statement block. The way it is defined has obvious characteristics, using the declare statement.

Why do you see the use of system variables in the form of "@ @ variable name" and "variable name"? how do you understand the difference between the two forms?

In theory, two forms can be used to use system variables: 1. It is preceded by the symbol "@" 2. Symbols are omitted. For example, I will look at the following form: CURRENT_USER. However, the convention for system variables to use the form of "@ @ variable name" is to precede it with the symbol "@ @".

After reading the above, do you have any further understanding of how to use variables in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.