Classification of variables in MySQL

• Variables are divided into system variables and user-defined variables
  • System variables: also divided into global variables and session variables
  • User defined variables: divided into user variables and local variables

1, System variable

• System variables are provided by the system, not user-defined, and belong to the server level

The syntax used is as follows:

1. View all system variables

show global variables; -- Global
show session variables;-- Of the current session(Current connection)

2. View some system variables that meet the conditions

show global variables like '%char%';
show [session] variables like '%char%'; #By default, it means session without writing session or global

3. View the value of a specified system variable@@

select @@global | session.System variable value;

4. Assign a value to a system variable. If @ @ is used, it will be used

Mode 1:
set global|[session] System variable name = value;

Mode 2:
set @@global|[session] System variable name = value;

be careful:

• If it is a global level, you need to add global; If it is session level, you need to add session; If you do not write, the default is session

• Scope of global variable:

  Each time the server starts, it will assign initial values to all global variables. Global variables are specific to all sessions, but cannot be restarted across sessions,
  That is, after the service is restarted, the previous changes to the global variables will return to the default

Case 1

SHOW GLOBAL VARIABLES;  -- View all global system variables
SHOW SESSION VARIABLES; -- View system variables for all current sessions

Case 2

SHOW GLOBAL VARIABLES LIKE '%char%'; -- View with'char'Global system variable of character

Case 3

SELECT @@global.autocommit; --View global variables auto commit variables
SELECT @@tx_isolation; --View the isolation level of the current session

Case 4

SET @@global.autocommit=0; --Set the global auto submit variable to 0, that is, turn off auto submit
SET GLOBAL autocommit = 1; --ditto

2, Session variable

Scope: the scope of the session is limited to the current session and will not affect other sessions

Case 1: view all variables of the current session

SHOW SESSION VARIABLES;
SHOW VARIABLES;

Case 2: view some variables that meet the conditions

SHOW VARIABLES LIKE '%commit%';

Case 3: to view the specified variables, you can only use@@

SELECT @@session.autocommit;
SELECT @@autocommit;

Case 4 setting the specified variable

SET SESSION tx_isolation='read-committed';
SET tx_isolation = 'read-committed';
SET @@session.tx_isolation='read-committed';
SET @@tx_isolation='read-committed';

3, Custom user variables

• Note: variables are user-defined and not systematic. They are divided into user variables and local variables

• Steps: operation, declaration, use

Scope: valid for the current session (connection), the same as the scope of the session variable

① Declare and initialize

SET @User variable name = value;
SET @User variable name := value;
SELECT @User variable name := value;

② Assignment (update of user variables)

-- #Method 1 is the same as declaring and initializing
SET @User variable name = value;
SET @User variable name := value;
SELECT @User variable name := value;
	#Case: declare and initialize
	SET @name = 'mike';
	SET @name = 111;
	SET @account= 1;

-- #Mode 2
SELECT field INTO Variable name
FROM surface;
	#Case:
	SELECT COUNT(*) INTO @account FROM book;

③ View the value of a user variable

SELECT @User variable name;
-- for example
SELECT @account;

4, Custom local variable

Scope: it is only valid in the begin end where it is defined. It is applied to the first sentence in begin end

① Statement

DECLARE Variable name type;
DECLARE Variable name type DEFAULT value;--Set variables and initialize

② Assignment

Method 1: Pass SET or SELECT
SET Local variable name=value;
SET Local variable name:=value;
SELECT @Local variable name:=value;

Mode 2: Pass SELECT INTO
SELECT field INTO Local variable name FROM surface;

③ Use

SELECT Local variable name;

5, Compare local and user variables

	Scope	Where defined and used	grammar
User variable	Current session	Anywhere in the conversation	You must add @ instead of deliberately limiting the type
local variable	BEGIN END	Only in BEGIN END and the first sentence is required	Generally, there is no need to add the @ symbol, and the type needs to be limited

Case: declare two variables and assign values, sum and print

-- #1. Use user variables
SET @m = 1;
SET @n = 2;
SET @sum = @m + @n;
SELECT @sum;

-- #2. Error demonstration using local variables (the current syntax is problematic because it must be between begin and end)
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE SUM INT;
SET SUM = m+n;
SELECT SUM;