SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern
' | WHEREexpr
]
SHOW VARIABLES
shows the values
of MySQL system variables. This information also can be obtained
using the mysqladmin variables command. The
LIKE
clause, if present, indicates
which variable names to match. The WHERE
clause can be given to select rows using more general
conditions, as discussed in Section 19.28, “Extensions to SHOW
Statements”.
This statement does not require any privilege. It requires only
the ability to connect to the server.
With the GLOBAL
modifier,
SHOW VARIABLES
displays the
values that are used for new connections to MySQL. With
SESSION
, it displays the values that are in
effect for the current connection. If no modifier is present,
the default is SESSION
.
LOCAL
is a synonym for
SESSION
.
If the default system variable values are unsuitable, you can
set them using command options when mysqld
starts, and most can be changed at runtime with the
SET
statement. See Section 5.1.6, “Using System Variables”, and
Section 12.5.4, “SET
Syntax”.
Partial output is shown here. The list of names and values may be different for your server. Section 5.1.4, “Server System Variables”, describes the meaning of each variable, and Section 7.5.3, “Tuning Server Parameters”, provides information about tuning them.
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.6-alpha-log |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | suse-linux |
| wait_timeout | 28800 |
+---------------------------------+---------------------------+
With a LIKE
clause, the statement
displays only rows for those variables with names that match the
pattern. To obtain the row for a specific variable, use a
LIKE
clause as shown:
SHOW VARIABLES LIKE 'max_join_size'; SHOW SESSION VARIABLES LIKE 'max_join_size';
To get a list of variables whose name match a pattern, use the
“%
” wildcard character in a
LIKE
clause:
SHOW VARIABLES LIKE '%size%'; SHOW GLOBAL VARIABLES LIKE '%size%';
Wildcard characters can be used in any position within the
pattern to be matched. Strictly speaking, because
“_
” is a wildcard that matches
any single character, you should escape it as
“\_
” to match it literally. In
practice, this is rarely necessary.
User Comments
This SHOW command can also be used in conjunction with WHERE. For example, to show all variables that have a numeric setting higher then zero, you can use:
SHOW VARIABLES WHERE VALUE > 0;
Or, if you would like to see all variables, except the storage engine specific ones, you can use the following expression:
SHOW VARIABLES WHERE Variable_Name NOT LIKE '%myisam%' AND Variable_Name NOT LIKE '%innodb%';
Add your own comment.