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

问题描述

在我发布的另一个问题中，有人告诉我两者之间存在差异:

@variable

和:

变量

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

解决方案

MySQL 有一个概念 用户定义的变量.

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

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

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

SET @var = 1选择@var2 := 2

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

DELIMITER//创建程序 prc_test (var INT)开始声明 var2 INT;设置 var2 = 1;选择 var2;结尾;//分隔符；

这些变量前面没有任何前缀.

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

CREATE PROCEDURE prc_test()开始声明 var2 INT DEFAULT 1;SET var2 = var2 + 1;设置@var2 = @var2 + 1;选择 var2，@var2；结尾;设置@var2 = 1;调用 prc_test();var2 @var2--- ---2 2调用 prc_test();var2 @var2--- ---2 3调用 prc_test();var2 @var2--- ---2 4

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

(除了用户自定义的变量，MySQL还有一些预定义的系统变量"，可能是全局变量"，比如@@global.portcode> 或会话变量"，例如 @@session.sql_mode；这些会话变量"与会话特定的用户定义变量无关.)

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

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