versioned_docs/version-4.x/sql-manual/basic-element/variables.md

---
{
    "title": "Variables",
    "language": "en",
    "description": "In Doris, variables are divided into system variables and user variables. Both are case-insensitive."
}
---

## Description

In Doris, variables are divided into system variables and user variables. Both are case-insensitive.

System variables affect the behavior of Doris. Both system variables and user variables can be used in user queries.

## System Variables

System variables are a set of variables predefined by Doris to control database behavior and performance. The main features are as follows:

- Variable Types:

    - Read-only variables: These variables are set by the system and cannot be modified by users, such as `version`, `current_timestamp`, etc.

    - Modifiable variables: Users can modify the values of these variables at runtime, such as `exec_mem_limit`, `time_zone`, etc.

- Scope:

    - Global variables (Global): Affect all sessions and set with `SET GLOBAL`.

    - Session variables (Session): Only affect the current session and set with `SET`.

    - Some variables have both global and session scopes.

- Access Methods:

    - Use `SHOW VARIABLES` to view all system variables.

    - Use `SHOW VARIABLES LIKE 'pattern'` to view specific variables by pattern matching.

    - For the values and effects of individual session variables, see [Session Variable List](./session-variables.md).

- Persistence:

    - Modifications to global variables are reset to default values after a system restart; session restarts do not reset to default values.

    - Changes to session variables are lost after the session ends.

## User-Defined Variables

User-defined variables are a mechanism for temporarily storing data within a session. The main features are as follows:

- Naming Rules:

    - Must be prefixed with `@`.

    - Variable names can include letters, numbers, and underscores.

    - Case-insensitive.

- Scope:

    - Only valid within the current session.

    - Automatically destroyed after the session ends.

    - Variables with the same name in different sessions are independent of each other.

- Assignment Methods:

    - Use `SET @var_name = value` syntax for assignment.

    - Supports assigning values using expression results.

- Data Types:

    - Can store numbers (integers, floating-point numbers).

    - Can store strings.

    - Can store date-time values.

    - Can store NULL values.

    - The type is automatically determined at assignment.

## System Variable Definitions and Query Statements

- SHOW VARIABLES

    You can view variables with `SHOW VARIABLES LIKE 'variable_name'`.

    ```sql
    SHOW VARIABLES LIKE '%time_zone%';
    +------------------+----------------+----------------+---------+
    | Variable_name    | Value          | Default_Value  | Changed |
    +------------------+----------------+----------------+---------+
    | system_time_zone | Asia/Hong_Kong | Asia/Hong_Kong | 0       |
    | time_zone        | Asia/Hong_Kong | Asia/Hong_Kong | 0       |
    +------------------+----------------+----------------+---------+
    ```

    Or view all variables with `SHOW VARIABLES`.

    ```sql
    SHOW VARIABLES
    +------------------------------------------------------------------+---------------------------------------+---------------------------------------+---------+
    | Variable_name                                                    | Value                                 | Default_Value                         | Changed |
    +------------------------------------------------------------------+---------------------------------------+---------------------------------------+---------+
    | DML_PLAN_RETRY_TIMES                                             | 3                                     | 3                                     | 0       |
    | adaptive_pipeline_task_serial_read_on_limit                      | 10000                                 | 10000                                 | 0       |
    | allow_modify_materialized_view                              | false                                 | false                                 | 0       |
    | allow_partition_column_nullable                            | true                                  | true                                  | 0       |
    | analyze_timeout                                           | 43200                                 | 43200                                 | 0       |
    | version                                                      | 5.7.99                                | 5.7.99                                | 0       |
    | version_comment                                                | Doris version doris-0.0.0--de61c58223 | Doris version doris-0.0.0--de61c58223 | 0       |
    | wait_full_block_schedule_times                                 | 2                                     | 2                                     | 0       |
    | wait_timeout                                               | 28800                                 | 28800                                 | 0       |
    | workload_group                                                 |                                       |                                       | 0       |
    +------------------------------------------------------------------+---------------------------------------+---------------------------------------+---------+
    360 rows in set (0.01 sec)
    ```

- SET

    Some variables can be set to take effect globally or only in the current session.

    Set to take effect only in the current session with `SET`. For example:

    ```sql
    SET exec_mem_limit = 137438953472;
    SHOW VARIABLES LIKE '%exec_mem_limit%';
    +----------------+--------------+---------------+---------+
    | Variable_name  | Value        | Default_Value | Changed |
    +----------------+--------------+---------------+---------+
    | exec_mem_limit | 137438953472 | 2147483648    | 1       |
    +----------------+--------------+---------------+---------+
    1 row in set (0.01 sec)
    SET forward_to_master = true;
    SHOW VARIABLES LIKE '%forward_to_master%';
    +-------------------+-------+---------------+---------+
    | Variable_name     | Value | Default_Value | Changed |
    +-------------------+-------+---------------+---------+
    | forward_to_master | true  | true          | 0       |
    +-------------------+-------+---------------+---------+
    1 row in set (0.00 sec)
    SET time_zone = "Asia/Shanghai";
    SHOW VARIABLES LIKE '%time_zone%';
    +------------------+----------------+----------------+---------+
    | Variable_name    | Value          | Default_Value  | Changed |
    +------------------+----------------+----------------+---------+
    | time_zone        | Asia/Shanghai  | Asia/Hong_Kong | 1       |
    | system_time_zone | Asia/Hong_Kong | Asia/Hong_Kong | 0       |
    +------------------+----------------+----------------+---------+
    2 rows in set (0.00 sec)
    ```

    Set globally

    ```sql
    SET GLOBAL exec_mem_limit = 137438953472;
    SHOW VARIABLES LIKE '%exec_mem_limit%';
    +----------------+--------------+---------------+---------+
    | Variable_name  | Value        | Default_Value | Changed |
    +----------------+--------------+---------------+---------+
    | exec_mem_limit | 137438953472 | 2147483648    | 1       |
    +----------------+--------------+---------------+---------+
    1 row in set (0.01 sec)
    ```

- UNSET

    Syntax:

    ```sql
    UNSET (GLOBAL | SESSION | LOCAL)? VARIABLE (ALL | identifier)
    unset global variable exec_mem_limit;
    SHOW VARIABLES LIKE '%exec_mem_limit%';
    +----------------+------------+---------------+---------+
    | Variable_name  | Value      | Default_Value | Changed |
    +----------------+------------+---------------+---------+
    | exec_mem_limit | 2147483648 | 2147483648    | 0       |
    +----------------+------------+---------------+---------+
    1 row in set (0.00 sec)
    ```

## User Variable Definition and Query Statements

User-defined variables can be defined with the following statement:

```sql
SET @var_name = constant_value|constant_expr;
```

Setting examples:

```sql
SET @v1 = "A";
SET @v2 = 32+33;
SET @v3 = str_to_date("2024-12-29 10:11:12", '%Y-%m-%d %H:%i:%s');
```

Can be used in queries

```sql
SELECT @v1, @v2, @v3;
+------+------+--------------------+
| @v1  | @v2  | @v3                 |
+------+------+--------------------+
| A    |   65 | 2024-12-29 10:11:12 |
+------+------+--------------------+
1 row in set (0.01 sec)
```