Mysql：命令选项、配置选项、（全局、会话）系统变量、状态变量：如何使用系统变量?

概述

  • mysql有大量的系统变量，从变量的适用范围分为全局系统变量和会话系统变量。全局系统变量仅仅影响新的连接会话，为其提供会话系统变量的默认值；而后者仅在当前的连接里存在并有效；
  • 每个全局系统变量都有其 编译默认值，又称全局默认值；
  • 全局系统变量编译默认值可以通过命令选项、选项文件配置选项予以改变，其值 称之为 全局系统变量的全局当前值；
  • 某些全局系统变量的全局当前值可以通过set global改变，这些全局系统变量称之为 动态全局系统变量；当然也有只读的不可改变的全局系统变量
  • 新的连接会创建一套仅适用于该连接会话的会话系统变量；
  • 会话系统变量的默认值 为 对应的全局系统变量的全局当前值，称之为 会话默认值
  • 某些会话系统变量的默认值可以通过set改变，这些会话系统变量又叫动态会话系统变量；当然也有只读的不可改变的会话系统变量
  • 有些会话系统变量是会话里独有的，不妨叫做 独有会话系统变量；其中也有动态和只读之分

系统变量的范围限制符

  • GLOBAL　　@@GLOBAL.　　
  • SESSION　　@@SESSION　　@@　　LOCAL　　@@LOCAL.


选项、系统变量设置　　--各方式各有不同的设置范围，并不完全相同；另外，对同一参数的设置，表达式也不相同：如set可以使用表达式，而其他方式则不可以

  1. 命令选项
  2. 选项文件
  3. 运行时set
  • 很多命令选项、配置选项、全局系统变量、会话系统变量system_variable_name 都一个对应的 max-system_variable_name，对其资源的最大值进行了限定
  • 设置动态全局系统变量必须具有super系统权限；设置会话系统变量不需要任何权限
  • 如果做了不适当的set，mysql会产生错误消息。例如 只适用于会话系统变量的设置，你设置到了全局系统变量上；或者只适用于全局的系统变量设置，你没有指明是全局设置（因为mysql对没有指明global的设置认为是会话的）
  • set语句可以设置多个系统变量，各设置之间使用逗号“,”分割。最后出现的global或session会作为后面没有做出范围限定的变量的默认范围；但是如果set语句仅设置一个系统变量，且没有global或session范围修饰，则作为session范围
  • 强调：set @@sys_var=var_value，总是设置会话系统变量。var_value中可以使用 表达式、变量——如果此处使用了@@sys_other_var,mysql或自动寻找该存在的变量，先会话，后全局，而不仅仅限于会话级变量！！。所以，select @@sys_var 可以返回会话系统变量的值；如果不存在，就会返回全局系统变量的值；如果全局系统变量也不存在，就产生unknown system variable的错误！
  • 可以通过set使用default值：使会话默认值=全局当前值；或使全局当前值=编译默认值。注意并不是所有的系统变量都具有default值
  • 某些命令选项、配置选项可以使用k、m、g、t..等表示计算机上字节大小的单位（不区分大小写；但绝对不可以使用表达式！相反，set可以使用表达式，却不能使用k、m、g、t..等。另外，对于开关型bool选项或变量设置：命令选项、配置选项可以使用0、1，不可以使用on、off！set 两种形式都可以！

显示、获取系统变量　　--两种方式各有不同的显示范围，并不完全相同

  1. show [GLOBAL|SESSION] variales；　　　　--显示所有系统变量
  2. show [GLOBAL|SESSION] variales like 'special system variable';　　--显示指定的系统变量
  3. show [GLOBAL|SESSION] variales like '%pat[\_]ern[%\_]';　　--显示模式匹配的系统变量
  4. select [GLOBAL|SESSION] @@system_variable_name

仅仅在session里的会话系统变量　　--一些仅仅存在于会话的系统变量：只读变量、可配置变量。这些变量绝大多数不能通过show variables 来显示，而仅仅能通过select来显示！

Name Cmd-Line Option file System Var Dynamic
autocommit     Yes Yes
big-tables Yes Yes    
- Variable: big_tables     Yes Yes
error_count     Yes No
foreign_key_checks     Yes Yes
identity     Yes Yes
insert_id     Yes Yes
last_insert_id     Yes Yes
ndb_table_no_logging     Yes Yes
ndb_table_temporary     Yes Yes
profiling     Yes Yes
rand_seed1     Yes Yes
rand_seed2     Yes Yes
sql_auto_is_null     Yes Yes
sql_big_selects     Yes Yes
sql_big_tables     Yes Yes
sql_buffer_result     Yes Yes
sql_log_bin     Yes Yes
sql_log_off     Yes Yes
sql_log_update     Yes Yes
sql_notes     Yes Yes
sql_quote_show_create     Yes Yes
sql_safe_updates     Yes Yes
sql_warnings     Yes Yes
timestamp     Yes Yes
transaction_allow_batching     Yes Yes
unique_checks     Yes Yes
warning_count     Yes No

