รายละเอียดตัวแปรทั้งหมด MySQL

system-variable-mysql

เวลาเราต้องการสร้างตารางบน database สิ่งหนึ่งที่เป็นปัญหาของนักพัฒนาเว็บไซต์ นั่นคือ รายละเอียดของค่าตัวแปรแต่ละฟิล์ดว่าจะเลือกตัวไหนดี และมีอะไรบ้าง บทความนี้ได้รวบมตัวแปร หรือ system variable ต่างของระบบฐานข้อมูล MySQL มาให้เพื่อนๆ ได้นำไปศึกษาและใช้งานกันดู ?

คุณคิดว่า คุณสามารถจำค่าตัวแปรของ MySQL ได้ทั้งหมดหรือเปล่า ถ้ายังไม่แน่ใจ ลองหาคำตอบได้จากตารางด้านล่างต่อไปนี

MySQL System Variable Summary

Name

Cmd-
Line

Option
file

System
Var

Var
Scope

Dyna
-mic

auto_increment_increment Yes Yes Yes Both Yes
auto_increment_offset Yes Yes Yes Both Yes
autocommit Yes Yes Yes Session Yes
automatic_sp_privileges Yes Global Yes
back_log Yes Yes Yes Global No
basedir Yes Yes Yes Global No
bdb_cache_size Yes Yes Yes Global No
bdb-home Yes Yes No
– Variable: bdb_home Yes Global No
bdb-lock-detect Yes Yes No
– Variable: bdb_lock_detect Yes Global No
bdb_log_buffer_size Yes Yes Yes Global No
bdb-logdir Yes Yes No
– Variable: bdb_logdir Yes Global No
bdb_max_lock Yes Yes Yes Global No
bdb-shared-data Yes Yes No
– Variable: bdb_shared_data Yes Global No
bdb-tmpdir Yes Yes No
– Variable: bdb_tmpdir Yes Global No
big-tables Yes Yes Yes
– Variable: big_tables Yes Session Yes
bind-address Yes Yes Yes Global No
binlog_cache_size Yes Yes Yes Global Yes
bulk_insert_buffer_size Yes Yes Yes Both Yes
character_set_client Yes Both Yes
character_set_connection Yes Both Yes
character_set_database[a] 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
collation_connection Yes Both Yes
collation_database[b] 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
datadir Yes Yes Yes Global No
date_format Yes Both No
datetime_format Yes Both No
debug Yes Yes Yes Both Yes
default-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
div_precision_increment Yes Yes Yes Both Yes
engine-condition-pushdown Yes Yes Yes
– Variable: engine_condition_pushdown Yes Both Yes
error_count Yes Session No
expire_logs_days Yes Yes Yes Global Yes
flush Yes Yes Yes Global Yes
flush_time Yes Yes Yes Global Yes
foreign_key_checks Yes Session 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
group_concat_max_len Yes Yes Yes Both Yes
have_archive Yes Global No
have_bdb Yes Global No
have_blackhole_engine Yes Global No
have_community_features Yes Global No
have_compress Yes Global No
have_crypt Yes Global No
have_csv Yes Global No
have_example_engine Yes Global No
have_federated_engine Yes Global No
have_geometry Yes Global No
have_innodb Yes Global No
have_isam Yes Global No
have_merge_engine Yes Global No
have_ndbcluster Yes Global No
have_openssl Yes Global No
have_profiling Yes Global No
have_query_cache Yes Global No
have_raid Yes Global No
have_rtree_keys Yes Global No
have_ssl Yes Global No
have_symlink Yes Global No
hostname Yes Global No
identity 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_hash_index Yes Yes Yes Global No
innodb_additional_mem_pool_size Yes Yes Yes Global No
innodb_autoextend_increment Yes Yes Yes Global Yes
innodb_buffer_pool_awe_mem_mb Yes Yes Yes Global No
innodb_buffer_pool_size Yes Yes Yes Global No
innodb_checksums Yes Yes Yes Global No
innodb_commit_concurrency Yes Yes Yes Global Yes
innodb_concurrency_tickets Yes Yes Yes Global Yes
innodb_data_file_path Yes Yes Yes Global No
innodb_data_home_dir Yes Yes Yes Global No
innodb_doublewrite Yes Yes Yes Global No
innodb_fast_shutdown Yes Yes Yes Global Yes
innodb_file_io_threads Yes Yes Yes Global No
innodb_file_per_table Yes Yes Yes Global No
innodb_flush_log_at_trx_commit Yes Yes Yes Global Yes
innodb_flush_method Yes Yes Yes Global No
innodb_force_recovery Yes Yes Yes Global No
innodb_lock_wait_timeout Yes Yes Yes Global No
innodb_locks_unsafe_for_binlog Yes Yes Yes Global No
innodb_log_arch_dir Yes Yes Yes Global No
innodb_log_archive Yes Yes Yes Global No
innodb_log_buffer_size Yes Yes Yes Global No
innodb_log_file_size Yes Yes Yes Global No
innodb_log_files_in_group Yes Yes Yes Global No
innodb_log_group_home_dir Yes Yes Yes Global No
innodb_max_dirty_pages_pct Yes Yes Yes Global Yes
innodb_max_purge_lag Yes Yes Yes Global Yes
innodb_mirrored_log_groups Yes Yes Yes Global No
innodb_open_files Yes Yes Yes Global No
innodb_rollback_on_timeout Yes Yes Yes Global No
innodb_support_xa Yes Yes Yes Both Yes
innodb_sync_spin_loops Yes Yes Yes Global Yes
innodb_table_locks Yes Yes Yes Both Yes
innodb_thread_concurrency Yes Yes Yes Global Yes
innodb_thread_sleep_delay Yes Yes Yes Global Yes
innodb_use_legacy_cardinality_algorithm Yes Yes Yes Global Yes
insert_id Yes Session Yes
interactive_timeout Yes Yes Yes Both Yes
join_buffer_size Yes Yes Yes Both Yes
keep_files_on_create Yes Yes Yes Both Yes
key_buffer_size Yes Yes Yes Global Yes
key_cache_age_threshold Yes Yes Yes Global Yes
key_cache_block_size Yes Yes Yes Global Yes
key_cache_division_limit Yes Yes Yes Global Yes
language Yes Yes Yes Global No
large_files_support Yes Global No
large_page_size Yes Global No
large-pages Yes Yes No
– Variable: large_pages Yes Global No
last_insert_id Yes Session Yes
lc_time_names Yes Both Yes
license Yes Global No
local_infile Yes Global Yes
locked_in_memory Yes Global No
log Yes Yes Yes Global No
log_bin Yes Global No
log-bin Yes Yes Yes Global No
log-bin-trust-function-creators Yes Yes Yes
– Variable: log_bin_trust_function_creators Yes Global Yes
log-bin-trust-routine-creators Yes Yes Yes
– Variable: log_bin_trust_routine_creators Yes Global Yes
log-error Yes Yes No
– Variable: log_error Yes Global No
log-queries-not-using-indexes Yes Yes Yes
– Variable: log_queries_not_using_indexes Yes Global Yes
log-slave-updates Yes Yes No
– Variable: log_slave_updates Yes Global No
log_slave_updates Yes Yes Yes Global No
log-slow-queries Yes Yes No
– Variable: log_slow_queries Yes Global No
log-warnings Yes Yes Yes
– Variable: log_warnings Yes Both Yes
long_query_time Yes Yes Yes Both Yes
low-priority-updates Yes Yes Yes
– Variable: low_priority_updates Yes Both Yes
lower_case_file_system Yes Yes Yes Global No
lower_case_table_names Yes Yes Yes Global No
max_allowed_packet Yes Yes Yes Global Yes
max_binlog_cache_size Yes Yes Yes Global Yes
max_binlog_size Yes Yes Yes Global Yes
max_connect_errors Yes Yes Yes Global Yes
max_connections Yes Yes Yes Global Yes
max_delayed_threads Yes Yes Yes Both Yes
max_error_count Yes Yes Yes Both Yes
max_heap_table_size Yes Yes Yes Both Yes
max_insert_delayed_threads Yes Both Yes
max_join_size Yes Yes Yes Both Yes
max_length_for_sort_data Yes Yes Yes Both Yes
max_prepared_stmt_count Yes Yes Yes Global Yes
max_relay_log_size Yes Yes Yes Global Yes
max_seeks_for_key Yes Yes Yes Both Yes
max_sort_length Yes Yes Yes Both Yes
max_sp_recursion_depth Yes Yes Yes Both Yes
max_user_connections Yes Yes Yes Both Yes
max_write_lock_count Yes Yes Yes Global Yes
memlock Yes Yes Yes Global No
multi_range_count Yes Yes Yes Both Yes
myisam_data_pointer_size Yes Yes Yes Global Yes
myisam_max_extra_sort_file_size Yes Yes Yes Global No
myisam_max_sort_file_size Yes Yes Yes Global Yes
myisam_mmap_size Yes Yes Yes Global No
myisam_recover_options Yes Global No
myisam_repair_threads Yes Yes Yes Both Yes
myisam_sort_buffer_size Yes Yes Yes Both Yes
myisam_stats_method Yes Yes Yes Both Yes
named_pipe Yes Global No
ndb_autoincrement_prefetch_sz Yes Yes Yes Both Yes
ndb_cache_check_time Yes Yes Yes Global Yes
ndb_force_send Yes Yes Yes Both Yes
ndb_use_exact_count Yes Both Yes
ndb_use_transactions Yes Yes Yes Both Yes
net_buffer_length Yes Yes Yes Both Yes
net_read_timeout Yes Yes Yes Both Yes
net_retry_count Yes Yes Yes Both Yes
net_write_timeout Yes Yes Yes Both Yes
new Yes Yes Yes Both Yes
old_passwords Yes Both Yes
open-files-limit Yes Yes No
– Variable: open_files_limit Yes Global No
optimizer_prune_level Yes Yes Yes Both Yes
optimizer_search_depth Yes Yes Yes Both Yes
pid-file Yes Yes No
– Variable: pid_file Yes Global No
plugin_dir Yes Yes Yes Global No
port Yes Yes Yes Global No
preload_buffer_size Yes Yes Yes Both Yes
prepared_stmt_count Yes Global No
profiling Yes Session Yes
profiling_history_size Yes Yes Yes Both Yes
protocol_version Yes Global No
pseudo_thread_id Yes Session Yes
query_alloc_block_size Yes Yes Yes Both Yes
query_cache_limit Yes Yes Yes Global Yes
query_cache_min_res_unit Yes Yes Yes Global Yes
query_cache_size Yes Yes Yes Global Yes
query_cache_type Yes Yes Yes Both Yes
query_cache_wlock_invalidate Yes Yes Yes Both Yes
query_prealloc_size Yes Yes Yes Both Yes
rand_seed1 Yes Session Yes
rand_seed2 Yes Session Yes
range_alloc_block_size Yes Yes Yes Both Yes
read_buffer_size Yes Yes Yes Both Yes
read_only Yes Yes Yes Global Yes
read_rnd_buffer_size Yes Yes Yes Both Yes
relay-log Yes Yes No
– Variable: relay_log Yes Global No
relay-log-index Yes Yes No
– Variable: relay_log_index Yes Global No
relay_log_index Yes Yes Yes Global No
relay_log_info_file Yes Yes Yes Global No
relay_log_purge Yes Yes Yes Global Yes
relay_log_space_limit Yes Yes Yes Global No
report-host Yes Yes No
– Variable: report_host Yes Global No
report-password Yes Yes No
– Variable: report_password Yes Global No
report-port Yes Yes No
– Variable: report_port Yes Global No
report-user Yes Yes No
– Variable: report_user Yes Global No
rpl_recovery_rank Yes Global Yes
safe-show-database Yes Yes Yes Global Yes
secure-auth Yes Yes Yes
– Variable: secure_auth Yes Global Yes
secure-file-priv Yes Yes No
– Variable: secure_file_priv Yes Global No
server-id Yes Yes Yes
– Variable: server_id Yes Global Yes
shared_memory Yes Global No
shared_memory_base_name Yes Global No
skip-external-locking Yes Yes No
– Variable: skip_external_locking Yes Global No
skip-name-resolve Yes Yes No
– Variable: skip_name_resolve Yes Global No
skip-networking Yes Yes No
– Variable: skip_networking Yes Global No
skip-show-database Yes Yes No
– Variable: skip_show_database Yes Global No
skip-sync-bdb-logs Yes Yes Yes Global No
slave_compressed_protocol Yes Yes Yes Global Yes
slave-load-tmpdir Yes Yes No
– Variable: slave_load_tmpdir Yes Global No
slave-net-timeout Yes Yes Yes
– Variable: slave_net_timeout Yes Global Yes
slave-skip-errors Yes Yes No
– Variable: slave_skip_errors Yes Global No
slave_transaction_retries Yes Yes Yes Global Yes
slow_launch_time Yes Yes Yes Global Yes
socket Yes Yes Yes Global No
sort_buffer_size Yes Yes Yes Both Yes
sql_auto_is_null Yes Session Yes
sql_big_selects Yes Session Yes
sql_big_tables Yes Session Yes
sql_buffer_result Yes Session Yes
sql_log_bin Yes Session Yes
sql_log_off Yes Session Yes
sql_log_update Yes Session Yes
sql_low_priority_updates Yes Both Yes
sql_max_join_size Yes Both Yes
sql-mode Yes Yes Yes
– Variable: sql_mode Yes Both Yes
sql_notes Yes Session Yes
sql_quote_show_create Yes Session Yes
sql_safe_updates Yes Session Yes
sql_select_limit Yes Both Yes
sql_slave_skip_counter Yes Global Yes
sql_warnings Yes Session Yes
ssl-ca Yes Yes No
– Variable: ssl_ca Yes Global No
ssl-capath Yes Yes No
– Variable: ssl_capath Yes Global No
ssl-cert Yes Yes No
– Variable: ssl_cert Yes Global No
ssl-cipher Yes Yes No
– Variable: ssl_cipher Yes Global No
ssl-key Yes Yes No
– Variable: ssl_key Yes Global No
storage_engine Yes Both Yes
sync-bdb-logs Yes Yes No
– Variable: sync_bdb_logs Yes Global No
sync_binlog Yes Yes Yes Global Yes
sync_frm Yes Yes Yes Global Yes
system_time_zone Yes Global No
table_cache Yes Yes Yes Global Yes
table_lock_wait_timeout Yes Yes Yes Global Yes
table_type Yes Both Yes
thread_cache_size Yes Yes Yes Global Yes
thread_concurrency Yes Yes Yes Global No
thread_stack Yes Yes Yes Global No
time_format Yes Both No
time_zone Yes Yes Yes Both Yes
timed_mutexes Yes Yes Yes Global Yes
timestamp Yes Session Yes
tmp_table_size Yes Yes Yes Both Yes
tmpdir Yes Yes Yes Global No
transaction_alloc_block_size Yes Yes Yes Both Yes
transaction_prealloc_size Yes Yes Yes Both Yes
tx_isolation Yes Both Yes
unique_checks Yes Session Yes
updatable_views_with_limit Yes Yes Yes Both Yes
version Yes Global No
version_comment Yes Global No
version_compile_machine Yes Global No
version_compile_os Yes Global No
wait_timeout Yes Yes Yes Both Yes
warning_count Yes Session No

ที่มา : MySQL.com