MySQL: @variable vs. variable. Whats the difference?

最新推荐文章于 2024-12-20 16:50:14 发布

weixin_34127717

最新推荐文章于 2024-12-20 16:50:14 发布

阅读量445

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/kungfupanda/p/5855158.html

版权

本文深入探讨了MySQL中用户定义变量的概念，包括@variable和variable的区别，以及它们在存储过程中的使用方式。通过实例展示了变量初始化、作用域和生命周期，强调了会话级变量与存储过程变量之间的关键区别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL: @variable vs. variable. Whats the difference?

up vote 351down vote favorite 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