MySQL Variables: A Comprehensive Guide

MySQL Variables

Variables play a crucial role in any programming language, allowing data storage and manipulation during execution. In MySQL, variables help in managing and passing data efficiently across different statements. Understanding variables in MySQL enhances the ability to write dynamic queries and stored procedures, making database interactions more effective.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

MySQL supports three types of variables:

• User-Defined Variables
• Local Variables
• System Variables

Let’s explore each type in detail.

1. User-Defined Variables

User-defined variables allow passing values between statements during a session. They are prefixed with @ and can hold values of types like integers, floats, decimals, strings, or NULL. These variables are session-specific, meaning they are not accessible to other sessions.

Declaring and Initializing User-Defined Variables

You can declare and assign a value to a user-defined variable using SET or SELECT statements.

Using the SET Statement

SET @customer_name = 'John Doe';

Using the SELECT Statement

SELECT @customer_age := 28;

Example 1: Assigning and Displaying a Value

SET @city = 'New York';
SELECT @city;

Output:

New York

Example 2: Using User-Defined Variables in Queries

Assume a students table exists. To find the oldest student’s details:

SELECT @max_age := MAX(age) FROM students;
SELECT first_name, last_name, age FROM students WHERE age = @max_age;

Example 3: Accessing an Undeclared Variable

SELECT @unknown_variable;

Output:

NULL

2. Local Variables

Local variables are declared within a stored procedure or function and do not require the @ prefix. Their scope is limited to the program block where they are defined.

Declaring Local Variables

Use the DECLARE statement with a datatype and an optional default value:

DECLARE total_price DECIMAL(10,2) DEFAULT 0.0;
DECLARE order_count INT DEFAULT 0;

Example: Using Local Variables in a Stored Procedure

DELIMITER //
CREATE PROCEDURE CalculateTotal()
BEGIN
    DECLARE price INT DEFAULT 200;
    DECLARE discount INT DEFAULT 50;
    DECLARE final_price INT;
    SET final_price = price - discount;
    SELECT price, discount, final_price;
END //
DELIMITER ;

Call the procedure using:

CALL CalculateTotal();

Output:

price | discount | final_price
--------------------------------
 200   | 50      | 150

3. System Variables

System variables control MySQL’s configuration and behavior. They can be Global, Session, or Mixed:

• Global Variables: Affect the entire server instance.
• Session Variables: Affect only the current session.
• Mixed Variables: Can be both global and session-based.

Viewing System Variables

To see current system variables:

SHOW VARIABLES;

To view a specific variable:

SELECT @@max_connections;

Example: Checking and Modifying a System Variable

SHOW VARIABLES LIKE 'wait_timeout';
SET GLOBAL wait_timeout = 300;

Example: Retrieving a Specific System Variable Value

SELECT @@sort_buffer_size;

Output:

sort_buffer_size | 262144

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

Conclusion

Understanding MySQL variables enhances database efficiency by allowing better data handling and query execution. Whether passing values in a session, defining local variables in stored procedures, or configuring MySQL behavior using system variables, mastering them can significantly improve database operations.

mysql variables types
mysql variables in query
mysql variables list
mysql declare variable
mysql set variable from select
mysql variables w3schools
mysql variables array
mysql> set global variable
mysql
mysql data types
mysql variables examples