How to declare a variable in MySQL?

Scope: Session vs Local Variables

In MySQL, variable scope matters. There are session and local variables.

Session Variables: @var

Session variables are user-defined and are specific to the session they're created in. Use the SET or SELECT statement to declare and assign values.

SET @my_var := 'session value'; 
-- Is it a coincidence that cookies are also saved per session? 🍪


Remember to precede the variable name with @.

Local Variables: var

Local variables are declared within stored procedures or BEGIN ... END blocks. They use DECLARE and don't need the @ prefix.

BEGIN
  DECLARE local_var VARCHAR(100);
  SET local_var = 'local_value';
  -- It won't leave this block, kind of like my dog, Rover 🐶
END;


Local variables scope are bounded to the block they're declared in.

Use of Server Settings with SET GLOBAL/SESSION

If you need to dynamically adjust server settings, use SET GLOBAL or SET SESSION.

SET GLOBAL max_connections = 500;
-- Global setting, like a world tour 🌍
SET SESSION sql_mode = 'TRADITIONAL'; 
-- Session setting, like a private concert 🎻


You can view these settings using SHOW VARIABLES or by selecting a specific variable SELECT @@var_name.

Working with User-Defined Variables

User-defined variables are not only session-bound, but also loosely typed, making them flexible for various data manipulations.

Directly Assigning Values

You can directly assign values using SET:

SET @var = 'value';
-- Simple as a potato, but as versatile as French fries! 🍟


Select Values into Variables

Assign values via SELECT:

SELECT 'value' INTO @var_name;
-- SELECT is not just for querying, you know 😉


Storing Query Results

Save results of SELECT queries into a variable:

SELECT column INTO @var FROM table WHERE condition;
-- Why write when you can SELECT? 🙌


Using Variables in WHERE Clause

variables can be used in the WHERE clause for dynamic filtering.

SET @id = 5;
SELECT * FROM table WHERE id = @id;
-- No hardcoded ids here! 💾


Handling NULLs

Handle potential NULLs by using IFNULL(@var, default_value);.

SET @might_be_null := NULL;
SELECT IFNULL(@might_be_null, "Fallback value");
-- Just like a parachute, you might hope to never need it, but... 🪂


Variables in Triggers

To update fields in triggers, variables are there for you:

CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
   SET NEW.column_name = @var_name;
   -- Triggers and variables, a match made in MySQL! ❤️
END;


Advanced Tips

While the fundamentals are widely known, mastering the nuances can give you an edge in handling complex situations.

Multiple Parameter Assignment

Assign values to an entire list variables in a single SET statement:

SET @a = 10, @b = @a + 5, @c = @b * @a;
-- The 'SET' domino effect! 🎳


Prepared Statements

Take advantage of user variables in dynamic PREPARE statements:

SET @s = 'SELECT SQRT(POW(?, 2) + POW(?, 2)) AS hypotenuse';
PREPARE stmt FROM @s;
SET @a = 3;
SET @b = 4;
EXECUTE stmt USING @a, @b;
-- SQL and Pythagoras, both classics in their own rights! 🧮


Mastering Joins

Using variables in JOIN conditions requires care to avoid full table scans:

SELECT *
FROM tbl JOIN (
  SELECT @rownum := @rownum + 1 AS rank, results.*
  FROM (SELECT @rownum := 0) r, tbl
  WHERE condition1
  ORDER BY col1
) results USING (col2)
WHERE results.rank = tbl.rank;
-- Who doesn't like a good race where it's variables vs. columns?  🏁
