Working with Variables in MySQL

Understanding Variables in MySQL

MySQL provides several ways to store and manipulate data using variables, offering flexibility in queries and stored programs. This tutorial explores the different types of variables available in MySQL, how to declare and assign values to them, and how to utilize them effectively in your SQL code.

Types of Variables

MySQL supports three main types of variables:

1. User-Defined Variables: These are session-specific and denoted by a prefix @. They are dynamically typed, meaning the data type is determined by the assigned value. You don’t need to declare them before using; simply assigning a value creates the variable.
2. Local Variables: These are declared within a stored procedure or function using the DECLARE keyword. They are strongly typed and scoped to the block of code in which they are defined.
3. System Variables: These are maintained by the MySQL server and control the server’s operation. They can be global (affecting the entire server), session-specific (affecting a single client connection), or both.

User-Defined Variables

User-defined variables are convenient for storing intermediate results or dynamically altering query behavior within a session.

Assigning Values:

You can assign values to user-defined variables using the SET statement:

SET @start_value = 10;
SET @end_value = 20;

Alternatively, you can assign values within a SELECT statement:

SELECT column1, @my_variable := column2 
FROM your_table;

This assigns the value of column2 to the variable @my_variable for each row. Be mindful that if multiple rows are returned, the last value of column2 will be stored in @my_variable.

Using User-Defined Variables:

Once assigned, you can use user-defined variables in your queries:

SELECT * 
FROM your_table
WHERE column_name BETWEEN @start_value AND @end_value;

Important Considerations:

• User-defined variables are session-specific, meaning they are only available within the current connection.
• They are dynamically typed, so their data type is determined by the assigned value.
• They don’t require explicit declaration.

Local Variables

Local variables are used within stored procedures, functions, and other code blocks to provide localized storage and maintain code readability.

Declaring Local Variables:

You must declare local variables using the DECLARE keyword at the beginning of a BEGIN...END block:

DELIMITER //

CREATE PROCEDURE my_procedure()
BEGIN
  DECLARE start_value INT UNSIGNED DEFAULT 1;
  DECLARE end_value INT UNSIGNED DEFAULT 10;

  SELECT *
  FROM your_table
  WHERE column_name BETWEEN start_value AND end_value;
END //

DELIMITER ;

• DECLARE must be the first statement within a BEGIN...END block.
• You must specify the data type of the variable (e.g., INT, VARCHAR, DECIMAL).
• You can optionally assign a default value using the DEFAULT keyword. If no default value is specified, the variable will be initialized to NULL.

Using Local Variables:

Local variables are used within the scope of the BEGIN...END block in which they are declared.

System Variables

System variables control the behavior of the MySQL server. They can be global (affecting all connections), session-specific (affecting only the current connection), or both.

Accessing System Variables:

You can view the current values of system variables using:

SHOW VARIABLES LIKE 'wait_timeout';  -- Example
SELECT @@wait_timeout;            -- Another example

Setting System Variables:

You can modify system variables using:

SET GLOBAL wait_timeout = 600;   -- Sets the global value
SET SESSION wait_timeout = 600;  -- Sets the session value

Be cautious when modifying global system variables, as it can affect the performance and stability of the server.