How to Declare and Use Variables in MySQL

MySQL has three different variable mechanisms. Use a user-defined session variable such as @limit when several statements in one connection need to share a value. Use a local variable without @ when writing a stored procedure, function, trigger, or event. Use a system variable such as @@SESSION.sql_mode when you are inspecting or configuring the MySQL server or one client session.

The most important distinction is that DECLARE is not a general-purpose statement for an ad hoc SQL window. It is allowed only inside a BEGIN ... END compound statement and must appear before the other statements in that block. For a quick script, start with SET @name = value;.

Choose the right MySQL variable

These forms are not interchangeable. In particular, DECLARE @name INT is not the syntax for a local variable, and DECLARE name INT fails if you run it as a standalone statement outside a stored program.

Use a user-defined variable in a SQL session

User-defined variables start with @. You do not declare them before use. Assign a literal or expression with SET, then refer to the variable in a later statement:

SET @low = 2, @high = 4;
SELECT n FROM demo_numbers WHERE n BETWEEN @low AND @high ORDER BY n;

The variables are private to the current connection. Another client cannot use them, and they disappear when the connection ends. An uninitialized user variable evaluates to NULL, so initialize variables at the beginning of a reusable script instead of depending on a previous statement.

SET is the clearest assignment form. MySQL also accepts := in a SET statement:

SET @total = 10;
SET @total := @total + 5;
SELECT @total;

User variables can hold values such as numbers, decimal values, strings, binary strings, or NULL. Their type is associated with the value assigned, rather than declared in a datatype clause. For predictable calculations, assign compatible values and use explicit casts when a value crosses a type boundary.

Load a query result with SELECT ... INTO

Use SELECT ... INTO when the value comes from a query. The number of target variables must match the number of selected expressions:

SELECT COUNT(*) INTO @number_of_rows
FROM demo_numbers;

SELECT @number_of_rows;

The query should be designed to return one row. An aggregate such as COUNT(*) naturally does that. A lookup by a unique key is another good fit. If a query can return several rows, decide which row you want and use a deterministic ORDER BY together with LIMIT 1; do not hide an accidental many-row result.

Do not use a user variable as a table or column identifier. SET @table_name = 'demo_numbers'; SELECT * FROM @table_name; does not substitute the table name. If dynamic SQL is genuinely required, construct a statement for PREPARE and validate any identifier supplied by the application.

MySQL 9.7 still supports assigning user variables in statements other than SET for backward compatibility, but the manual marks that behavior for removal in a future release. Prefer SET and SELECT ... INTO instead of expressions such as SELECT @n := n.

Declare a local variable in a stored program

Local variables use DECLARE without @ and require a MySQL datatype. The declaration can include DEFAULT; otherwise the initial value is NULL. Put variable declarations at the beginning of the BEGIN ... END block, before executable statements. They are visible in that block and nested blocks, but not after the block ends.

The following procedure uses an input parameter, a local variable, and an output parameter. The DELIMITER commands are for the mysql client: they let the client send the semicolon-containing procedure body as one CREATE PROCEDURE statement.

DROP PROCEDURE IF EXISTS count_numbers;
DELIMITER //
CREATE PROCEDURE count_numbers(IN p_min INT, OUT p_count INT)
BEGIN
  DECLARE local_count INT DEFAULT 0;
  SELECT COUNT(*) INTO local_count
  FROM demo_numbers
  WHERE n >= p_min;
  SET p_count = local_count;
END//
DELIMITER ;
CALL count_numbers(3, @result);
SELECT @result;

The procedure’s local_count exists only while the procedure runs. The OUT parameter is returned through the session variable supplied to CALL, so the caller can read it afterward. If you need to assign a local variable from multiple columns, provide one target per selected expression and handle the possibility of no matching row in the routine’s logic.

You can declare several variables of the same type in one statement, but separate declarations are often easier to maintain:

DECLARE quantity, retries INT DEFAULT 0;
DECLARE label VARCHAR(100) DEFAULT 'pending';

DECLARE also defines conditions, cursors, and handlers. Their order matters: variables and conditions come before cursors, and cursors come before handlers. This is another reason a declaration placed after a SELECT or SET statement produces a syntax error.

Understand the SELECT ... INTO row boundary

A scalar target can store one value, not an arbitrary result set. The following query intentionally returns three rows and therefore fails with error 1172:

SET @selected = 99;
SELECT n INTO @selected FROM demo_numbers ORDER BY n;

ERROR 1172 (42000) at line 6: Result consisted of more than one row

Fix the query by narrowing it to one known row, aggregating it, or changing the design so the result remains a result set instead of a variable. LIMIT 1 is appropriate only when the selected row is defined by an intentional ordering:

SELECT n INTO @selected
FROM demo_numbers
ORDER BY n DESC
LIMIT 1;

The same one-row rule applies to a local variable populated with SELECT ... INTO. A routine that can legitimately find no row should initialize its variable deliberately and use a NOT FOUND handler or an existence check when that distinction matters.

Inspect or set a MySQL system variable

System variables configure the server or a client connection. Inspect them with SHOW VARIABLES or SELECT and an @@ qualifier:

SHOW SESSION VARIABLES LIKE 'sql_mode';
SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

With no scope keyword, SHOW VARIABLES uses the current session. A session value affects only the current connection. A global value is used by the server and generally initializes the value for new connections. A client needs the appropriate privilege to change a restricted system variable globally; setting a session variable changes only that client. Many settings can also be configured at startup or persisted through the supported server configuration mechanism, so a temporary SET SESSION change is not a durable server configuration.

Use the scope explicitly when the distinction matters:

SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
-- A privileged server administrator may use an appropriate GLOBAL setting.
-- SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

The commented global statement is intentionally not part of the runnable fixture: whether it succeeds depends on the account’s privileges and the server variable’s scope and mutability.

Troubleshoot a variable that does not work

• If DECLARE is rejected, check that it is inside BEGIN ... END and before every executable statement. For an ordinary SQL script, use SET @name = value.
• If a variable unexpectedly contains NULL, initialize it and check whether the source expression or lookup produced NULL.
• If SELECT ... INTO reports error 1172, make the query return exactly one row or keep the result as rows.
• If another connection cannot see @name, that is expected: user-defined variables are session-specific.
• If changing @@GLOBAL.name fails, check the variable’s mutability and your administrative privilege instead of changing a local variable’s syntax.

The tested success fixture assigns session variables, reuses them in a filter, declares a local variable in a procedure, and returns its output parameter. The boundary fixture verifies the multiple-row SELECT ... INTO diagnostic. Both use a temporary MySQL server and the same SQL behavior described above.

For the complete syntax and version-specific restrictions, see the MySQL user-defined variable reference, local DECLARE reference, and SET variable-assignment reference.