24、MySQL 8.0 5.1.5服务器系统变量

MySQL服务器维护许多系统变量，指出它如何配置。每个系统变量都有一个默认值。可以在服务器启动时使用命令行上的选项或选项文件来设置系统变量。它们中的大多数可以在运行时使用该SET 语句动态更改 ，这使您可以修改服务器的操作，而无需停止并重新启动它。您也可以在表达式中使用系统变量值。

在运行时，设置系统变量的全局值通常需要SYSTEM_VARIABLES_ADMIN or SUPER权限，而个别变量描述中则指定了异常。对于某些系统变量，设置会话值还需要sme权限; 再次，个别变量描述中有例外情况。

有几种方法可以查看系统变量的名称和值：

要查看服务器将基于其编译的默认值以及它读取的任何选项文件使用的值，请使用以下命令：
mysqld --verbose --help
要根据其编译的默认值查看服务器将使用的值，忽略任何选项文件中的设置，请使用以下命令：
mysqld --no-defaults --verbose --help
要查看正在运行的服务器使用的当前值，请使用该 SHOW VARIABLES语句或Performance Schema系统变量表。请参见 第26.11.13节“性能模式系统变量表”。
本部分包含一个列出所有系统变量的表格，下表提供了每个变量的描述。所有MySQL 8.0版本都提供没有指定版本的变量。有关操作系统变量的更多信息，请参见第5.1.6节“使用系统变量”。

表5.2系统变量摘要

