What is the difference between variable and @variable in MySQL [duplicate]

Question

If we declare a variable in mysql as:

declare prev_year int;

Are set prev_year = 100; and set @prev_year = 100; the same?

What is the difference as they seem to act like different variables?

Answer 1

No, they aren't really the same ... but they could be seen as the same depending on the context used.

I'll explain. MySQL has session variables, these are variables you assign that live until the session is ended (think of a connection variable). These variables are declared with an @ symbol. So you could execute these two statements and it will work:

SET @myId := 123;

SELECT * FROM table
WHERE id = @myId;

When you create stored procedures and you use DECLARE your variables are in the scope of that procedure, e.g

DECLARE myId INT;

SELECT id INTO myId FROM table
WHERE name = 'steve';