ITEEDU

13.5.4.21. SHOW VARIABLES语法

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']

SHOW VARIABLES显示了部门MySQL系统变量的值。本信息也可以使用mysqladmin variables命令获得。

使用GLOBAL选项，您可以获得被用于MySQL新连接的值。使用SESSION，您可以得到对于当前连接起效的值。如果您两个选项都不使用，默认值为SESSION。

LOCAL是SESSION的同义词。

如果默认值不合适，当mysqld启动时或在SET语句运行过程中，您可以使用命令行选项设置多数的这类变量。请参见5.3.1节，“mysqld命令行选项”和13.5.3节，“SET语法。

此处显示了部分的输出。对于您的服务器，变量和值的清单会有所不同。在5.3.3节，“服务器系统变量”中给出了每个变量的意义。在7.5.2节，“调节服务器参数”中提供了有关调整变量的信息。

mysql> SHOW VARIABLES;
+---------------------------------+-----------------------------------------------+
| Variable_name                   | Value                                         |
+---------------------------------+-----------------------------------------------+
| auto_increment_increment        | 1                                             |
| auto_increment_offset           | 1                                             |
| automatic_sp_privileges         | ON                                            |
| back_log                        | 50                                            |
| basedir                         | /home/jon/bin/mysql-5.1/                      |
| binlog_cache_size               | 32768                                         |
| bulk_insert_buffer_size         | 8388608                                       |
| character_set_client            | latin1                                        |
| character_set_connection        | latin1                                        |
...                               ...                                              
| max_user_connections            | 0                                             |
| max_write_lock_count            | 4294967295                                    |
| multi_range_count               | 256                                           |
| myisam_data_pointer_size        | 6                                             |
| myisam_max_sort_file_size       | 2147483647                                    |
| myisam_recover_options          | OFF                                           |
| myisam_repair_threads           | 1                                             |
| myisam_sort_buffer_size         | 8388608                                       |
| ndb_autoincrement_prefetch_sz   | 32                                            |
| ndb_cache_check_time            | 0                                             |
| ndb_force_send                  | ON                                            |
...                               ...                                                       ...    
| time_zone                       | SYSTEM                                        |
| timed_mutexes                   | OFF                                           |
| tmp_table_size                  | 33554432                                      |
| tmpdir                          |                                               |
| transaction_alloc_block_size    | 8192                                          |
| transaction_prealloc_size       | 4096                                          |
| tx_isolation                    | REPEATABLE-READ                               |
| updatable_views_with_limit      | YES                                           |
| version                         | 5.1.2-alpha-log                               |
| version_comment                 | Source distribution                           |
| version_compile_machine         | i686                                          |
| version_compile_os              | suse-linux                                    |
| wait_timeout                    | 28800                                         |
+---------------------------------+-----------------------------------------------+

使用LIKE子句，本语句只显示与样式相匹配的变量：

mysql> SHOW VARIABLES LIKE 'have%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| have_archive          | YES      |
| have_bdb              | NO       |
| have_blackhole_engine | YES      |
| have_compress         | YES      |
| have_crypt            | YES      |
| have_csv              | YES      |
| have_example_engine   | NO       |
| have_federated_engine | NO       |
| have_geometry         | YES      |
| have_innodb           | YES      |
| have_isam             | NO       |
| have_ndbcluster       | DISABLED |
| have_openssl          | NO       |
| have_partition_engine | YES      |
| have_query_cache      | YES      |
| have_raid             | NO       |
| have_rtree_keys       | YES      |
| have_symlink          | YES      |
+-----------------------+----------+