MySQL: @variable vs. Variable. What’s the difference?

In this post, we will get to know the difference between @variable and Variable. where we use the ‘@ ‘ sign only for showing that that particular variable is available only for that particular session only.  Something same to local and global variables.

On the other hand, variables defined without ‘@’ are user-defined variables, which means they exist only within the context of the query. These variables are not prefixed with the ‘@’ symbol, such as ‘my_variable’, and can be used within the query where they are defined.

Variable

Variables

As these are simply two different ways to declare the variable for the declaration of the variable using both methods we have taken the example in which we can simply make the difference as per our choice.

SET @session_ variable = 'Hello';
SELECT @session_ variable;  -- output: Hello

SELECT @session_ variable;  -- output: NULL

In this example, the ‘@session_variable’ is defined as a session variable using the SET statement. The variable can then be used within the same session as shown in the first SELECT statement. However, when the second SELECT statement is executed in a new session, the variable is no longer available and the output is NULL.

SELECT @user_ variable := 'World';
SELECT @user_ variable;  -- output: World

SELECT @user_ variable;  -- output: NULL

In this example, the ‘user_variable’ is defined as a user-defined variable within the context of the first SELECT statement. The variable can then be used within the same query as shown in the second SELECT statement. However, when the third SELECT statement is executed, the variable is no longer available and the output is NULL.

Variables defined with ‘@’ are session variables, which means they exist only for the duration of the current session. These variables are prefixed with the ‘@’ symbol, such as ‘@my_variable’, and can be used throughout the current session.

There are a few things to be noted here which areas followed.

  1. Session variables can be used across multiple queries within the same session. For example, you can set a session variable in one query and use it in another query within the same session.
  2. User-defined variables can only be used within the same query where they are defined. They cannot be used across multiple queries.
  3. Session variables can be used to store the result of a query for later use. For example, you can assign the result of a SELECT statement to a session variable and then use that variable in a later query.
  4. User-defined variables are assigned using the:= operator, while session variables are assigned using the SET statement.
  5. User-defined variables can be used to simplify complex queries. For example, you can assign a complex expression to a variable and then use that variable in the query instead of repeating the expression multiple times.

 

 

To learn more about the difference between @variable and Variable and drow the main difference visit: the difference bt stack over flow.

To learn more about MySQL tutorials and the solutions to the problems along with concepts and tutorials of solutions list and learn visit: MySQL Tutorials And Problems.

 

Leave a Comment

%d bloggers like this: