MySQL: @variable vs. variable. Whats the difference?
5883 ワード
MySQL: @variable vs. variable. Whats the difference?
up vote
351down votefavorite
121
In another question I posted someone told me that there is a difference between:
and:
in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me?
add a comment
up vote
445down voteaccepted
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
You can initialize this variable with a
When you develop a stored procedure in
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
As you can see,
(In addition to user-defined variables, MySQL also has some predefined "system variables", which may be "global variables"such as
転載先:https://www.cnblogs.com/kungfupanda/p/5855158.html
up vote
351down votefavorite
121
In another question I posted someone told me that there is a difference between:
@variable
and:
variable
in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me?
add a comment
up vote
445down voteaccepted
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: @var
You can initialize this variable with a
SET
statement or inside in 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 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.) 転載先:https://www.cnblogs.com/kungfupanda/p/5855158.html