MySQL:@variable与变量.有什么不同? [英] MySQL: @variable vs. variable. What's the difference?

问题描述

在另一个问题中，我发布了一个人告诉我，两者之间是有区别的:

In another question I posted someone told me that there is a difference between:

@variable

和:

variable

在MySQL中

.他还提到了MSSQL如何具有批处理范围，而MySQL如何具有会话范围.有人可以为我详细说明吗?

in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me?

推荐答案

MySQL具有 它们是松散类型的变量，可以在会话的某处进行初始化，并保持其值直到会话结束.

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

它们以@符号开头，如下所示:@var

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

您可以使用SET语句或在查询内部初始化此变量:

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

SET @var = 1

SELECT @var2 := 2

在MySQL中开发存储过程时，可以传递输入参数并声明局部变量:

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.

过程变量和特定于会话的用户定义变量之间的区别在于，每次调用该过程时，都会将过程变量重新初始化为NULL，而特定于会话的变量则不会:

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

如您所见，每次调用该过程时，都会重新初始化var2(过程变量)，而没有调用@var2(特定于会话的变量).

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

(除用户定义的变量外，MySQL 还具有一些预定义的系统变量"，它们可以是全局变量"(例如@@global.port)或会话变量"(例如；这些会话变量"与特定于会话的用户定义变量无关.)

(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.)

这篇关于MySQL:@variable与变量.有什么不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！