MySQL - What is the difference between @var_name and var_name?

var_name is a system variable which you can’t define yourself while @var_name is a session-specific user-defined variable.

The user-defined variable in MySQL enables you to store a value in one statement and refer to it in another statement later. Therefore, the main difference is that while the procedure variable var_name is reinitialized to NULL each time the procedure is called, the session-specific user-defined variable @var_name is not, it got passed the value from the last statement.

You can initialize a user-defined variable in a SET statement:

SET @var_name = expr [, @var_name = expr] ...

Let’s create a simple procedure as an example to compare test_var and @test_var:

CREATE PROCEDURE test_procedure ()
BEGIN
    DECLARE test_var INT DEFAULT 1;
    SET test_var = test_var + 1;
    SET @test_var = @test_var + 1;
    SELECT  test_var, @test_var;
END;

SET @test_var = 1;

CALL test_procedure();

test_var  @test_var
---       ---
2         2


CALL test_procedure();

test_var  @test_var
---       ---
2         3


CALL test_procedure();

test_var  @test_var
---       ---
2         4

And it’s session-specific, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client session are automatically expired when that client exits.

---

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.


Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.