19.9 Session Variables
Session variables remain in effect during the session until the variable changes or the session ends. The change has no effect on other sessions. For new connections, a session variable value is initialized with the corresponding global system variable value. Many of the following session variables are also available as user configurable variables. See: User-Configurable System Variables.
To assign a value to a session variable, precede the variable
name with the SESSION
or
LOCAL
keyword, or with the
@@SESSION.
, @@LOCAL.
, or
@@
qualifier, or with no keyword or modifier.
For example:
mysql> SET SESSION sql_mode = 'TRADITIONAL';
mysql> SET LOCAL sql_mode = 'TRADITIONAL';
mysql> SET @@SESSION.sql_mode = 'TRADITIONAL';
mysql> SET @@LOCAL.sql_mode = 'TRADITIONAL';
mysql> SET @@sql_mode = 'TRADITIONAL';
mysql> SET sql_mode = 'TRADITIONAL';
Table 19-21 User Settable Session Variables
Name | Default Value |
---|---|
autocommit
|
ON
|
big_tables
|
OFF
|
block_encryption_mode
|
aes-128-ecb
|
character_set_client
|
utf8mb4
|
character_set_connection
|
utf8mb4
|
character_set_results
|
utf8mb4
|
character_set_server
|
utf8mb4
|
collation_connection
|
utf8mb4_0900_ai_ci
|
collation_database
|
utf8mb4_0900_ai_ci
|
collation_server
|
utf8mb4_0900_ai_ci
|
completion_type
|
NO_CHAIN
|
cte_max_recursion_depth
|
1000
|
default-storage-engine
|
InnoDB
|
--default_tmp_storage_engine
|
InnoDB
|
default_week_format
|
0
|
div_precision_increment
|
4
|
end_markers_in_json
|
OFF
|
eq_range_index_dive_limit
|
200
|
foreign_key_checks
|
ON
|
generated_random_password_length
|
20
|
group_concat_max_len
|
1024
|
group_replication_consistency
|
BEFORE_ON_PRIMARY_FAILOVER
|
information_schema_stats_expiry
|
86400
|
innodb_ddl_buffer_size
|
1048576
|
innodb_ddl_threads
|
4
|
innodb_ft_enable_stopword
|
ON
|
innodb_ft_user_stopword_table
|
NULL
|
innodb_lock_wait_timeout
|
50
|
innodb_parallel_read_threads
|
Shape dependent. See Shape-Dependent System Variables. |
internal_tmp_mem_storage_engine
|
TempTable
|
join_buffer_size
|
262144
|
lc_messages
|
en_US
|
lc_time_names
|
en_US
|
lock_wait_timeout
|
86400
|
long_query_time
|
10
|
max_allowed_packet
|
67108864
|
max_execution_time
|
0
|
max_heap_table_size
|
16777216
|
max_join_size
|
18446744073709551615
|
max_length_for_sort_data
|
4096
|
max_points_in_geometry
|
65536
|
max_seeks_for_key
|
18446744073709551615
|
max_sort_length
|
1024
|
mysqlx_max_allowed_packet
|
67108864
|
mysqlx_read_timeout
|
30
|
mysqlx_wait_timeout
|
28800
|
mysqlx_write_timeout
|
60
|
net_buffer_length
|
16384
|
net_read_timeout
|
30
|
net_retry_count
|
10
|
net_write_timeout
|
60
|
new
|
OFF
|
old_alter_table
|
OFF
|
optimizer_prune_level
|
1
|
optimizer_search_depth
|
62
|
optimizer_switch
|
For MySQL 9.3.1 and later:
For MySQL 9.3.0 and earlier:
|
optimizer_trace
|
enabled=off,one_line=off
|
optimizer_trace_features
|
greedy_search=on, range_optimizer=on, dynamic_range=on,
repeated_subselect=on
|
optimizer_trace_limit
|
1
|
optimizer_trace_max_mem_size
|
1048576
|
optimizer_trace_offset
|
-1
|
parser_max_mem_size
|
18446744073709551615
|
print_identified_with_as_hex
|
OFF
|
pseudo_replica_mode
|
OFF
|
pseudo_slave_mode
|
OFF
|
query_alloc_block_size
|
8192
|
query_prealloc_size
|
8192
|
range_alloc_block_size
|
4096
|
range_optimizer_max_mem_size
|
8388608
|
rbr_exec_mode
|
STRICT
|
read_buffer_size
|
131072
|
read_rnd_buffer_size
|
262144
|
resultset_metadata
|
FULL
|
secondary_engine_cost_threshold
|
100000.000000
|
session_track_gtids
|
OFF
|
session_track_schema
|
ON
|
session_track_state_change
|
OFF
|
session_track_system_variables
|
262144
|
session_track_transaction_info
|
OFF
|
show_create_table_skip_secondary_engine
|
OFF
|
show_create_table_verbosity
|
OFF
|
sort_buffer_size
|
262144
|
sql_auto_is_null
|
OFF
|
sql_big_selects
|
ON
|
sql_buffer_result
|
OFF
|
sql_mode
|
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,
NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION
|
sql_notes
|
ON
|
sql_quote_show_create
|
ON
|
sql_safe_updates
|
OFF
|
sql_select_limit
|
18446744073709551615
|
sql_warnings
|
OFF
|
thread_pool_high_priority_connection
|
0
|
time_zone
|
UTC
|
tmp_table_size
|
16777216
|
transaction_alloc_block_size
|
8192
|
transaction_isolation
|
REPEATABLE-READ
|
transaction_prealloc_size
|
4096
|
transaction_read_only
|
OFF
|
unique_checks
|
ON
|
updatable_views_with_limit
|
YES
|
use_secondary_engine
|
ON
|
wait_timeout
|
28800
|
windowing_use_high_precision
|
ON
|
Parent topic: Configuration