全部动态系统变量（包括仅仅出现在session总的会话系统变量）　　--可以通过select显示，大部分也可以通过show variables显示

Variable Name Variable Type Variable Scope
auto_increment_increment numeric GLOBAL | SESSION
auto_increment_offset numeric GLOBAL | SESSION
autocommit boolean SESSION
automatic_sp_privileges boolean GLOBAL
big_tables boolean SESSION
binlog_cache_size numeric GLOBAL
binlog_format enumeration GLOBAL | SESSION
bulk_insert_buffer_size numeric GLOBAL | SESSION
character_set_client string GLOBAL | SESSION
character_set_connection string GLOBAL | SESSION
character_set_database string GLOBAL | SESSION
character_set_filesystem string GLOBAL | SESSION
character_set_results string GLOBAL | SESSION
character_set_server string GLOBAL | SESSION
collation_connection string GLOBAL | SESSION
collation_database string GLOBAL | SESSION
collation_server string GLOBAL | SESSION
completion_type numeric GLOBAL | SESSION
concurrent_insert boolean GLOBAL
connect_timeout numeric GLOBAL
date_format string GLOBAL | SESSION
datetime_format string GLOBAL | SESSION
debug string GLOBAL | SESSION
default_week_format numeric GLOBAL | SESSION
delay_key_write enumeration GLOBAL
delayed_insert_limit numeric GLOBAL
delayed_insert_timeout numeric GLOBAL
delayed_queue_size numeric GLOBAL
div_precision_increment numeric GLOBAL | SESSION
engine_condition_pushdown boolean GLOBAL | SESSION
event_scheduler enumeration GLOBAL
expire_logs_days numeric GLOBAL
flush boolean GLOBAL
flush_time numeric GLOBAL
foreign_key_checks boolean SESSION
ft_boolean_syntax string GLOBAL
general_log boolean GLOBAL
general_log_file filename GLOBAL
group_concat_max_len numeric GLOBAL | SESSION
identity numeric SESSION
init_connect string GLOBAL
init_slave string GLOBAL
innodb_autoextend_increment numeric GLOBAL
innodb_commit_concurrency numeric GLOBAL
innodb_concurrency_tickets numeric GLOBAL
innodb_fast_shutdown boolean GLOBAL
innodb_flush_log_at_trx_commit numeric GLOBAL
innodb_max_dirty_pages_pct numeric GLOBAL
innodb_max_purge_lag numeric GLOBAL
innodb_stats_on_metadata boolean GLOBAL
innodb_support_xa boolean GLOBAL | SESSION
innodb_sync_spin_loops numeric GLOBAL
innodb_table_locks boolean GLOBAL | SESSION
innodb_thread_concurrency numeric GLOBAL
innodb_thread_sleep_delay numeric GLOBAL
innodb_use_legacy_cardinality_algorithm boolean GLOBAL
insert_id numeric SESSION
interactive_timeout numeric GLOBAL | SESSION
join_buffer_size numeric GLOBAL | SESSION
keep_files_on_create boolean GLOBAL | SESSION
key_buffer_size numeric GLOBAL
key_cache_age_threshold numeric GLOBAL
key_cache_block_size numeric GLOBAL
key_cache_division_limit numeric GLOBAL
last_insert_id numeric SESSION
lc_time_names string GLOBAL | SESSION
local_infile   GLOBAL
log string GLOBAL
log_bin_trust_function_creators boolean GLOBAL
log_bin_trust_routine_creators boolean GLOBAL
log_output enumeration GLOBAL
log_queries_not_using_indexes boolean GLOBAL
log_slow_queries boolean GLOBAL
log_warnings numeric GLOBAL | SESSION
long_query_time numeric GLOBAL | SESSION
low_priority_updates boolean GLOBAL | SESSION
max_allowed_packet numeric GLOBAL | SESSION
max_binlog_cache_size numeric GLOBAL
max_binlog_size numeric GLOBAL
max_connect_errors numeric GLOBAL
max_connections numeric GLOBAL
max_delayed_threads numeric GLOBAL | SESSION
max_error_count numeric GLOBAL | SESSION
max_heap_table_size numeric GLOBAL | SESSION
max_insert_delayed_threads numeric GLOBAL | SESSION
max_join_size numeric GLOBAL | SESSION
max_length_for_sort_data numeric GLOBAL | SESSION
max_prepared_stmt_count numeric GLOBAL
max_relay_log_size numeric GLOBAL
max_seeks_for_key numeric GLOBAL | SESSION
max_sort_length numeric GLOBAL | SESSION
max_sp_recursion_depth numeric GLOBAL | SESSION
max_tmp_tables numeric GLOBAL | SESSION
max_user_connections numeric GLOBAL | SESSION
max_write_lock_count numeric GLOBAL
min_examined_row_limit numeric GLOBAL | SESSION
multi_range_count numeric GLOBAL | SESSION
myisam_data_pointer_size numeric GLOBAL
myisam_max_sort_file_size numeric GLOBAL
myisam_repair_threads numeric GLOBAL | SESSION
myisam_sort_buffer_size numeric GLOBAL | SESSION
myisam_stats_method enumeration GLOBAL | SESSION
myisam_use_mmap boolean GLOBAL
ndb_autoincrement_prefetch_sz numeric GLOBAL | SESSION
ndb_cache_check_time numeric GLOBAL
ndb_extra_logging numeric GLOBAL
ndb_force_send boolean GLOBAL | SESSION
ndb_log_empty_epochs boolean GLOBAL
ndb_log_update_as_write boolean GLOBAL
ndb_log_updated_only boolean GLOBAL
ndb_optimization_delay numeric GLOBAL
ndb_table_no_logging boolean SESSION
ndb_table_temporary boolean SESSION
ndb_use_exact_count boolean GLOBAL | SESSION
net_buffer_length numeric GLOBAL | SESSION
net_read_timeout numeric GLOBAL | SESSION
net_retry_count numeric GLOBAL | SESSION
net_write_timeout numeric GLOBAL | SESSION
new boolean GLOBAL | SESSION
old_passwords boolean GLOBAL | SESSION
optimizer_prune_level boolean GLOBAL | SESSION
optimizer_search_depth numeric GLOBAL | SESSION
optimizer_switch set GLOBAL | SESSION
preload_buffer_size numeric GLOBAL | SESSION
profiling boolean SESSION
profiling_history_size numeric GLOBAL | SESSION
query_alloc_block_size numeric GLOBAL | SESSION
query_cache_limit numeric GLOBAL
query_cache_min_res_unit numeric GLOBAL
query_cache_size numeric GLOBAL
query_cache_type enumeration GLOBAL | SESSION
query_cache_wlock_invalidate boolean GLOBAL | SESSION
query_prealloc_size numeric GLOBAL | SESSION
rand_seed1 numeric SESSION
rand_seed2 numeric SESSION
range_alloc_block_size numeric GLOBAL | SESSION
read_buffer_size numeric GLOBAL | SESSION
read_only numeric GLOBAL
read_rnd_buffer_size numeric GLOBAL | SESSION
relay_log_purge boolean GLOBAL
rpl_recovery_rank numeric GLOBAL
safe_show_database boolean GLOBAL
secure_auth boolean GLOBAL
server_id numeric GLOBAL
slave_allow_batching boolean GLOBAL
slave_compressed_protocol boolean GLOBAL
slave_exec_mode enumeration GLOBAL
slave_net_timeout numeric GLOBAL
slave_transaction_retries numeric GLOBAL
slow_launch_time numeric GLOBAL
slow_query_log boolean GLOBAL
slow_query_log_file filename GLOBAL
sort_buffer_size numeric GLOBAL | SESSION
sql_auto_is_null boolean SESSION
sql_big_selects boolean SESSION
sql_big_tables boolean SESSION
sql_buffer_result boolean SESSION
sql_log_bin boolean SESSION
sql_log_off boolean SESSION
sql_log_update boolean SESSION
sql_low_priority_updates boolean GLOBAL | SESSION
sql_max_join_size numeric GLOBAL | SESSION
sql_mode set GLOBAL | SESSION
sql_notes boolean SESSION
sql_quote_show_create boolean SESSION
sql_safe_updates boolean SESSION
sql_select_limit numeric GLOBAL | SESSION
sql_slave_skip_counter numeric GLOBAL
sql_warnings boolean SESSION
storage_engine enumeration GLOBAL | SESSION
sync_binlog numeric GLOBAL
sync_frm boolean GLOBAL
table_cache numeric GLOBAL
table_definition_cache numeric GLOBAL
table_lock_wait_timeout numeric GLOBAL
table_open_cache numeric GLOBAL
table_type enumeration GLOBAL | SESSION
thread_cache_size numeric GLOBAL
time_format string GLOBAL | SESSION
time_zone string GLOBAL | SESSION
timed_mutexes boolean GLOBAL
timestamp string SESSION
tmp_table_size numeric GLOBAL | SESSION
transaction_alloc_block_size numeric GLOBAL | SESSION
transaction_allow_batching boolean SESSION
transaction_prealloc_size numeric GLOBAL | SESSION
tx_isolation enumeration GLOBAL | SESSION
unique_checks boolean SESSION
updatable_views_with_limit boolean GLOBAL | SESSION
wait_timeout numeric GLOBAL | SESSION
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jinzhenshui/p/1502563.html