How to Declare and Use User-Defined, Local, and Server System Variables in MySQL?

Detailed explanation and application of MySQL variables

Variables in MySQL are containers that temporarily store values ​​for the duration of query execution. There are three main types: user-defined variables, local variables and server system variables.

User-defined variables (@ prefix)

• Declaration: No explicit declaration required
• Initial value: NULL
• Data type: integer, decimal, floating point, string or NULL
• Scope: session level

Initialize user-defined variables:

<code class="language-sql">SET @start = 1, @finish = 10;</code>

Example: Using user-defined variables in a SELECT query

<code class="language-sql">SELECT * FROM places WHERE place BETWEEN @start AND @finish;</code>

Local variables (no prefix)

• Statement: Need to use DECLARE statement
• Initial value: NULL if DEFAULT clause is omitted
• Data type: integer, decimal, string or NULL
• Scope: limited to the BEGIN...END block in which they are declared

Declare and initialize local variables in stored procedures:

<code class="language-sql">DECLARE start INT unsigned DEFAULT 1;  
DECLARE finish INT unsigned DEFAULT 10;</code>

Example: Using local variables in stored procedures

<code class="language-sql">CREATE PROCEDURE sp_test(var1 INT) 
BEGIN   
    DECLARE start  INT unsigned DEFAULT 1;  
    DECLARE finish INT unsigned DEFAULT 10;

    SELECT  var1, start, finish;

    SELECT * FROM places WHERE place BETWEEN start AND finish; 
END; </code>

Server system variables (@@ prefix)

• Declaration: No explicit declaration required
• Initial value: Default value set by the MySQL server
• Data type: depends on the variable
• Scope: global (server-wide) or session level

View the current values ​​of server system variables:

<code class="language-sql">SELECT @@sort_buffer_size;</code>

Modify the value of the server system variable:

<code class="language-sql">SET GLOBAL sort_buffer_size=1000000;</code>

The above is the detailed content of How to Declare and Use User-Defined, Local, and Server System Variables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!