เวลาเราต้องการสร้างตารางบน database สิ่งหนึ่งที่เป็นปัญหาของนักพัฒนาเว็บไซต์ นั่นคือ รายละเอียดของค่าตัวแปรแต่ละฟิล์ดว่าจะเลือกตัวไหนดี และมีอะไรบ้าง บทความนี้ได้รวบมตัวแปร หรือ system variable ต่างของระบบฐานข้อมูล MySQL มาให้เพื่อนๆ ได้นำไปศึกษาและใช้งานกันดู ?
คุณคิดว่า คุณสามารถจำค่าตัวแปรของ MySQL ได้ทั้งหมดหรือเปล่า ถ้ายังไม่แน่ใจ ลองหาคำตอบได้จากตารางด้านล่างต่อไปนี
MySQL System Variable Summary
|
Name |
Cmd- |
Option |
System |
Var |
Dyna |
| 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