Name Cmd-Line Option File System Var Var Scope Dynamic
activate_all_roles_on_login Yes Yes Yes Global Yes
auto_generate_certs Yes Yes Yes Global No
auto_increment_increment Yes Both Yes
auto_increment_offset Yes Both Yes
autocommit Yes Yes Yes Both Yes
automatic_sp_privileges Yes Global Yes
avoid_temporal_upgrade Yes Yes Yes Global Yes
back_log Yes Global No
basedir Yes Yes Yes Global No
big-tables Yes Yes Yes
- Variable: big_tables Yes Both Yes
bind-address Yes Yes No
- Variable: bind_address Yes Global No
binlog_cache_size Yes Yes Yes Global Yes
binlog_checksum Yes Global Yes
binlog_direct_non_transactional_updates Yes Yes Yes Both Yes
binlog_error_action Yes Yes Yes Global Yes
binlog_expire_logs_seconds Yes Yes Yes Global Yes
binlog-format Yes Yes Yes
- Variable: binlog_format Yes Both Yes
binlog_group_commit_sync_delay Yes Yes Yes Global Yes
binlog_group_commit_sync_no_delay_count Yes Yes Yes Global Yes
binlog_gtid_simple_recovery Yes Yes Yes Global No
binlog_max_flush_queue_time Yes Global Yes
binlog_order_commits Yes Global Yes
binlog_row_image Yes Yes Yes Both Yes
binlog_row_metadata Yes Yes Yes Global Yes
binlog_row_value_options Yes Yes Yes Both Yes
binlog_rows_query_log_events Yes Yes Yes Both Yes
binlog_stmt_cache_size Yes Yes Yes Global Yes
binlog_transaction_dependency_history_size Yes Yes Yes Global Yes
binlog_transaction_dependency_tracking Yes Yes Yes Global Yes
block_encryption_mode Yes Yes Yes Both Yes
bulk_insert_buffer_size Yes Yes Yes Both Yes
caching_sha2_password_auto_generate_rsa_keys Yes Yes Yes Global No
caching_sha2_password_private_key_path Yes Yes Yes Global No
caching_sha2_password_public_key_path Yes Yes Yes Global No
character_set_client Yes Both Yes
character_set_connection Yes Both Yes
character_set_database (note 1) Yes Both Yes
character-set-filesystem Yes Yes Yes
- Variable: character_set_filesystem Yes Both Yes
character_set_results Yes Both Yes
character-set-server Yes Yes Yes
- Variable: character_set_server Yes Both Yes
character_set_system Yes Global No
character-sets-dir Yes Yes No
- Variable: character_sets_dir Yes Global No
check_proxy_users Yes Yes Yes Global Yes
collation_connection Yes Both Yes
collation_database (note 1) Yes Both Yes
collation-server Yes Yes Yes
- Variable: collation_server Yes Both Yes
completion_type Yes Yes Yes Both Yes
concurrent_insert Yes Yes Yes Global Yes
connect_timeout Yes Yes Yes Global Yes
connection_control_failed_connections_threshold Yes Yes Yes Global Yes
connection_control_max_connection_delay Yes Yes Yes Global Yes
connection_control_min_connection_delay Yes Yes Yes Global Yes
core_file Yes Global No
cte_max_recursion_depth Yes Yes Yes Both Yes
daemon_memcached_enable_binlog Yes Yes Yes Global No
daemon_memcached_engine_lib_name Yes Yes Yes Global No
daemon_memcached_engine_lib_path Yes Yes Yes Global No
daemon_memcached_option Yes Yes Yes Global No
daemon_memcached_r_batch_size Yes Yes Yes Global No
daemon_memcached_w_batch_size Yes Yes Yes Global No
datadir Yes Yes Yes Global No
date_format Yes Global No
datetime_format Yes Global No
debug Yes Yes Yes Both Yes
debug_sync Yes Session Yes
default_authentication_plugin Yes Yes Yes Global No
default_collation_for_utf8mb4 Yes Yes Yes Both Yes
default_password_lifetime Yes Yes Yes Global Yes
default-storage-engine Yes Yes Yes
- Variable: default_storage_engine Yes Both Yes
default_tmp_storage_engine Yes Yes Yes Both Yes
default_week_format Yes Yes Yes Both Yes
delay-key-write Yes Yes Yes
- Variable: delay_key_write Yes Global Yes
delayed_insert_limit Yes Yes Yes Global Yes
delayed_insert_timeout Yes Yes Yes Global Yes
delayed_queue_size Yes Yes Yes Global Yes
disabled_storage_engines Yes Yes Yes Global No
disconnect_on_expired_password Yes Yes Yes Session No
div_precision_increment Yes Yes Yes Both Yes
dragnet.log_error_filter_rules Yes Yes Yes Global Yes
end_markers_in_json Yes Both Yes
enforce-gtid-consistency Yes Yes Yes Global Yes
enforce_gtid_consistency Yes Yes Yes Global Yes
eq_range_index_dive_limit Yes Both Yes
error_count Yes Session No
event-scheduler Yes Yes Yes
- Variable: event_scheduler Yes Global Yes
executed_gtids_compression_period Yes Global Yes
expire_logs_days Yes Yes Yes Global Yes
explicit_defaults_for_timestamp Yes Yes Yes Both Yes
external_user Yes Session No
flush Yes Yes Yes Global Yes
flush_time Yes Yes Yes Global Yes
foreign_key_checks Yes Both Yes
ft_boolean_syntax Yes Yes Yes Global Yes
ft_max_word_len Yes Yes Yes Global No
ft_min_word_len Yes Yes Yes Global No
ft_query_expansion_limit Yes Yes Yes Global No
ft_stopword_file Yes Yes Yes Global No
general-log Yes Yes Yes
- Variable: general_log Yes Global Yes
general_log_file Yes Yes Yes Global Yes
group_concat_max_len Yes Yes Yes Both Yes
group_replication_allow_local_disjoint_gtids_join Yes Yes Yes Global Yes
group_replication_allow_local_lower_version_join Yes Yes Yes Global Yes
group_replication_auto_increment_increment Yes Yes Yes Global Yes
group_replication_bootstrap_group Yes Yes Yes Global Yes
group_replication_communication_debug_options Yes Yes Yes Global Yes
group_replication_components_stop_timeout Yes Yes Yes Global Yes
group_replication_compression_threshold Yes Yes Yes Global Yes
group_replication_enforce_update_everywhere_checks Yes Yes Yes Global Yes
group_replication_flow_control_applier_threshold Yes Yes Yes Global Yes
group_replication_flow_control_certifier_threshold Yes Yes Yes Global Yes
group_replication_flow_control_hold_percent Yes Yes Yes Global Yes
group_replication_flow_control_max_commit_quota Yes Yes Yes
- Variable: group_replication_flow_control_min_quota Yes Global Yes
group_replication_flow_control_member_quota_percent Yes Yes Yes Global Yes
group_replication_flow_control_min_quota Yes Yes Yes Global Yes
group_replication_flow_control_min_recovery_quota Yes Yes Yes Global Yes
group_replication_flow_control_mode Yes Yes Yes Global Yes
group_replication_flow_control_period Yes Yes Yes Global Yes
group_replication_flow_control_release_percent Yes Yes Yes Global Yes
group_replication_force_members Yes Yes Yes Global Yes
group_replication_group_name Yes Yes Yes Global Yes
group_replication_group_seeds Yes Yes Yes Global Yes
group_replication_gtid_assignment_block_size Yes Yes Yes Global Yes
group_replication_ip_whitelist Yes Yes Yes Global Yes
group_replication_local_address Yes Yes Yes Global Yes
group_replication_member_weight Yes Yes Yes Global Yes
group_replication_poll_spin_loops Yes Yes Yes Global Yes
group_replication_recovery_complete_at Yes Yes Yes Global Yes
group_replication_recovery_get_public_key Yes Yes Yes Global Yes
group_replication_recovery_public_key_path Yes Yes Yes Global Yes
group_replication_recovery_reconnect_interval Yes Yes Yes Global Yes
group_replication_recovery_retry_count Yes Yes Yes Global Yes
group_replication_recovery_ssl_ca Yes Yes Yes Global Yes
group_replication_recovery_ssl_capath Yes Yes Yes Global Yes
group_replication_recovery_ssl_cert Yes Yes Yes Global Yes
group_replication_recovery_ssl_cipher Yes Yes Yes Global Yes
group_replication_recovery_ssl_crl Yes Yes Yes Global Yes
group_replication_recovery_ssl_crlpath Yes Yes Yes Global Yes
group_replication_recovery_ssl_key Yes Yes Yes Global Yes
group_replication_recovery_ssl_verify_server_cert Yes Yes Yes Global Yes
group_replication_recovery_use_ssl Yes Yes Yes Global Yes
group_replication_single_primary_mode Yes Yes Yes Global Yes
group_replication_ssl_mode Yes Yes Yes Global Yes
group_replication_start_on_boot Yes Yes Yes Global Yes
group_replication_transaction_size_limit Yes Yes Yes Global Yes
group_replication_unreachable_majority_timeout Yes Yes Yes Global Yes
gtid_executed Yes Varies No
gtid_executed_compression_period Yes Global Yes
gtid-mode Yes Yes Yes
- Variable: gtid_mode Yes Global Yes
gtid_mode Yes Global Yes
gtid_next Yes Session Yes
gtid_owned Yes Both No
gtid_purged Yes Global Yes
have_compress Yes Global No
have_crypt Yes Global No
have_dynamic_loading Yes Global No
have_geometry Yes Global No
have_openssl Yes Global No
have_profiling Yes Global No
have_query_cache Yes Global No
have_rtree_keys Yes Global No
have_ssl Yes Global No
have_statement_timeout Yes Global No
have_symlink Yes Global No
histogram_generation_max_mem_size Yes Yes Yes Both Yes
host_cache_size Yes Global Yes
hostname Yes Global No
identity Yes Session Yes
ignore-builtin-innodb Yes Yes No
- Variable: ignore_builtin_innodb Yes Global No
information_schema_stats_expiry Yes Yes Yes Session Yes
init_connect Yes Yes Yes Global Yes
init-file Yes Yes No
- Variable: init_file Yes Global No
init_slave Yes Yes Yes Global Yes
innodb_adaptive_flushing Yes Yes Yes Global Yes
innodb_adaptive_flushing_lwm Yes Yes Yes Global Yes
innodb_adaptive_hash_index Yes Yes Yes Global Yes
innodb_adaptive_hash_index_parts Yes Yes Yes Global No
innodb_adaptive_max_sleep_delay Yes Yes Yes Global Yes
innodb_api_bk_commit_interval Yes Yes Yes Global Yes
innodb_api_disable_rowlock Yes Yes Yes Global No
innodb_api_enable_binlog Yes Yes Yes Global No
innodb_api_enable_mdl Yes Yes Yes Global No
innodb_api_trx_level Yes Yes Yes Global Yes
innodb_autoextend_increment Yes Yes Yes Global Yes
innodb_autoinc_lock_mode Yes Yes Yes Global No
innodb_background_drop_list_empty Yes Yes Yes Global Yes
innodb_buffer_pool_chunk_size Yes Yes Yes