Session Variables

15.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 15-19 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`	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