Working with Data in MySQL: When to Use User-Defined Variables and System Variables

The key difference is in scope and persistence:

Here's a table summarizing the difference:

Example:

-- Set a user-defined variable
SET @product_id = 123;

-- Use the user-defined variable in a query
SELECT * FROM products WHERE product_id = @product_id;

-- System variable (read-only)
SELECT @@version; -- This shows the MySQL version

This code calculates the total price of an order by multiplying the quantity of an item by its unit price and stores the result in a user-defined variable.

-- Set user-defined variables for quantity and unit price
SET @quantity = 5;
SET @unit_price = 10.50;

-- Calculate total price using variables
SET @total_price = @quantity * @unit_price;

-- Display the total price
SELECT @total_price AS 'Total Price';

System variable (read-only):

This code retrieves the current MySQL server version using a system variable.

-- Get the MySQL version
SELECT @@version AS 'MySQL Version';

System variable (modifiable):

This code temporarily increases the connection timeout for the current session using a system variable. Remember to adjust the timeout value (new_timeout) as needed.

-- Set the original connection timeout (store for reference)
SELECT @@session.wait_timeout AS 'Original Timeout';

-- Set a new connection timeout (temporary for this session)
SET @@session.wait_timeout = 300;  -- Change timeout value (in seconds)

-- Display the new timeout value
SELECT @@session.wait_timeout AS 'New Timeout';

-- (Perform operations that might take longer)

-- Reset the connection timeout to the original value (optional)
SET @@session.wait_timeout = (SELECT @original_timeout);

Subqueries allow you to embed a complete SELECT statement within another statement. This can be useful for retrieving a value you need within the main query without relying on a separate user-defined variable.

Instead of using a user-defined variable for the unit price:

-- Set user-defined variable for unit price (original)
SET @unit_price = 10.50;

-- Use the variable in the main query
SELECT * FROM products 
WHERE product_id = 123
AND price = @unit_price;

You can use a subquery:

-- Use a subquery to retrieve unit price
SELECT * FROM products 
WHERE product_id = 123
AND price = (SELECT unit_price FROM product_prices WHERE product_id = 123);

Derived Tables (Common Table Expressions - CTE):

Derived tables (also known as Common Table Expressions or CTEs) allow you to define temporary result sets within a single query. This can be helpful for complex calculations or filtering data before using it in the main query.

Imagine you need to calculate the total price for multiple products with different quantities. Instead of multiple user-defined variables:

-- Set user-defined variables for quantities (original)
SET @quantity1 = 2;
SET @quantity2 = 3;

-- Use variables in the main query
SELECT product_name, price * @quantity1 AS 'Total Price (Product 1)', price * @quantity2 AS 'Total Price (Product 2)'
FROM products
WHERE product_id IN (101, 102);

You can use a derived table (CTE):

WITH product_quantities AS (
  SELECT product_id, quantity
  FROM order_details
)
SELECT p.product_name, p.price * pq.quantity AS 'Total Price'
FROM products p
INNER JOIN product_quantities pq ON p.product_id = pq.product_id
WHERE p.product_id IN (101, 102);

Stored Procedures and Functions:

For complex logic involving multiple variables and calculations, stored procedures and functions offer a more structured and reusable approach. They can accept parameters as input and potentially return values, eliminating the need for user-defined variables within the calling code.