User-Defined Variables

Session-scoped user-defined variables (@var), and how to set and use them.

User-defined variables are variables which can be created by the user and exist in the session. This means that no one can access user-defined variables that have been set by another user, and when the session is closed these variables expire. However, these variables can be shared between several queries and stored programs.

User-defined variables names must be preceded by a single at character (@). While it is safe to use a reserved word as a user-variable name, the only allowed characters are ASCII letters, digits, dollar sign ($), underscore (_) and dot (.). If other characters are used, the name can be quoted in one of the following ways:

• @var_name

• @'var_name'

• @"var_name"

These characters can be escaped as usual.

User-variables names are case insensitive.

User-defined variables cannot be declared. They can be read even if no value has been set yet; in that case, they are NULL. To set a value for a user-defined variable you can use:

• SET statement;

• := operator within a SQL statement;

• SELECT ... INTO.

Since user-defined variables type cannot be declared, the only way to force their type is using CAST() or CONVERT():

SET @str = CAST(123 AS CHAR(5));

If a variable has not been used yet, its value is NULL:

SELECT @x IS NULL;
+------------+
| @x IS NULL |
+------------+
|          1 |
+------------+

It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined.

User-defined variables can be used in most MariaDB's statements and clauses which accept an SQL expression. However there are some exceptions, like the LIMIT clause.

They must be used to PREPARE a prepared statement:

@sql = 'DELETE FROM my_table WHERE c>1;';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Another common use is to include a counter in a query:

SET @var = 0;
SELECT a, b, c, (@var:=@var+1) AS counter FROM my_table;

Viewing

User-defined variables can be viewed by either querying the USER_VARIABLES, or by running SHOW USER_VARIABLES.

Flushing User-Defined Variables

User-defined variables are reset and the Information Schema table emptied with the FLUSH USER_VARIABLES statement.

Examples

SET @v1 = 0;
SET @v2 = 'abc';
SET @v3 = CAST(123 AS CHAR(5));

SHOW USER_VARIABLES;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| v3            | 123   |
| v2            | abc   |
| v1            | 0     |
+---------------+-------+

SELECT * FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME;
+---------------+----------------+---------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| v1            | 0              | INT           | latin1             |
| v2            | abc            | VARCHAR       | utf8               |
| v3            | 123            | VARCHAR       | utf8               |
+---------------+----------------+---------------+--------------------+

FLUSH USER_VARIABLES;

SELECT * FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME;
Empty set (0.000 sec)

See Also

• DECLARE VARIABLE

• Performance Schema user_variables_by_thread Table

• Information Schema USER_VARIABLES Table

_{This page is licensed: CC BY-SA / Gnu FDL}