MySQL Variables

Local variables

• Variables in the local environment
• It's a variable that doesn't have the @ symbol before it.
• The local variable is a highly typed variable.
• The scope of the local variable is set in the saved program block where it is declared.
• MySQL declares the local variable with the DECLARE keyword.
• The DECLARE statement also has a DEFAULT clause that specifies a variable's default value.
• The default value is NULL if the DEFAULT clause is omitted. Stored procedure programs are the most typical use of it.

Syntax

We can declare Local variables as follows

DECLARE variable_name datatype(size) [DEFAULT default_value];

Let's look at some of the examples of local variables.

This is how you can declare a variable

mysql> DECLARE number INT DEFAULT 0;

We can also declare more than one variable with the same data type using a single DECLARE statement.

mysql> DECLARE p,q,r,s INT DEFAULT -1;

This is how you can use the DECLARE statement in a stored procedure.

DELIMITER//
Create Procedure Example()
BEGIN 
    DECLARE P INT DEFAULT 50;
    DECLARE Q,R,S INT;
    SET Q =  12;
    SET R =  45;
    SET S =  P+Q-R;
    SELECT P,Q,R,S;
END//
DELIMITER;

Now on calling the above function:

mysql> CALL Example();

The output will be as follows:

System variables

• All programming units are members of the system variables class, which contains predefined variables.
• There are a lot of system variables in MySQL that influence how it works, and each one has a default value.
• We can change numerous system variables dynamically using the SET command during runtime. It enables us to modify the server's functionality without having to shut it down and restart it.
• In addition, you can use the system variable in expressions.
• The GLOBAL, SESSION and MIX kinds are among the system variables provided by the MySQL server. The GLOBAL variable is visible throughout the life of the server, but the SESSION variable is only active for a single session.

We can see the values and their names used by the running server by using the following commands.

Commands to see the current values used by the running server.

mysql> SHOW VARIABLES;

OR

mysql> SELECT @@variable_name;

Use the following command to see the values depending on the system's built-in defaults.

mysql> mysqld –verbose –help

Example 1

mysql> SELECT @@key_buffer_size;

Output

mysql> SELECT @@key_buffer_size;

@@key_buffer_size

4732877

1 row in set (0.00 sec)

Example 2

mysql> SHOW VARIABLES LIKE ‘%wait_timeout%’;

Output

mysql> SHOW VARIABLES LIKE ‘%wait_timeout%’;

4 rows in set (0.01 sec)

FAQs

1. Which operators are used as assignment operators in the set statement
   The operators used to assign value to the variable in the set statement are ‘=’ and ’:=’.
    
2. What types of system variables are provided by the MySQL server?
   There are a variety of system variables provided by the MySQL server and those include GLOBAL, SESSION, and MIX types.
    
3. Which system variables aren’t preceded by @ symbol.
   Local variables are not preceded by the @ symbol.
    
4. In MySQL, what is @@?
   @@ - System Variable, When you refer to a system variable in an expression as @@var name (without specifying @@global. or @@session. ), MySQL returns the session value if it exists, and the global value if it does not. (This is in contrast to the SET @@var name = value command, which always refers to the session value.)
    
5. In MySQL, how can I assign a variable?
   A user-defined variable can be assigned a value in one of two ways. In the SET statement, the assignment operator can be either:= or =. The statement, for example, sets the value 100 to the variable @counter. The SELECT statement is the second approach to assign a value to a variable.

Key Takeaways

In the above session we have learned about user-defined variables, system variables, and local variables in MySQL. we have also looked at the examples. Hope you learned something. 

Recommended Readings:

• Update Query in MySQL
• MySQL Case Statement

But the knowledge never stops, so to better understand the Database management system, you can go through many articles on our platform. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Happy Learning Ninja :)