✏️ Explanatory Question
Level: Advanced — Understanding variable scope is key to writing correct stored programs and sessions.
MySQL supports several kinds of variables, distinguished mainly by their scope (how long they live and where they're accessible) and their prefix syntax.
@. Live for the entire session/connection. No declaration needed.DECLARE inside stored programs. Exist only within that BEGIN...END block. No @ prefix.@@. Control server/session configuration (e.g., @@autocommit, @@version).@ variables for values that must persist across statements in a session (e.g., capturing an OUT parameter).
| Type | Prefix | Scope | Declared? |
|---|---|---|---|
| User-Defined (Session) | @ | Whole session | No (set on use) |
| Local | none | Inside BEGIN...END | Yes (DECLARE) |
| System (Global) | @@global. | Entire server | Predefined |
| System (Session) | @@session. | Current session | Predefined |
-- 1) USER-DEFINED (@) variable: persists in the session
SET @counter = 10;
SET @name = 'Rumman';
SELECT @counter + 5 AS result; -- 15
-- Capture a query result into an @ variable
SELECT COUNT(*) INTO @total FROM employees;
SELECT @total;
-- 2) LOCAL variable: only inside a stored program
DELIMITER $$
CREATE PROCEDURE CalcBonus(IN p_id INT)
BEGIN
DECLARE v_salary DECIMAL(10,2); -- local, no @ prefix
DECLARE v_bonus DECIMAL(10,2);
SELECT salary INTO v_salary FROM employees WHERE emp_id = p_id;
SET v_bonus = v_salary * 0.10;
SELECT v_salary AS salary, v_bonus AS bonus;
END $$
DELIMITER ;
-- 3) SYSTEM (@@) variable: server/session settings
SELECT @@version; -- MySQL version
SELECT @@autocommit; -- current autocommit setting
SET SESSION @@autocommit = 0; -- change for this session@ variables are session-scoped and untyped — they take on the type of the last assigned value. Local DECLAREd variables are strongly typed and must be declared at the top of the block, before any statements.