User-defined variables vs. local variables in MySQL?

User-defined variables are also called session-specific variables. It is a loosely typed variable that can be initialized somewhere in the session and contains the value of the user-defined variable until the end of the session.

User-defined variables are prefixed with the symbol @. For example:

@anyVariableName;

There are two ways to initialize user-defined variables. You can use the SET command or use a SELECT query. The first method is as follows:

SET @anyVariableName=anyValue;

The second method is as follows:

SELECT @anyVariableName :=anyValue;

If you do not use a colon (:) in the SELECT query, it will be evaluated as an expression. The result is either true or false:

mysql> select @m=10;

The following is the output:

+-------+
| @m=10 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

Local variables can be used in stored procedures, functions, etc., together with the DECLARE keyword. No @ prefix is ​​required like user-defined variables.

The syntax of local variables is as follows.

DECLARE yourVariableName dataType;

Note: The main difference between local variables and user-defined variables is that local variables are reinitialized with NULL values ​​every time the stored procedure is called, while session-specific variables or user-defined variables Defining variables does not. Not reinitialized with NULL. User-defined variables set by one user cannot be seen by other users. Any session variables for a given user are automatically destroyed when the user exits.

Here is a demonstration of session-specific variables and local variables with stored procedures. The query to create a stored procedure with local variables and user-defined variables is as follows:

mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_LocalAndUserDefinedVariableDemo()
   -> BEGIN
   -> DECLARE localVariable int default 10;
   -> SET localVariable=localVariable+10;  
   -> SET @userVariable=@userVariable+10;
   -> SELECT localVariable;
   -> SELECT @userVariable;
   -> END;
   -> //
Query OK, 0 rows affected (0.39 sec)
mysql> DELIMITER ;

Now set the value of the user-defined variable. The query is as follows:

mysql> SET @userVariable=10;
Query OK, 0 rows affected (0.00 sec)

Now call the stored procedure. On the first call, the user-defined variable is 10 10=20 and the local variable is 10 10=20.

Use the call command to call the stored procedure:

mysql> CALL sp_LocalAndUserDefinedVariableDemo();

The following is the output:

+---------------+
| localVariable |
+---------------+
|            20 |
+---------------+
1 row in set (0.32 sec)
+---------------+
| @userVariable |
+---------------+
|            20 |
+---------------+
1 row in set (0.34 sec)
Query OK, 0 rows affected (0.36 sec)

In the second call, the user-defined variable will hold the value 20 and add 10, For example, 20 10=30, and the local variable is reinitialized with 10 again and 10 is added, such as 10 10=20.

Call the stored procedure and check the sample output:

mysql> CALL sp_LocalAndUserDefinedVariableDemo();

Below is the output:

+---------------+
| localVariable |
+---------------+
|            20 |
+---------------+
1 row in set (0.00 sec)
+---------------+
| @userVariable |
+---------------+
|            30 |
+---------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.02 sec)

On the third call, the user defined variable will hold the value 30 and add 10 , like 30 10=40, and the local variable is reinitialized again with 10 and adds 10, like 10 10=20.

Now you can say that in every procedure call the local variable is re-initialized with some value, that value may be NULL or something else, like in my case I provided the default value 10. This means that it sets local variables to the value 10 on every procedure call, while user-defined variables do not.

The above is the detailed content of User-defined variables vs. local variables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!