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
