User-Defined Variables
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, though they were case sensitive in MySQL 4.1 and older versions.
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;
Information Schema
User-defined variables can be viewed in the Information Schema USER_VARIABLES Table (as part of the User Variables plugin) from MariaDB 10.2.
Flushing User-Defined Variables
User-defined variables are reset and the Information Schema table emptied with the FLUSH USER_VARIABLES statement.
SET @str = CAST(123 AS CHAR(5)); SELECT * FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME; +---------------+----------------+---------------+--------------------+ | VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME | +---------------+----------------+---------------+--------------------+ | str | 123 | VARCHAR | utf8mb3 | +---------------+----------------+---------------+--------------------+ FLUSH USER_VARIABLES; SELECT * FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME; Empty set (0.000 sec)
