5.1.8.2 动态系统变量

许多服务器系统变量是动态的,可以在运行时设置。参见第 13.7.4.1 节“变量分配的 SET 语法”。有关设置系统变量的特权要求的说明,请参见第 5.1.8.1 节“系统变量特权”

下 table 列出了适用于mysqld的所有动态系统变量。

该 table 列出了每个变量的数据类型和范围。最后一列指示每个变量的作用域是全局变量,会话变量还是两者。有关设置和使用变量的详细信息,请参见相应的项目描述。在适当的地方,提供了指向有关该项目的更多信息的直接链接。

类型为“字符串”的变量采用字符串值。类型为“数字”的变量采用数字值。可以将类型为“布尔”的变量设置为 0、1,ONOFF。标记为“枚举”的变量通常应设置为该变量的可用值之一,但也可以设置为与所需枚举值相对应的数字。对于枚举的系统变量,第一个枚举值对应于 0.这与用于 table 列的ENUM数据类型不同,后者的第一个枚举值对应于 1.

table5.4 动态系统变量摘要

Variable NameVariable TypeVariable Scope
audit_log_connection_policyEnumerationGlobal
audit_log_exclude_accountsStringGlobal
audit_log_flushBooleanGlobal
audit_log_include_accountsStringGlobal
audit_log_read_buffer_sizeIntegerVaries
audit_log_rotate_on_sizeIntegerGlobal
audit_log_statement_policyEnumerationGlobal
authentication_ldap_sasl_auth_method_nameStringGlobal
authentication_ldap_sasl_bind_base_dnStringGlobal
authentication_ldap_sasl_bind_root_dnStringGlobal
authentication_ldap_sasl_bind_root_pwdStringGlobal
authentication_ldap_sasl_ca_pathStringGlobal
authentication_ldap_sasl_group_search_attrStringGlobal
authentication_ldap_sasl_group_search_filterStringGlobal
authentication_ldap_sasl_init_pool_sizeIntegerGlobal
authentication_ldap_sasl_log_statusIntegerGlobal
authentication_ldap_sasl_max_pool_sizeIntegerGlobal
authentication_ldap_sasl_server_hostStringGlobal
authentication_ldap_sasl_server_portIntegerGlobal
authentication_ldap_sasl_tlsBooleanGlobal
authentication_ldap_sasl_user_search_attrStringGlobal
authentication_ldap_simple_auth_method_nameStringGlobal
authentication_ldap_simple_bind_base_dnStringGlobal
authentication_ldap_simple_bind_root_dnStringGlobal
authentication_ldap_simple_bind_root_pwdStringGlobal
authentication_ldap_simple_ca_pathStringGlobal
authentication_ldap_simple_group_search_attrStringGlobal
authentication_ldap_simple_group_search_filterStringGlobal
authentication_ldap_simple_init_pool_sizeIntegerGlobal
authentication_ldap_simple_log_statusIntegerGlobal
authentication_ldap_simple_max_pool_sizeIntegerGlobal
authentication_ldap_simple_server_hostStringGlobal
authentication_ldap_simple_server_portIntegerGlobal
authentication_ldap_simple_tlsBooleanGlobal
authentication_ldap_simple_user_search_attrStringGlobal
auto_increment_incrementIntegerBoth
auto_increment_offsetIntegerBoth
autocommitBooleanBoth
automatic_sp_privilegesBooleanGlobal
avoid_temporal_upgradeBooleanGlobal
big_tablesBooleanBoth
binlog_cache_sizeIntegerGlobal
binlog_checksumStringGlobal
binlog_direct_non_transactional_updatesBooleanBoth
binlog_error_actionEnumerationGlobal
binlog_formatEnumerationBoth
binlog_group_commit_sync_delayIntegerGlobal
binlog_group_commit_sync_no_delay_countIntegerGlobal
binlog_max_flush_queue_timeIntegerGlobal
binlog_order_commitsBooleanGlobal
binlog_row_imageEnumerationBoth
binlog_rows_query_log_eventsBooleanBoth
binlog_stmt_cache_sizeIntegerGlobal
binlog_transaction_dependency_history_sizeIntegerGlobal
binlog_transaction_dependency_trackingEnumerationGlobal
block_encryption_modeStringBoth
bulk_insert_buffer_sizeIntegerBoth
character_set_clientStringBoth
character_set_connectionStringBoth
character_set_databaseStringBoth
character_set_filesystemStringBoth
character_set_resultsStringBoth
character_set_serverStringBoth
check_proxy_usersBooleanGlobal
collation_connectionStringBoth
collation_databaseStringBoth
collation_serverStringBoth
completion_typeEnumerationBoth
concurrent_insertEnumerationGlobal
connect_timeoutIntegerGlobal
connection_control_failed_connections_thresholdIntegerGlobal
connection_control_max_connection_delayIntegerGlobal
connection_control_min_connection_delayIntegerGlobal
debugStringBoth
debug_syncStringSession
default_password_lifetimeIntegerGlobal
default_storage_engineEnumerationBoth
default_tmp_storage_engineEnumerationBoth
default_week_formatIntegerBoth
delay_key_writeEnumerationGlobal
delayed_insert_limitIntegerGlobal
delayed_insert_timeoutIntegerGlobal
delayed_queue_sizeIntegerGlobal
div_precision_incrementIntegerBoth
end_markers_in_jsonBooleanBoth
enforce_gtid_consistencyEnumerationGlobal
eq_range_index_dive_limitIntegerBoth
event_schedulerEnumerationGlobal
expire_logs_daysIntegerGlobal
explicit_defaults_for_timestampBooleanBoth
flushBooleanGlobal
flush_timeIntegerGlobal
foreign_key_checksBooleanBoth
ft_boolean_syntaxStringGlobal
general_logBooleanGlobal
general_log_fileFile nameGlobal
group_concat_max_lenIntegerBoth
group_replication_allow_local_disjoint_gtids_joinBooleanGlobal
group_replication_allow_local_lower_version_joinBooleanGlobal
group_replication_auto_increment_incrementIntegerGlobal
group_replication_bootstrap_groupBooleanGlobal
group_replication_components_stop_timeoutIntegerGlobal
group_replication_compression_thresholdIntegerGlobal
group_replication_enforce_update_everywhere_checksBooleanGlobal
group_replication_exit_state_actionEnumerationGlobal
group_replication_flow_control_applier_thresholdIntegerGlobal
group_replication_flow_control_certifier_thresholdIntegerGlobal
group_replication_flow_control_modeEnumerationGlobal
group_replication_force_membersStringGlobal
group_replication_group_nameStringGlobal
group_replication_group_seedsStringGlobal
group_replication_gtid_assignment_block_sizeIntegerGlobal
group_replication_ip_whitelistStringGlobal
group_replication_local_addressStringGlobal
group_replication_member_weightIntegerGlobal
group_replication_poll_spin_loopsIntegerGlobal
group_replication_recovery_complete_atEnumerationGlobal
group_replication_recovery_reconnect_intervalIntegerGlobal
group_replication_recovery_retry_countIntegerGlobal
group_replication_recovery_ssl_caStringGlobal
group_replication_recovery_ssl_capathStringGlobal
group_replication_recovery_ssl_certStringGlobal
group_replication_recovery_ssl_cipherStringGlobal
group_replication_recovery_ssl_crlFile nameGlobal
group_replication_recovery_ssl_crlpathDirectory nameGlobal
group_replication_recovery_ssl_keyStringGlobal
group_replication_recovery_ssl_verify_server_certBooleanGlobal
group_replication_recovery_use_sslBooleanGlobal
group_replication_single_primary_modeBooleanGlobal
group_replication_ssl_modeEnumerationGlobal
group_replication_start_on_bootBooleanGlobal
group_replication_transaction_size_limitIntegerGlobal
group_replication_unreachable_majority_timeoutIntegerGlobal
gtid_executed_compression_periodIntegerGlobal
gtid_modeEnumerationGlobal
gtid_nextEnumerationSession
gtid_purgedStringGlobal
host_cache_sizeIntegerGlobal
identityIntegerSession
init_connectStringGlobal
init_slaveStringGlobal
innodb_adaptive_flushingBooleanGlobal
innodb_adaptive_flushing_lwmIntegerGlobal
innodb_adaptive_hash_indexBooleanGlobal
innodb_adaptive_max_sleep_delayIntegerGlobal
innodb_api_bk_commit_intervalIntegerGlobal
innodb_api_trx_levelIntegerGlobal
innodb_autoextend_incrementIntegerGlobal
innodb_background_drop_list_emptyBooleanGlobal
innodb_buffer_pool_dump_at_shutdownBooleanGlobal
innodb_buffer_pool_dump_nowBooleanGlobal
innodb_buffer_pool_dump_pctIntegerGlobal
innodb_buffer_pool_filenameFile nameGlobal
innodb_buffer_pool_load_abortBooleanGlobal
innodb_buffer_pool_load_nowBooleanGlobal
innodb_buffer_pool_sizeIntegerGlobal
innodb_change_buffer_max_sizeIntegerGlobal
innodb_change_bufferingEnumerationGlobal
innodb_change_buffering_debugIntegerGlobal
innodb_checksum_algorithmEnumerationGlobal
innodb_cmp_per_index_enabledBooleanGlobal
innodb_commit_concurrencyIntegerGlobal
innodb_compress_debugEnumerationGlobal
innodb_compression_failure_threshold_pctIntegerGlobal
innodb_compression_levelIntegerGlobal
innodb_compression_pad_pct_maxIntegerGlobal
innodb_concurrency_ticketsIntegerGlobal
innodb_deadlock_detectBooleanGlobal
innodb_default_row_formatEnumerationGlobal
innodb_disable_resize_buffer_pool_debugBooleanGlobal
innodb_disable_sort_file_cacheBooleanGlobal
innodb_fast_shutdownIntegerGlobal
innodb_fil_make_page_dirty_debugIntegerGlobal
innodb_file_formatStringGlobal
innodb_file_format_maxStringGlobal
innodb_file_per_tableBooleanGlobal
innodb_fill_factorIntegerGlobal
innodb_flush_log_at_timeoutIntegerGlobal
innodb_flush_log_at_trx_commitEnumerationGlobal
innodb_flush_neighborsEnumerationGlobal
innodb_flush_syncBooleanGlobal
innodb_flushing_avg_loopsIntegerGlobal
innodb_ft_aux_tableStringGlobal
innodb_ft_enable_diag_printBooleanGlobal
innodb_ft_enable_stopwordBooleanBoth
innodb_ft_num_word_optimizeIntegerGlobal
innodb_ft_result_cache_limitIntegerGlobal
innodb_ft_server_stopword_tableStringGlobal
innodb_ft_user_stopword_tableStringBoth
innodb_io_capacityIntegerGlobal
innodb_io_capacity_maxIntegerGlobal
innodb_large_prefixBooleanGlobal
innodb_limit_optimistic_insert_debugIntegerGlobal
innodb_lock_wait_timeoutIntegerBoth
innodb_log_checkpoint_nowBooleanGlobal
innodb_log_checksumsBooleanGlobal
innodb_log_compressed_pagesBooleanGlobal
innodb_log_write_ahead_sizeIntegerGlobal
innodb_lru_scan_depthIntegerGlobal
innodb_max_dirty_pages_pctNumericGlobal
innodb_max_dirty_pages_pct_lwmNumericGlobal
innodb_max_purge_lagIntegerGlobal
innodb_max_purge_lag_delayIntegerGlobal
innodb_max_undo_log_sizeIntegerGlobal
innodb_merge_threshold_set_all_debugIntegerGlobal
innodb_monitor_disableStringGlobal
innodb_monitor_enableStringGlobal
innodb_monitor_resetEnumerationGlobal
innodb_monitor_reset_allEnumerationGlobal
innodb_old_blocks_pctIntegerGlobal
innodb_old_blocks_timeIntegerGlobal
innodb_online_alter_log_max_sizeIntegerGlobal
innodb_optimize_fulltext_onlyBooleanGlobal
innodb_print_all_deadlocksBooleanGlobal
innodb_purge_batch_sizeIntegerGlobal
innodb_purge_rseg_truncate_frequencyIntegerGlobal
innodb_random_read_aheadBooleanGlobal
innodb_read_ahead_thresholdIntegerGlobal
innodb_replication_delayIntegerGlobal
innodb_rollback_segmentsIntegerGlobal
innodb_saved_page_number_debugIntegerGlobal
innodb_spin_wait_delayIntegerGlobal
innodb_stats_auto_recalcBooleanGlobal
innodb_stats_include_delete_markedBooleanGlobal
innodb_stats_methodEnumerationGlobal
innodb_stats_on_metadataBooleanGlobal
innodb_stats_persistentBooleanGlobal
innodb_stats_persistent_sample_pagesIntegerGlobal
innodb_stats_sample_pagesIntegerGlobal
innodb_stats_transient_sample_pagesIntegerGlobal
innodb_status_outputBooleanGlobal
innodb_status_output_locksBooleanGlobal
innodb_strict_modeBooleanBoth
innodb_support_xaBooleanBoth
innodb_sync_spin_loopsIntegerGlobal
innodb_table_locksBooleanBoth
innodb_thread_concurrencyIntegerGlobal
innodb_thread_sleep_delayIntegerGlobal
innodb_tmpdirDirectory nameBoth
innodb_trx_purge_view_update_only_debugBooleanGlobal
innodb_trx_rseg_n_slots_debugIntegerGlobal
innodb_undo_log_truncateBooleanGlobal
innodb_undo_logsIntegerGlobal
insert_idIntegerSession
interactive_timeoutIntegerBoth
internal_tmp_disk_storage_engineEnumerationGlobal
join_buffer_sizeIntegerBoth
keep_files_on_createBooleanBoth
key_buffer_sizeIntegerGlobal
key_cache_age_thresholdIntegerGlobal
key_cache_block_sizeIntegerGlobal
key_cache_division_limitIntegerGlobal
keyring_aws_cmk_idStringGlobal
keyring_aws_regionEnumerationGlobal
keyring_encrypted_file_dataFile nameGlobal
keyring_encrypted_file_passwordStringGlobal
keyring_file_dataFile nameGlobal
keyring_okv_conf_dirDirectory nameGlobal
keyring_operationsBooleanGlobal
last_insert_idIntegerSession
lc_messagesStringBoth
lc_time_namesStringBoth
local_infileBooleanGlobal
lock_wait_timeoutIntegerBoth
log_bin_trust_function_creatorsBooleanGlobal
log_builtin_as_identified_by_passwordBooleanGlobal
log_error_verbosityIntegerGlobal
log_outputSetGlobal
log_queries_not_using_indexesBooleanGlobal
log_slow_admin_statementsBooleanGlobal
log_slow_slave_statementsBooleanGlobal
log_statements_unsafe_for_binlogBooleanGlobal
log_syslogBooleanGlobal
log_syslog_facilityStringGlobal
log_syslog_include_pidBooleanGlobal
log_syslog_tagStringGlobal
log_throttle_queries_not_using_indexesIntegerGlobal
log_timestampsEnumerationGlobal
log_warningsIntegerGlobal
long_query_timeNumericBoth
low_priority_updatesBooleanBoth
master_info_repositoryStringGlobal
master_verify_checksumBooleanGlobal
max_allowed_packetIntegerBoth
max_binlog_cache_sizeIntegerGlobal
max_binlog_sizeIntegerGlobal
max_binlog_stmt_cache_sizeIntegerGlobal
max_connect_errorsIntegerGlobal
max_connectionsIntegerGlobal
max_delayed_threadsIntegerBoth
max_error_countIntegerBoth
max_execution_timeIntegerBoth
max_heap_table_sizeIntegerBoth
max_insert_delayed_threadsIntegerBoth
max_join_sizeIntegerBoth
max_length_for_sort_dataIntegerBoth
max_points_in_geometryIntegerBoth
max_prepared_stmt_countIntegerGlobal
max_relay_log_sizeIntegerGlobal
max_seeks_for_keyIntegerBoth
max_sort_lengthIntegerBoth
max_sp_recursion_depthIntegerBoth
max_tmp_tablesIntegerBoth
max_user_connectionsIntegerBoth
max_write_lock_countIntegerGlobal
min_examined_row_limitIntegerBoth
multi_range_countIntegerBoth
myisam_data_pointer_sizeIntegerGlobal
myisam_max_sort_file_sizeIntegerGlobal
myisam_repair_threadsIntegerBoth
myisam_sort_buffer_sizeIntegerBoth
myisam_stats_methodEnumerationBoth
myisam_use_mmapBooleanGlobal
mysql_firewall_modeBooleanGlobal
mysql_firewall_traceBooleanGlobal
mysql_native_password_proxy_usersBooleanGlobal
mysqlx_connect_timeoutIntegerGlobal
mysqlx_idle_worker_thread_timeoutIntegerGlobal
mysqlx_max_allowed_packetIntegerGlobal
mysqlx_max_connectionsIntegerGlobal
mysqlx_min_worker_threadsIntegerGlobal
ndb_allow_copying_alter_tableBooleanBoth
ndb_autoincrement_prefetch_szIntegerBoth
ndb_blob_read_batch_bytesIntegerBoth
ndb_blob_write_batch_bytesIntegerBoth
ndb_cache_check_timeIntegerGlobal
ndb_clear_apply_statusBooleanGlobal
ndb_data_node_neighbourIntegerGlobal
ndb_default_column_formatEnumerationGlobal
ndb_default_column_formatEnumerationGlobal
ndb_deferred_constraintsIntegerBoth
ndb_deferred_constraintsIntegerBoth
ndb_distributionEnumerationGlobal
ndb_distributionEnumerationGlobal
ndb_eventbuffer_free_percentIntegerGlobal
ndb_eventbuffer_max_allocIntegerGlobal
ndb_extra_loggingIntegerGlobal
ndb_force_sendBooleanBoth
ndb_fully_replicatedBooleanBoth
ndb_index_stat_enableBooleanBoth
ndb_index_stat_optionStringBoth
ndb_join_pushdownBooleanBoth
ndb_log_binBooleanBoth
ndb_log_binlog_indexBooleanGlobal
ndb_log_empty_epochsBooleanGlobal
ndb_log_empty_epochsBooleanGlobal
ndb_log_empty_updateBooleanGlobal
ndb_log_empty_updateBooleanGlobal
ndb_log_exclusive_readsBooleanBoth
ndb_log_exclusive_readsBooleanBoth
ndb_log_update_as_writeBooleanGlobal
ndb_log_update_minimalBooleanGlobal
ndb_log_updated_onlyBooleanGlobal
ndb_optimization_delayIntegerGlobal
ndb_read_backupBooleanGlobal
ndb_recv_thread_activation_thresholdIntegerGlobal
ndb_recv_thread_cpu_maskBitmapGlobal
ndb_report_thresh_binlog_epoch_slipIntegerGlobal
ndb_report_thresh_binlog_mem_usageIntegerGlobal
ndb_row_checksumIntegerBoth
ndb_show_foreign_key_mock_tablesBooleanGlobal
ndb_slave_conflict_roleEnumerationGlobal
ndb_table_no_loggingBooleanSession
ndb_table_temporaryBooleanSession
ndb_use_exact_countBooleanBoth
ndb_use_transactionsBooleanBoth
ndbinfo_max_bytesIntegerBoth
ndbinfo_max_rowsIntegerBoth
ndbinfo_offlineBooleanGlobal
ndbinfo_show_hiddenBooleanBoth
ndbinfo_table_prefixStringBoth
net_buffer_lengthIntegerBoth
net_read_timeoutIntegerBoth
net_retry_countIntegerBoth
net_write_timeoutIntegerBoth
newBooleanBoth
offline_modeBooleanGlobal
old_alter_tableBooleanBoth
old_passwordsEnumerationBoth
optimizer_prune_levelIntegerBoth
optimizer_search_depthIntegerBoth
optimizer_switchSetBoth
optimizer_traceStringBoth
optimizer_trace_featuresStringBoth
optimizer_trace_limitIntegerBoth
optimizer_trace_max_mem_sizeIntegerBoth
optimizer_trace_offsetIntegerBoth
parser_max_mem_sizeIntegerBoth
preload_buffer_sizeIntegerBoth
profilingBooleanBoth
profiling_history_sizeIntegerBoth
pseudo_slave_modeIntegerSession
pseudo_thread_idIntegerSession
query_alloc_block_sizeIntegerBoth
query_cache_limitIntegerGlobal
query_cache_min_res_unitIntegerGlobal
query_cache_sizeIntegerGlobal
query_cache_typeEnumerationBoth
query_cache_wlock_invalidateBooleanBoth
query_prealloc_sizeIntegerBoth
rand_seed1IntegerSession
rand_seed2IntegerSession
range_alloc_block_sizeIntegerBoth
range_optimizer_max_mem_sizeIntegerBoth
rbr_exec_modeEnumerationBoth
read_buffer_sizeIntegerBoth
read_onlyBooleanGlobal
read_rnd_buffer_sizeIntegerBoth
relay_log_info_repositoryStringGlobal
relay_log_purgeBooleanGlobal
require_secure_transportBooleanGlobal
rewriter_enabledBooleanGlobal
rewriter_verboseIntegerGlobal
rpl_semi_sync_master_enabledBooleanGlobal
rpl_semi_sync_master_timeoutIntegerGlobal
rpl_semi_sync_master_trace_levelIntegerGlobal
rpl_semi_sync_master_wait_for_slave_countIntegerGlobal
rpl_semi_sync_master_wait_no_slaveBooleanGlobal
rpl_semi_sync_master_wait_pointEnumerationGlobal
rpl_semi_sync_slave_enabledBooleanGlobal
rpl_semi_sync_slave_trace_levelIntegerGlobal
rpl_stop_slave_timeoutIntegerGlobal
secure_authBooleanGlobal
server_idIntegerGlobal
session_track_gtidsEnumerationBoth
session_track_schemaBooleanBoth
session_track_state_changeBooleanBoth
session_track_system_variablesStringBoth
session_track_transaction_infoEnumerationBoth
sha256_password_proxy_usersBooleanGlobal
show_compatibility_56BooleanGlobal
show_create_table_verbosityBooleanBoth
show_old_temporalsBooleanBoth
slave_allow_batchingBooleanGlobal
slave_checkpoint_groupIntegerGlobal
slave_checkpoint_periodIntegerGlobal
slave_compressed_protocolBooleanGlobal
slave_exec_modeEnumerationGlobal
slave_max_allowed_packetIntegerGlobal
slave_net_timeoutIntegerGlobal
slave_parallel_typeEnumerationGlobal
slave_parallel_workersIntegerGlobal
slave_pending_jobs_size_maxIntegerGlobal
slave_preserve_commit_orderBooleanGlobal
slave_rows_search_algorithmsSetGlobal
slave_sql_verify_checksumBooleanGlobal
slave_transaction_retriesIntegerGlobal
slow_launch_timeIntegerGlobal
slow_query_logBooleanGlobal
slow_query_log_fileFile nameGlobal
sort_buffer_sizeIntegerBoth
sql_auto_is_nullBooleanBoth
sql_big_selectsBooleanBoth
sql_buffer_resultBooleanBoth
sql_log_binBooleanSession
sql_log_offBooleanBoth
sql_modeSetBoth
sql_notesBooleanBoth
sql_quote_show_createBooleanBoth
sql_safe_updatesBooleanBoth
sql_select_limitIntegerBoth
sql_slave_skip_counterIntegerGlobal
sql_warningsBooleanBoth
stored_program_cacheIntegerGlobal
super_read_onlyBooleanGlobal
sync_binlogIntegerGlobal
sync_frmBooleanGlobal
sync_master_infoIntegerGlobal
sync_relay_logIntegerGlobal
sync_relay_log_infoIntegerGlobal
table_definition_cacheIntegerGlobal
table_open_cacheIntegerGlobal
thread_cache_sizeIntegerGlobal
thread_pool_high_priority_connectionIntegerBoth
thread_pool_max_unused_threadsIntegerGlobal
thread_pool_prio_kickup_timerIntegerBoth
thread_pool_stall_limitIntegerGlobal
time_zoneStringBoth
timestampNumericSession
tmp_table_sizeIntegerBoth
transaction_alloc_block_sizeIntegerBoth
transaction_allow_batchingBooleanSession
transaction_isolationEnumerationBoth
transaction_prealloc_sizeIntegerBoth
transaction_read_onlyBooleanBoth
transaction_write_set_extractionEnumerationBoth
tx_isolationEnumerationBoth
tx_read_onlyBooleanBoth
unique_checksBooleanBoth
updatable_views_with_limitBooleanBoth
validate_password_check_user_nameBooleanGlobal
validate_password_dictionary_fileFile nameGlobal
validate_password_lengthIntegerGlobal
validate_password_mixed_case_countIntegerGlobal
validate_password_number_countIntegerGlobal
validate_password_policyEnumerationGlobal
validate_password_special_char_countIntegerGlobal
version_tokens_sessionStringBoth
wait_timeoutIntegerBoth