MySQL：@variable 与变量。有什么不同？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1009954/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: @variable vs. variable. What's the difference?
提问by aarona
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?
在 MySQL 中。他还提到了 MSSQL 如何具有批处理范围而 MySQL 具有会话范围。有人可以为我详细说明一下吗？
回答by Quassnoi
MySQLhas the concept of user-defined variables.
MySQL具有用户定义变量的概念。
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
它们前面有一个@符号，如下所示：@var
You can initialize this variable with a SETstatement or inside in a query:
您可以使用SET语句或在查询中初始化此变量：
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:
在 中开发存储过程时MySQL，可以传递输入参数并声明局部变量：
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 NULLeach time the procedure is called, while the session-specific variable is not:
过程变量和特定于会话的用户定义变量之间的区别在于，NULL每次调用过程时都会重新初始化过程变量，而特定于会话的变量则不是：
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.
如您所见，var2每次调用过程时都会重新初始化 (procedure variable)，而@var2(session-specific variable) 则不会。
(In addition to user-defined variables, MySQL alsohas some predefined "system variables", which may be "global variables" such as @@global.portor "session variables" such as @@session.sql_mode; these "session variables" are unrelated to session-specific user-defined variables.)
（除了用户自定义的变量，MySQL还有一些预定义的“系统变量”，可能是“全局变量”之类的@@global.port或者“会话变量”之类的@@session.sql_mode；这些“会话变量”与特定会话的用户自定义变量无关变量。）
回答by molf
In MySQL, @variableindicates a user-defined variable. You can define your own.
在 MySQL 中，@variable表示用户定义的变量。你可以定义你自己的。
SET @a = 'test';
SELECT @a;
Outside of stored programs, a variable, without @, is a system variable, which you cannot define yourself.
在存储程序之外， a variable，而没有@，是一个系统变量，您无法自己定义。
The scope of this variable is the entire session. That means that while your connection with the database exists, the variable can still be used.
此变量的范围是整个会话。这意味着当您与数据库的连接存在时，仍然可以使用该变量。
This is in contrast with MSSQL, where the variable will only be available in the current batch of queries (stored procedure, script, or otherwise). It will not be available in a different batch in the same session.
这与 MSSQL 形成对比，在 MSSQL 中，变量仅在当前查询批次（存储过程、脚本或其他）中可用。它不会在同一会话的不同批次中可用。
回答by Xybo
MSSQL requires that variables within procedures be DECLAREd and folks use the @Variable syntax (DECLARE @TEXT VARCHAR(25) = 'text'). Also, MS allows for declares within any block in the procedure, unlike mySQL which requires all the DECLAREs at the top.
MSSQL 要求过程中的变量被声明，并且人们使用@Variable 语法（DECLARE @TEXT VARCHAR(25) = 'text'）。此外，MS 允许在过程中的任何块内进行声明，这与 mySQL 不同，后者需要顶部的所有 DECLARE。
While good on the command line, I feel using the "set = @variable" within stored procedures in mySQL is risky. There is no scope and variables live across scope boundaries. This is similar to variables in JavaScript being declared without the "var" prefix, which are then the global namespace and create unexpected collisions and overwrites.
虽然在命令行上很好，但我觉得在 mySQL 的存储过程中使用“set = @variable”是有风险的。没有作用域，变量跨越作用域边界。这类似于在 JavaScript 中声明的变量没有“var”前缀，它们是全局命名空间并会产生意外的冲突和覆盖。
I am hoping that the good folks at mySQL will allow DECLARE @Variable at various block levels within a stored procedure. Notice the @ (at sign). The @ sign prefix helps to separate variable names from table column names - as they are often the same. Of course, one can always add an "v" or "l_" prefix, but the @ sign is a handy and succinct way to have the variable name match the column you might be extracting the data from without clobbering it.
我希望 mySQL 的好人将允许在存储过程中的各个块级别上声明 @Variable。注意@（at 符号）。@ 符号前缀有助于将变量名与表列名分开 - 因为它们通常是相同的。当然，您总是可以添加“v”或“l_”前缀，但@符号是一种方便且简洁的方式，可以让变量名称与您可能从中提取数据的列相匹配，而不会破坏它。
MySQL is new to stored procedures and they have done a good job for their first version. It will be a pleaure to see where they take it form here and to watch the server side aspects of the language mature.
MySQL 是存储过程的新手，他们的第一个版本做得很好。很高兴看到他们在这里形成它并观看语言的服务器端方面的成熟。
回答by Peter
In principle, I use UserDefinedVariables (prepended with @) within Stored Procedures. This makes life easier, especially when I need these variables in two or more Stored Procedures. Just when I need a variable only within ONE Stored Procedure, than I use a System Variable (without prepended @).
原则上，我在存储过程中使用 UserDefinedVariables（以 @ 开头）。这让生活更轻松，尤其是当我在两个或多个存储过程中需要这些变量时。就在我只需要一个存储过程中的变量时，我才使用系统变量（不带@）。
@Xybo: I don't understand why using @variables in StoredProcedures should be risky. Could you please explain "scope" and "boundaries" a little bit easier (for me as a newbe)?
@Xybo：我不明白为什么在 StoredProcedures 中使用 @variables 会有风险。您能否更容易地解释“范围”和“边界”（对我来说是新手）？
