Shulou(Shulou.com)06/01 Report--

The following is followed by the author to understand the variable category and function of MySQL. I believe you will benefit a lot after reading it. The text is not much in essence. I hope the variable category and function of MySQL is what you want.

There are four types of variables in MySQL: local variables, user variables, session variables, and global variables, of which local variables only exist in functions and stored procedures, which is not well understood here. Session variables and global variables are collectively referred to as system variables in MySQL.

User variable

Basic

As the name implies, it is a user-defined variable. How do you define variables? There are two ways:

SET mode

# both ways can be SET @ variable = exprSET @ variable: = expr

SELECT mode

# must: = SELECT @ variable: = expr

User variable definition remarks:

The initial value of an undefined variable is null (can be used directly without defining a variable and will not report an error)

Variable names are not case-sensitive

Variables cannot be used where literal values are required, such as limit statements in select.

The order in which expressions that call user variables are actually evaluated is undefined, such as SELECT @ a = 0, @ a: = @ a + 1, and both columns may be 0.

When assigning a value to a user variable, the value of the expression is determined first. To understand this, see the following code:

SET @ m = 0 * * set @ m = 3, @ n = @ m * * select @ n; # 0

Although the type of user variable can be changed dynamically, it is not recommended because your life may be at risk when handing over the code: P.

As variables, they all have scope, and the function of user variables is the whole session, that is, the whole session is valid. This looks good, but be aware that when connection pooling is used and custom user variables are not initialized correctly, unexpected problems can occur. Because it was not actually destroyed, it still records the results of the last time.

Example

Let's take a simple example to implement the function of an ordinal number, the table and data are as follows:

CREATE TABLE employee (id int primary key, salary int not null); INSERT INTO employee VALUES (1,100); INSERT INTO employee VALUES (2,200); INSERT INTO employee VALUES (3,300)

According to what we have learned before, we can quickly write the following SQL:

SELECT salary, (@ rowno: = @ rowno + 1) AS 'rowno'FROM employee, (SELECT @ rowno: = 0) r

No problem, everything is as expected, and then let's add a WHERE condition to try:

SELECT salary, (@ rowno: = @ rowno + 1) AS 'rowno'FROM employee, (SELECT @ rowno: = 0) rWHERE @ rowno = 0

In theory, this should not return data, but it does return a piece of data, the one with an id of 1.

Why? The @ rowno used by the WHERE condition is always the same value of 0, and it does not respond in real time because it is modified on the SELECT. To achieve

The function of WHERE needs to be rewritten as follows:

SELECT salary, rownoFROM (SELECT salary, (@ rowno: = @ rowno + 1) AS 'rowno' FROM employee, (SELECT @ rowno: = 0) r) mWHERE rowno = 2

In fact, in SELECT's WHERE, GROUP BY, and ORDER BY, the user variable does not operate as expected, it uses the old value and will not be modified in real time.

System variable session variable

Session variables are the variables maintained by the cloud server for each client connection. When the client connects, the session variable of the client is initialized with the current value of the corresponding global variable.

As the name implies, the scope of a session variable is a session Session. How do you set a value for a session variable? As follows:

Set session var_name = value;set @ @ session.var_name = value;set var_name = value

Note that you can only set values for existing session variables, not create new session variables. So how do I get the session variable? As follows:

The code above show session variables;# lists all session variables, and you can filter show session variables LIKE "% var%" through like; global variables

Global variables will affect the overall operation of the CVM. But once rebooted, these settings will be reset. Note that to change global variables, you must have SUPER permission.

Its settings are similar to those of session variables:

Set global var_name = value;set @ @ global.var_name = value

Global variables cannot be added, only existing ones can be modified. The operation of getting a global variable is similar to a session variable:

Show session variables;show global variables like "var%"

After reading this article on variable categories and functions of MySQL, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.