MySQL: @Variable Vs. Variable. What's the Difference

MySQL: @variable vs. variable. What's the difference?

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 vs. variable. Whats the difference? (Part2)

MySQL's variable naming schema is a bit weird, when having the first look into it. Generally MySQL differentiates between three types of variables:

• system variables (global or session scoped): @@varname
• user defined variables (they are session scoped): @varname
• local variables in stored programs: varname

So naming conflicts, such as those you mentioned above, only arise within stored programs. Therefore you first should try to avoid these naming conflicts by assigning unambiguous parameter names, e.g. by prefxing the parameters with p such as pId. If MySQL encounters an ambiguity it will interpret the reference as the name of a variable (see here):

[...] Local variable names should not
be the same as column names. If an SQL
statement, such as a SELECT ... INTO
statement, contains a reference to a
column and a declared local variable
with the same name, MySQL currently
interprets the reference as the name
of a variable. [...]

The wording currently somehow gives the impression that this behaviour could change in future versions.

What is the difference between variable and @variable in MySQL

No, they aren't really the same ... but they could be seen as the same depending on the context used.

I'll explain. MySQL has session variables, these are variables you assign that live until the session is ended (think of a connection variable). These variables are declared with an @ symbol. So you could execute these two statements and it will work:

SET @myId := 123;

SELECT * FROM table
WHERE id = @myId;

When you create stored procedures and you use DECLARE your variables are in the scope of that procedure, e.g

DECLARE myId INT;

SELECT id INTO myId FROM table
WHERE name = 'steve';

Is there a difference between := and = with mysql variables?

In the case of SET, they are synonymous:

SET @var := 1234;
SET @var = 1234;

But = acts as a conditional operator when used in a SELECT:

SELECT @var := 1234; -- 1234
SELECT @var = 1234;  -- 1

So it's generally best to stick to := for assignment to avoid confusion.

User-Defined Variable Assignment difference between MySQL and MariaDB

No, this is not possible.

In fact, the MySQL documentation warns about the use of variable assignments inside SQL statements:

The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

What's more, this is a "feature" that is subject to removal:

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

So you should not rely on that, and rewrite your queries.

What's the difference between declaring the variable before or after 'AS'?

parameters are before AS, and variables are in the declaration body which is after AS

ALTER  PROCEDURE [dbo].[GetEmployeeTakeHomePay]
    <procedure parameters>
AS 
    <local variables>

see official documentation

What is the difference between queries and questions in mysql

Try looking in the manual?

• 13.7.5.37. SHOW STATUS Syntax
• 5.1.5. Server Status Variables

unlike the Queries variable. This [Questions] variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

---

Related Topics