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)

URL: https://mariadb.com/kb/en/user-defined-variables/