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 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.
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 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.
SHOW VARIABLES
You can view variables with SHOW VARIABLES LIKE 'variable_name'.
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.
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 doris0.0.0--de61c5823 | Doris version doris-0.0--de61c5823 | 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:
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
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:
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-defined variables can be defined with the following statement:
SET @var_name = constant_value|constant_expr;
Setting examples:
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
SELECT @v1, @v2, @v3; +------+------+--------------------+ | @v1 | @v2 | @v3 | +------+------+--------------------+ | A | 65 | 2024-12-29 10:11:12 | +------+------+--------------------+ 1 row in set (0.01 sec)