User-Defined Variables vs. Procedure Variables in MySQL: What's the Difference?

Comparison of user-defined variables and process variables in MySQL

In MySQL, user-defined variables are declared with a @ prefix, while procedure variables have no prefix. This distinction arises from the fact that session-specific user-defined variables retain their value across a session, whereas procedure variables are reinitialized to NULL on each procedure call.

Session-specific variables (@variable)

User-defined variables prefixed with @ act as session-specific variables. They can be initialized using a SET statement or in a query and persist until the end of the session. For example:

<code class="language-sql">SET @var = 1;</code>

Process variable (variable)

Variables declared in a stored procedure or function without a prefix are local variables. These variables are only available within the scope of the procedure and are reinitialized to NULL on each procedure call.

<code class="language-sql">CREATE PROCEDURE prc_test ()
BEGIN
    DECLARE var2 INT DEFAULT 1;
    ...
END;</code>

Main differences

The following table summarizes the main differences between @ variables and process variables:

Features

@variable (session-specific)

variable (process variable)

Scope

Last for entire session

Reinitialized on each procedure call

Syntax

Prefixed with

特性	@variable (会话特定)	variable (过程变量)
作用域	持续整个会话	每次过程调用时重新初始化
语法	以 @ 为前缀	没有前缀

No prefix

Example

Consider the following stored procedure:

<code class="language-sql">CREATE PROCEDURE prc_test ()
BEGIN
    DECLARE var2 INT DEFAULT 1;
    SET var2 = var2 + 1;
    SET @var2 = @var2 + 1;
    ...
END;</code>

Each time this procedure is called, var2 is reset to 1 and @var2 continues to be incremented. This demonstrates the reinitialization behavior of process variables and the session-specific nature of user-defined variables.

The above is the detailed content of User-Defined Variables vs. Procedure Variables in MySQL: What's the Difference?. For more information, please follow other related articles on the PHP Chinese website!