MySQL Variables: What's the Difference Between `@variables` and Procedure Variables?

MySQL User-Defined Variables: @variables vs. Procedure Variables

MySQL offers user-defined variables, denoted by a leading @ symbol (e.g., @myVar). These @variables are session-scoped; they are initialized within a session and persist until the session ends. Crucially, they differ from procedure variables.

Unlike @variables, procedure variables are local to a stored procedure. Each time a procedure is called, its local variables are reinitialized to NULL. This contrasts sharply with @variables, which retain their values across multiple procedure calls within the same session. Modifying an @variable inside a procedure affects its value for subsequent calls within that session.

It's also important to distinguish both @variables and procedure variables from MySQL's system variables. System variables are either global (e.g., @@global.port) or session-scoped (e.g., @@session.sql_mode). Session system variables, while session-specific, behave differently from @variables.

Let's illustrate the difference:

<code class="language-sql">CREATE PROCEDURE prc_test ()
BEGIN
    DECLARE myProcVar INT DEFAULT 1;
    SET myProcVar = myProcVar + 1;
    SET @myUserVar = @myUserVar + 1;
    SELECT myProcVar, @myUserVar;
END;

SET @myUserVar = 1;

CALL prc_test();
CALL prc_test();
CALL prc_test();</code>

The output will show:

<code>myProcVar  @myUserVar
---------  ----------
2           2
2           3
2           4</code>

Observe that myProcVar (the procedure variable) resets to 2 with each call, while @myUserVar (the user-defined variable) increments cumulatively, preserving its value across calls.

This understanding is crucial for effectively using MySQL variables. @variables manage session data, procedure variables handle local procedure state, and system variables control the database environment.

The above is the detailed content of MySQL Variables: What's the Difference Between `@variables` and Procedure Variables?. For more information, please follow other related articles on the PHP Chinese website!