Home >Database >Mysql Tutorial >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!