MySQL - Variables

MySQL - Working with Variables

Variables are named containers that store data values. In programming and scripting, they allow you to store a result, manipulate it, and reference it later. MySQL supports several types of variables, each with a different scope and use case, making your SQL scripts more dynamic and powerful.

Understanding the different types of variables is key to writing advanced queries, stored procedures, and administrative scripts. MySQL has three main categories of variables:

• User-Defined Variables: Session-specific variables that you can set and use across multiple statements.
• Local Variables: Variables used inside stored procedures, functions, and triggers, with a scope limited to the BEGIN...END block they are declared in.
• System Variables: Pre-defined server configuration variables that control the behavior of the MySQL server.

User-Defined Variables

User-defined variables are specific to your current user session. They are prefixed with a single @ symbol (e.g., @my_variable). Once you set a user-defined variable, it exists until your session ends (you disconnect). They are loosely typed, meaning you don’t declare a data type; it’s inferred from the value you assign.

Syntax for Setting and Using

You can assign a value using either the SET statement with = or a SELECT statement with the := assignment operator.

-- Using SET (preferred for simple assignment)
SET @my_name = 'Alice';

-- Using SELECT (useful for assigning from a query result)
SELECT @max_salary := MAX(salary) FROM employees;

Example: Multi-Step Query

Let’s use the employees table from previous examples. We want to find which employee(s) earn the highest salary. We can do this in two steps using a variable:

First, find the maximum salary and store it in a variable.

SELECT @max_salary := MAX(salary) FROM employees;

Second, use that variable to find all employees who earn that salary.

SELECT NAME, SALARY FROM employees WHERE SALARY = @max_salary;

Output

This will show the employee(s) with the top salary without needing a subquery.

NAME	SALARY
Charlie	81000.00

Local Variables

Local variables are used within stored programs like procedures, functions, events, and triggers. They are declared using the DECLARE keyword, are strongly typed, and have no prefix. Their scope is limited to the BEGIN...END block in which they are declared.

Syntax

DECLARE variable_name DATATYPE [DEFAULT default_value];

Example: Inside a Stored Procedure

Let’s create a stored procedure that calculates the total salary for a given department. We’ll use a local variable total_salary to store the sum.

-- Change the delimiter to allow for semicolons inside the procedure
DELIMITER $$

CREATE PROCEDURE GetDepartmentSalary(IN dept_name VARCHAR(50))
BEGIN
    -- Declare a local variable of type DECIMAL
    DECLARE total_salary DECIMAL(15, 2) DEFAULT 0.00;

    -- Calculate the sum and store it in the local variable
    SELECT SUM(SALARY) INTO total_salary
    FROM employees
    WHERE DEPARTMENT = dept_name;

    -- Return the result
    SELECT total_salary AS total_for_department;
END$$

-- Reset the delimiter back to default
DELIMITER ;

Now, you can call the procedure to get the total salary for the ‘Engineering’ department:

CALL GetDepartmentSalary('Engineering');

Output

total_for_department
156000.00

System Variables

System variables control the configuration and operation of the MySQL server. They can be viewed and, in many cases, modified at runtime. They have two scopes: GLOBAL and SESSION.

• GLOBAL: A global setting affects the server’s overall behavior. Changing a GLOBAL variable requires the SUPER privilege and applies to all new connections made to the server after the change.
• SESSION: A session setting only affects the current client connection. Any user can change session variables, and the changes are discarded when the session ends.

Syntax for Viewing and Setting

-- Show all system variables
SHOW VARIABLES;

-- Show variables matching a pattern
SHOW GLOBAL VARIABLES LIKE '%time_zone%';

-- View a specific system variable's value (note the @@ prefix)
SELECT @@sql_mode;
SELECT @@GLOBAL.max_connections;

-- Set a system variable for the current session
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE';

-- Set a system variable globally (requires SUPER privilege)
SET GLOBAL max_connections = 200;

Example: Changing the Time Zone

Let’s check the current time zone for our session and then change it.

-- Check the current time zone
SELECT @@session.time_zone; -- Might show 'SYSTEM'

-- Set the time zone for the current session to UTC
SET SESSION time_zone = '+00:00';

-- Verify the change
SELECT @@session.time_zone; -- Now shows '+00:00'

Comparison of Variable Types

Feature	User-Defined Variable	Local Variable
Syntax	@variable_name	variable_name (no prefix)
Declaration	Not declared, assigned directly with SET or SELECT	Declared with DECLARE at the start of a BEGIN...END block
Scope	Entire connection session	The BEGIN...END block where it is declared
Typing	Loosely typed	Strongly typed (requires a data type)
Use Case	Passing values between statements in a single session	Logic within stored procedures, functions, and triggers