Session variable: the session variable is initialized by MYSQL every time a new connection is established. MYSQL will copy the values of all current global variables. As a session variable. (that is, if the values of session variables and global variables have not been changed manually after the session is established, the values of all these variables are the same.)

Global variables: when the server starts, each global variable is initialized to its default value (these default values can be changed through the options specified in the command line or options file). mysql has many global variables, including some basic information of the system and some basic configurations of mysql (such as connect_timeout, which is 10s by default).

User variables: user variables are variables defined by users themselves, and also fail when the connection is disconnected. The definition and use of user variables are much simpler than session variables.

Local variables: local variables usually appear in stored procedures for intermediate calculation results, data exchange, etc. when the stored procedures are executed, the life cycle of variables will end.

1. View the values of all variables in mysql

SHOW [GLOBAL|SESSION] VARIABLES;

To view global variables:
SHOW GLOBAL VARIABLES;

View session variables:
SHOW SESSION VARIABLES;
SHOW VARIABLES;

2. View the value of a single variable in mysql

SHOW [GLOBAL|SESSION] VARIABLES [LIKE matching mode];

To view global variables:
SHOW GLOBAL VARIABLES LIKE 'wait_timeout'; 
SHOW GLOBAL VARIABLES LIKE 'wait_time%';
SHOW GLOBAL VARIABLES LIKE '%wait%';
SELECT @@GLOBAL.wait_timeout;

View session variables:
SHOW SESSION VARIABLES LIKE 'wait_timeout'; 
SHOW  VARIABLES LIKE 'wait_timeout%';
SHOW  VARIABLES LIKE '%wait%';
SELECT @@SESSION.wait_timeout;
select @@local.wait_timeout;
SELECT @@wait_timeout;

View user variables:
select @count;

To view local variables:
declare count int(4);
select count;

3. Set the value of the variable

Global variables:
SET GLOBAL default_storage_engine = MyISAM;
SET @@GLOBAL.default_storage_engine = MyISAM;

Session variables:
SET SESSION default_storage_engine = MyISAM;
SET LOCAL default_storage_engine = MyISAM;
SET default_storage_engine = MyISAM;
SET @@SESSION.default_storage_engine = MyISAM;
SET @@LOCAL.default_storage_engine = MyISAM;
SET @@default_storage_engine = MyISAM;

User variables:
set @count=1;
set @count:=11;
select 100 into @count;
select count(price) into @count from items where id= 99;

Local variables:
declare count int(4);
declare sum int(4);
set count=1;
set sum:=0;

Refresh takes effect immediately
flush privileges

Comparative use of several variables

Operation type	global variable	Session variable	User variable	Local variable (parameter)
Location of occurrence	Command line, function, stored procedure	Command line, function, stored procedure	Command line, function, stored procedure	Functions, stored procedures
How to define	You can only view and modify, not define	You can only view and modify, not define	Direct use, @ var form	declare count int(4);
Effective life cycle	Restore defaults on server restart	When disconnected, the variable disappears	When disconnected, the variable disappears	The scope of the function or stored procedure is out, and the variable is invalid
View all variables	show global variables;	show session variables; show variables;	-	-
View some variables	show global variables like 'sql%';	show session variables like 'wait_timeout';  show variables like 'wait_timeout%'; show variables like '%wait%';	-	-
View specified variables	select @@global.sql_mode;	select @@SESSION.wait_timeout; select @@local.wait_timeout; select @@wait_timeout;	select @var;	select count;
Set the specified variable	set global sql_mode=''; set @@global.sql_mode=''	set session wait_timeout = 64800; set local wait_timeout = 64800; set wait_timeout = 64800; set @@session.wait_timeout = 64800; set @@local.wait_timeout = 64800; set @@wait_timeout = 64800;	set @count=1; set @count:=11; select 1 into @count; select count(price) into @count from items where id= 99;	set count=1; set count:=101; select 1 into count;

be careful:

1. The scope of action is omitted from the statement setting system variables. The default scope of action is SESSION

2. If a client modifies a value of the session variable, the values of other clients will not be changed.

3. Sometimes it is found that the modification does not take effect. You need to close the session and restart, that is, exit and log in again. However, it is found that restarting the mysql server variable does not take effect. The best way is to modify the default value of mysql startup.

(1) Under windows, D: \ MySQL server 8.0 \ my Ini, modify this variable under the [mysqld] stack

(2) Under Linux, modify / etc / my CNF, which takes effect after restarting mysql

vim /etc/my.cnf

restart mysql: 
serveice mysql restart(5.5.7 edition)
serveice mysqld restart