@variable vs. variable in MySQL: What's the Difference?

MySQL variable type comparison: @variable and variable

MySQL provides two types of variables: user-defined variables and session-level user-defined variables. The latter starts with the @ symbol (@variable), while the former does not (variable).

User-defined variables

Such variables are loosely typed and retain their value throughout the session. They are created outside of any stored procedure using the SET statement. For example:

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

Session-level user-defined variables

Unlike user-defined variables, these variables are only visible in the current session. They are declared in stored procedures and passed as parameters. For example:

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

Main differences

The main difference between these two variable types is their scope and how they are initialized. Procedure variables are reinitialized to NULL each time the procedure is called, while session-level variables retain their values ​​across procedure calls. The following code snippet demonstrates this:

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

SET @var2 = 1;</code>

Example output:

var2	@var2
2	2
2	3
2	4

As you can see, var2 is reinitialized each time, while @var2 accumulates value changes across calls.

Conclusion

Understanding the difference between @variable and variable is critical to effective MySQL development. It allows you to manage variables appropriately based on their scope and initialization behavior, ensuring code efficiency and accuracy.

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