MySQL Variables

Master MySQL's three flavors of variables — local variables (DECLARE in BEGIN...END), user variables (@x, session-scoped), and system variables (@@x, server configuration). Covers SET vs SELECT INTO assignment, the := operator, SHOW VARIABLES, SET PERSIST in MySQL 8.0, and best practices for choosing the right kind for each scenario.

MySQL has three different kinds of variables, each with its own scope, syntax, and lifetime. They're easy to confuse — and mixing them up causes some of the most common stored-procedure bugs.

Local variables are declared inside BEGIN ... END blocks (typically inside stored procedures, functions, or triggers). They have:

• No @ prefix — just the name.
• A required data type — strongly typed, you must declare it.
• Block scope — visible only inside the BEGIN ... END they were declared in.
• An optional default via DEFAULT; otherwise initialized to NULL.

Declare a few salaries, sum them, return the result. The original tutorial had several typos in this procedure — missing semicolons, missing space between DECLARE and Lex, inconsistent variable casing — corrected below:

DROP PROCEDURE IF EXISTS salaries;

DELIMITER $$

CREATE PROCEDURE salaries()
BEGIN
    DECLARE steven INT;
    DECLARE neena  INT DEFAULT 18000;
    DECLARE lex    INT;
    DECLARE bruce  INT;
    DECLARE total  INT;

    SET steven = 25000;
    SET lex    = 20000;
    SET bruce  = 10000;
    SET total  = steven + neena + lex + bruce;

    SELECT total, steven, neena, lex, bruce;
END$$

DELIMITER ;

CALL salaries();

User variables — the @ kind — are session-scoped. They live for the duration of your connection and are visible everywhere in that session: from the CLI, from procedures called by your session, from any query you run.

Unlike local variables, they:

• Don't need declaration — first assignment creates them.
• Are loosely typed — type is inferred from the value.
• Persist across statements — set in one query, read in the next.
• Are case-insensitive — @name and @NAME are the same variable.

SET @name = 'Gowtham';
SELECT @name;

SELECT @var := value assigns and returns the value in one shot. Useful when you want to capture a query result in a variable while also displaying it:

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

-- Now use it
SELECT * FROM employees WHERE salary = @max_salary;

System variables — the @@ kind — are MySQL's configuration knobs. They control server behavior: connection limits, character sets, buffer sizes, transaction isolation, autocommit mode, and hundreds more.

Two scopes:

The SHOW VARIABLES command lists system variables. Filter with LIKE to find a specific one:

SHOW VARIABLES LIKE '%table%';

Use SELECT @@var_name to read a single system variable's current value:

SELECT @@version, @@autocommit, @@max_connections;

Many system variables are dynamic — you can change them at runtime with SET:

-- Just for this session
SET SESSION sql_mode = 'STRICT_ALL_TABLES';

-- Server-wide (requires SUPER privilege)
SET GLOBAL max_connections = 200;

-- Persistent across server restarts (MySQL 8.0+)
SET PERSIST max_connections = 200;

1. Use local variables inside procedures by default. They're typed, scoped, and predictable.
2. Watch for variable / column name shadowing. If a local variable shares its name with a table column, MySQL uses the variable in expressions but the column in WHERE clauses — easy bug to introduce. Prefix locals (v_total) or pick distinct names.
3. Reach for user variables when you need session-scoped state — running totals across queries, OUT parameter receivers, parameterized scripts.
4. Don't overuse user variables. They're not type-safe and they leak across scope boundaries; relying on them for procedural state is fragile.
5. Treat system variables as configuration. Read them freely, but be deliberate about SET GLOBAL / SET PERSIST — those changes affect the whole server.
6. Initialize local variables explicitly. DECLARE total INT DEFAULT 0; is clearer than DECLARE total INT; (which starts as NULL — and NULL + anything is still NULL).

• MySQL has three kinds of variables: local (no prefix, declared in BEGIN ... END), user (@ prefix, session-scoped), system (@@ prefix, server configuration).
• Local variables need DECLARE var [, var ...] datatype [DEFAULT value]; at the top of their block. All DECLAREs come before any other statement.
• User variables are loosely typed, case-insensitive, persist for the connection, and don't need declaration. Set with SET @x = ...; or SELECT @x := ...;.
• System variables are MySQL's configuration. Read with SELECT @@var or SHOW VARIABLES LIKE '...';; change with SET SESSION, SET GLOBAL, or SET PERSIST (8.0+).
• Use the right tool: locals for procedure-internal state, user variables for cross-statement values and OUT-parameter receivers, system variables for MySQL configuration.