MySQL has the 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:
You can initialize this variable with a
SET statement or inside in a query:
SET @var = 1SELECT @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 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 2CALL prc_test();var2 @var2--- ---2 3CALL 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.)