MySQL Variables

Introduction

In this chapter, we will learn about variables in MySQL. Variables in MySQL allow you to store temporary data that can be used within the session or within a specific SQL statement. They are useful for holding intermediate results, managing control flow, and simplifying complex queries. We will cover the syntax for defining variables, examples of their usage, and important considerations for using variables in MySQL.

Types of Variables

MySQL supports two types of variables:

1. User-defined variables: These variables are prefixed with @ and are available only within the session in which they are defined.
2. Local variables: These variables are declared within a stored program (like stored procedures, functions, or triggers) using the DECLARE statement and have a scope limited to the stored program.

User-defined Variables

User-defined variables can be assigned values using the SET statement or within a SELECT statement.

Syntax

SET @variable_name = value;

or

SELECT value INTO @variable_name;

Example

Here is an example of how to use user-defined variables:

SET @total_sales = 1000;
SELECT @total_sales;

SELECT COUNT(*) INTO @customer_count FROM customers;
SELECT @customer_count;

Output

In this example, @total_sales is assigned a value of 1000 using the SET statement, and @customer_count is assigned the result of a SELECT query.

Local Variables

Local variables are declared within a stored procedure, function, or trigger and are limited to the scope of the stored program.

Syntax

DECLARE variable_name datatype [DEFAULT value];

Example

Here is an example of how to use local variables within a stored procedure:

DELIMITER $$

CREATE PROCEDURE calculate_total_sales()
BEGIN
    DECLARE total_sales DECIMAL(10, 2) DEFAULT 0.0;

    SELECT SUM(amount) INTO total_sales FROM sales;

    SELECT total_sales;
END $$

DELIMITER ;

Usage

CALL calculate_total_sales();

Output

In this example, total_sales is declared as a local variable within the calculate_total_sales stored procedure and is assigned the sum of the amount column from the sales table.

Using Variables in Queries

You can use variables in queries to simplify complex operations and control the flow of SQL statements.

Example: Using Variables in a Query

SET @min_price = 100;
SET @max_price = 500;

SELECT product_name, price
FROM products
WHERE price BETWEEN @min_price AND @max_price;

Output

In this example, @min_price and @max_price are used to filter products within a specified price range.

Important Considerations

• Session Scope: User-defined variables are session-specific and are not shared across connections. They exist only for the duration of the session.
• Data Types: User-defined variables do not have a specific data type. They can hold string, integer, decimal, or NULL values. The data type is determined by the value assigned.
• Local Scope: Local variables declared within stored programs are not accessible outside their scope and cease to exist once the stored program completes execution.
• Initialization: It is a good practice to initialize variables to avoid unexpected results.

Conclusion

Variables in MySQL are powerful tools for managing temporary data and controlling the flow of SQL operations. This chapter covered the syntax for defining and using user-defined and local variables, provided examples of their usage, and discussed important considerations.