Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
User-Defined variables    
3.
Local variables
3.1.
Syntax
4.
System variables
5.
FAQs
6.
Key Takeaways
Last Updated: Mar 27, 2024

MySQL Variables

Author Divyansh Jain
0 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

While a program is running, variables are used to store data or information. It's a way of marking data with a meaningful term that helps the reader understand the program. The main purpose of the variable is to keep data in memory so that it can be used later in the program.

There are three different types of MySQL variables.

  1. User-defined variables
  2. Local variables
  3. System variables

Paramount Network Denim GIF by Yellowstone

Source: Yellowstone

User-Defined variables    

  • You may need to send a value from one SQL statement to another at times. This is accomplished by storing the value in a MySQL user-defined variable in the first statement and referencing it in future statements.
  • The format @variable name, where the variable name comprises alphanumeric characters, is used to create a user-defined variable. As of MySQL 5.7.5, the maximum length of a user-defined variable is 64 characters.
  • The case of the user-defined variables is unimportant. It indicates that the @id and @ID are identical.
  • You can set the data type of the user-defined variable to integer, floating-point, decimal, string, or NULL.
  • Other clients cannot see a user-defined variable defined by one client. In other words, a session-specific user-defined variable.
  • It's important to note that user-defined variables are a MySQL-specific addition to the SQL standard. Other database systems may or may not have them.
  • You can use either:= or = as the assignment operator in the SET statement.\
  • Following the assignment, you can use the variable in any statement that allows expressions, such as the WHERE clause, INSERT, or UPDATE statement.

 

User-defined variables can be assigned a value in two ways

SET @var_name := value;

or

SELECT @var_name := value;

Example 1

mysql> SET @name =’jack’;

We can display using SELECT statement.

mysql> SELECT @name;

Output

mysql> SET @name = ‘jack’;
Query OK, 0 rows affected(0.01 sec)

mysql> SELECT @name;
@name
jack

 

1 row in set (0.00 sec)

Example 2 

NULL output will be obtained when the undeclared variable is accessed.

mysql> SELECT @variable;

Output

mysql> SELECT @variable;
@variable
0x

 

1 row in set (0.00 sec)
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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:

  P Q R S
> 50 12 45 17

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%’;
Variable_name Value

Innodb_lock_wait_timeout

Lock_wait_timeout

Mysql_wait_timeout

wait_timeout

32

2162300

451000

156000

 

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:

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 :) 

Live masterclass