MySQL: @variable vs. variable. What’s the difference?

by

MySQL has a concept of user-defined variables.

They are loosely typed variables that may be initialized somewhere in a session and keep their value until the session ends.

They are prepended with an @ sign, like this: @var

You can initialize this variable with a SET statement or inside a query:

SET @var = 1

SELECT @var2 := 2

When you develop a stored procedure in MySQL, you can pass the input parameters and declare the local variables:

DELIMITER //

CREATE PROCEDURE prc_test (var INT)
BEGIN
    DECLARE  var2 INT;
    SET var2 = 1;
    SELECT  var2;
END;
//

DELIMITER ;

These variables are not prepended with any prefixes.

The difference between a procedure variable and a session-specific user-defined variable is that a procedure variable is reinitialized to NULL each time the procedure is called, while the session-specific variable is not:

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

SET @var2 = 1;

CALL prc_test();

var2  @var2
---   ---
2     2


CALL prc_test();

var2  @var2
---   ---
2     3


CALL prc_test();

var2  @var2
---   ---
2     4

As you can see, var2 (procedure variable) is reinitialized each time the procedure is called, while @var2 (session-specific variable) is not.

(In addition to user-defined variables, MySQL also has some predefined “system variables”, which may be “global variables” such as @@global.port or “session variables” such as @@session.sql_mode; these “session variables” are unrelated to session-specific user-defined variables.)

More Related Contents:

  1. When using with SAVEPOINT and ROLLBACK , what will be the output of this?
  2. How do I make this MySQL query faster and better?
  3. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  4. MySQL offset infinite rows
  5. Ordering by the order of values in a SQL IN() clause
  6. SQL Selecting from two Tables with inner join and limit [duplicate]
  7. Can a foreign key reference a non-unique index?
  8. MySQL Group by SUM
  9. How to set a maximum execution time for a mysql query?
  10. Get the latest date from grouped MySQL data
  11. SQL keys, MUL vs PRI vs UNI
  12. MySQL Error 1264: out of range value for column
  13. How to change the default port of mysql from 3306 to 3360
  14. Special Characters in MySQL Table Name
  15. How to move columns in a MySQL table?
  16. Is it possible to reference one column as multiple foreign keys?
  17. quick selection of a random row from a large table in mysql
  18. #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
  19. How can we find gaps in sequential numbering in MySQL?
  20. Empty IN clause parameter list in MySQL
  21. creating a random number using MYSQL
  22. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  23. mysql count group by having
  24. SQL UPDATE with sub-query that references the same table in MySQL
  25. Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server
  26. How to store a one to many relation in MySQL database?
  27. MySQL – Complexity of: SELECT COUNT(*) FROM MyTable;
  28. How to count date difference excluding weekend and holidays in MySQL
  29. Execute multiple semi-colon separated query using mysql Prepared Statement
  30. mysql unique number generation

Leave a Comment