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: index_merge_sort_union=on, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on, mrr_cost_based=on, block_nested_loop=on, batched_key_access=off, materialization=on, semijoin=on, loosescan=on, firstmatch=on, duplicateweedout=on, subquery_materialization_cost_based=on, use_index_extensions=on, condition_fanout_filter=on, derived_merge=on, use_invisible_indexes=off, skip_scan=on, hash_join=on, subquery_to_derived=off, prefer_ordering_index=on, hypergraph_optimizer=on, derived_condition_pushdown=on For MySQL 9.3.0 and earlier: index_merge_sort_union=on, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on, mrr_cost_based=on, block_nested_loop=on, batched_key_access=off, materialization=on, semijoin=on, loosescan=on, firstmatch=on, duplicateweedout=on, subquery_materialization_cost_based=on, use_index_extensions=on, condition_fanout_filter=on, derived_merge=on, use_invisible_indexes=off, skip_scan=on, hash_join=on, subquery_to_derived=off, prefer_ordering_index=on, hypergraph_optimizer=off, derived_condition_pushdown=on
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