Variables of MySQL learning notes: global variables, session variables, user variables and local variables

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


Case 2: view some variables that meet the conditions


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;
	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

ScopeWhere defined and usedgrammar
User variableCurrent sessionAnywhere in the conversationYou must add @ instead of deliberately limiting the type
local variableBEGIN ENDOnly in BEGIN END and the first sentence is requiredGenerally, 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)
SET SUM = m+n;

Keywords: Database

Added by unreel on Sun, 20 Feb 2022 21:18:18 +0200