# 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](https://mariadb.com/docs/server/server-usage/stored-routines).

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](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set) statement;
* [:=](https://mariadb.com/docs/server/reference/sql-structure/operators/assignment-operators/assignment-operator) operator within a SQL statement;
* [SELECT ... INTO](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/selectinto).

Since user-defined variables type cannot be declared, the only way to force their type is using [CAST()](https://mariadb.com/docs/server/reference/sql-functions/string-functions/cast) or [CONVERT()](https://mariadb.com/docs/server/reference/sql-functions/string-functions/convert):

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

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

```sql
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](https://mariadb.com/docs/server/sql-statements/data-manipulation/selecting-data/select#limit) clause.

They must be used to [PREPARE](https://mariadb.com/docs/server/reference/sql-statements/prepared-statements/prepare-statement) a prepared statement:

```sql
@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:

```sql
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](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-user_variables-table), or by running `SHOW USER_VARIABLES`.

## Flushing User-Defined Variables

User-defined variables are reset and the [Information Schema table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-user_variables-table) emptied with the [FLUSH USER\_VARIABLES](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/flush-commands/flush) statement.

## Examples

```sql
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](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/declare-variable)
* [Performance Schema user\_variables\_by\_thread Table](https://mariadb.com/docs/server/reference/system-tables/performance-schema/performance-schema-tables/performance-schema-user_variables_by_thread-table)
* [Information Schema USER\_VARIABLES Table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-user_variables-table)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}