What are the variables of MySQL? how to use?

This article brings you what are the variables of MySQL? how to use? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

MySQL's variables are divided into four types: local variables, user variables, session variables and global variables. Local variables only exist in functions and stored procedures. I don’t know much about them here. Among them, session variables and global variables are collectively called system variables in MySQL.

User variables

Basic

As the name suggests, they are user-defined variables. How to define variables? There are two methods:

SET method

# 两种方式都可以
SET @variable = expr
SET @variable := expr

SELECT method

# 必须 :=
SELECT @variable := expr

User variable definition remarks:

1. Undefined variables The initial value is null (can be used directly without defining the variable, no error will be reported)

2. Variable names are not case-sensitive

3. Variables cannot be used where literal values ​​are required, such as the limit statement in select, etc.

4. The evaluation order of expressions calling user variables is actually undefined, such as SELECT @a = 0, @a := @a 1;, Both columns may be 0.

5. When assigning a value to a user variable, the value of the expression will be determined first. How to understand, please look at the following code:

   SET @m = 0;
   SET @m = 3, @n = @m;
   SELECT @n; # 0

6. Although the type of user variables can be modified dynamically, it is not recommended because you may be in danger of life when handing over the code:p .

As variables, they all have scope. User variables are valid for the entire session, that is, they are valid for the entire session. This looks good, but be aware that when a connection pool is used and custom user variables are not initialized correctly, unexpected problems may occur. Because it has not actually been destroyed, it still records the last result.

Example

Let’s take a simple example to implement a serial number function. 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);

Based on what we have learned before, we can quickly write The following SQL comes out:

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

There is no problem, everything is as expected, and then we add a WHERE condition to try:

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

Theoretically, this should not return data, but it It also returns a piece of data, the one with id 1.
why? The @rowno used in the WHERE condition is always the same value 0, and it will not respond in real time because it is modified in SELECT. To realize the function of
WHERE, it needs to be rewritten as follows:

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

In fact, the user variables in WHERE, GROUP BY and ORDER BY of SELECT will not operate as expected. It uses the old value and does not Will be modified in real time.

System variables

Session variables

Session variables are variables maintained by the server for each client connection. When the client connects, the client's session variables are initialized using the current values ​​of the corresponding global variables.

As the name suggests, the scope of a session variable is a session. How to 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, and you cannot create new session variables. So how to get the session variables? As follows:

show session variables;
# 以上代码会把所有会话变量罗列出来，可通过 like 进行过滤
show session variables LIKE "%var%";

Global variables

Global variables will affect the overall operation of the server. But upon reboot, 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 add new variables, only existing ones can be modified. The operation of obtaining global variables is similar to session variables:

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

The above is the detailed content of What are the variables of MySQL? how to use?. For more information, please follow other related articles on the PHP Chinese website!