MySQL variable principles and application examples

In the MySQL documentation, MySQL variables can be divided into two categories, namely system variables and user variables.

However, according to actual applications, they are further divided into four types, namely local variables, user variables, session variables and global variables.

1. Local variables

MySQL local variables can only be used in begin/end statement blocks, such as begin/end statement blocks in stored procedures.

Its scope is limited to this statement block.

-- The declare statement is used to define local variables. You can use default to specify the default value.
declare age int default 0;

-- Local variable assignment method 1
set age=18;

-- Local variable assignment method 2
select StuAge
into age
from demo.student
where StuNo='A001';

2. User variables

MySQL user variables. User variables in MySQL do not need to be declared in advance. When using them, just use "@variable name" directly.

Its scope is the current connection.

-- The first usage, when using set, you can use the "=" or ":=" two assignment symbols to assign values
set @age=19;

set @age:=20;

-- The second usage, when using select, you must use the ":=" assignment symbol to assign a value
select @age:=22;

select @age:=StuAge
from demo.student
where StuNo='A001';

Session variables

MySQL session variables, the server maintains a series of session variables for each connected client.

Its scope is limited to the current connection, that is, the session variables in each connection are independent.

-- Display all session variables
show session variables;

-- Three ways to set the value of a session variable
set session auto_increment_increment=1;
set @@session.auto_increment_increment=2;
set auto_increment_increment=3; -- When the session keyword is omitted, the default is session, which sets the value of the session variable

-- Three ways to query the value of session variables
select @@auto_increment_increment;
select @@session.auto_increment_increment;
show session variables like '%auto_increment_increment%'; -- The session keyword can be omitted

-- The keyword session can also be replaced by the keyword local
set @@local.auto_increment_increment=1;
select @@local.auto_increment_increment;

4. Global variables

MySQL global variables, global variables affect the overall operation of the server, when the service starts, it initializes all global variables to default values. To modify global variables, you must have super privileges.

Its scope is the entire life cycle of the server.

-- Display all global variables
show global variables;

-- Two ways to set the value of a global variable
set global sql_warnings=ON; -- global cannot be omitted
set @@global.sql_warnings=OFF;

-- Two ways to query the value of global variables
select @@global.sql_warnings;
show global variables like '%sql_warnings%';

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.