Description\n-----------\n\nUser-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.\n\nUser-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:\n\n @var_name\n @'var_name'\n @"var_name"\n\nThese characters can be escaped as usual.\n\nUser-variables names are case insensitive.\n\nUser-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:\n\n SET statement;\n := operator within a SQL statement;\n SELECT ... INTO.\n\nSince user-defined variables type cannot be declared, the only way to force their type is using CAST() or CONVERT():\n\n``sql\nSET @str = CAST(123 AS CHAR(5));\n`\n\nIf a variable has not been used yet, its value is NULL:\n\n`sql\nSELECT @x IS NULL;\n+------------+\n| @x IS NULL |\n+------------+\n|          1 |\n+------------+\n`\n\nIt 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.\n\nUser-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.\n\nThey must be used to PREPARE a prepared statement:\n\n`sql\n@sql = 'DELETE FROM my_table WHERE c>1;';\nPREPARE stmt FROM @sql;\nEXECUTE stmt;\nDEALLOCATE PREPARE stmt;\n`\n\nAnother common use is to include a counter in a query:\n\n`sql\nSET @var = 0;\nSELECT a, b, c, (@var:=@var+1) AS counter FROM my_table;\n`\n\nViewing\n\nUser-defined variables can be viewed by either querying the USER_VARIABLES, or by running SHOW USER_VARIABLES`.\n\nFlushing User-Defined Variables\n\nUser-defined variables are reset and the Information Schema table emptied with the FLUSH USER_VARIABLES statement.\n\nExamples\n--------\n\nSET @v1 = 0;\nSET @v2 = 'abc';\nSET @v3 = CAST(123 AS CHAR(5));\n\nSHOW USER_VARIABLES;\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| v3            | 123   |\n| v2            | abc   |\n| v1            | 0     |\n+---------------+-------+\n\nSELECT  FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME;\n+---------------+----------------+---------------+--------------------+\n| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |\n+---------------+----------------+---------------+--------------------+\n| v1            | 0              | INT           | latin1             |\n| v2            | abc            | VARCHAR       | utf8               |\n| v3            | 123            | VARCHAR       | utf8               |\n+---------------+----------------+---------------+--------------------+\n\nFLUSH USER_VARIABLES;\n\nSELECT  FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME;\nEmpty set (0.000 sec)\n\nURL: https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/user-defined-variables