:: DEVELOPER ZONE

MySQL 5.0 Reference Manual :: 13 SQL Statement Syntax :: 13.5 Database Administration Statements :: 13.5.4 SHOW Syntax :: 13.5.4.24 SHOW VARIABLES Syntax

  • MySQL 5.0 Reference Manual

  • 13.5.4 SHOW Syntax
  • 13.5.4.1 SHOW CHARACTER SET Syntax
  • 13.5.4.2 SHOW COLLATION Syntax
  • 13.5.4.3 SHOW COLUMNS Syntax
  • 13.5.4.4 SHOW CREATE DATABASE Syntax
  • 13.5.4.5 SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax
  • 13.5.4.6 SHOW CREATE TABLE Syntax
  • 13.5.4.7 SHOW CREATE VIEW Syntax
  • 13.5.4.8 SHOW DATABASES Syntax
  • 13.5.4.9 SHOW ENGINE Syntax
  • 13.5.4.10 SHOW ENGINES Syntax
  • 13.5.4.11 SHOW ERRORS Syntax
  • 13.5.4.12 SHOW GRANTS Syntax
  • 13.5.4.13 SHOW INDEX Syntax
  • 13.5.4.14 SHOW INNODB STATUS Syntax
  • 13.5.4.15 SHOW LOGS Syntax
  • 13.5.4.16 SHOW OPEN TABLES Syntax
  • 13.5.4.17 SHOW PRIVILEGES Syntax
  • 13.5.4.18 SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS Syntax
  • 13.5.4.19 SHOW PROCESSLIST Syntax
  • 13.5.4.20 SHOW STATUS Syntax
  • 13.5.4.21 SHOW TABLE STATUS Syntax
  • 13.5.4.22 SHOW TABLES Syntax
  • 13.5.4.23 SHOW TRIGGERS Syntax
  • 13.5.4.24 SHOW VARIABLES Syntax
  • 13.5.4.25 SHOW WARNINGS Syntax


13.5.4.24. SHOW VARIABLES Syntax

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

SHOW VARIABLES shows the values of MySQL system variables. This information also can be obtained using the mysqladmin variables command.

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.2.3, “Using System Variables”, and Section 13.5.3, “SET Syntax”.

Partial output is shown here. The list of names and values may be different for your server. Section 5.2.2, “Server System Variables”, describes the meaning of each variable, and Section 7.5.2, “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                         | /                                   |
| bdb_cache_size                  | 8388600                             |
| bdb_home                        | /var/lib/mysql/                     |
| bdb_log_buffer_size             | 32768                               |
...
| max_connections                 | 100                                 |
| max_connect_errors              | 10                                  |
| max_delayed_threads             | 20                                  |
| max_error_count                 | 64                                  |
| max_heap_table_size             | 16777216                            |
| max_join_size                   | 4294967295                          |
| max_relay_log_size              | 0                                   |
| max_sort_length                 | 1024                                |
...
| 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.0.19-Max                          |
| version_comment                 | MySQL Community Edition - Max (GPL) |
| version_compile_machine         | i686                                |
| version_compile_os              | pc-linux-gnu                        |
| wait_timeout                    | 28800                               |
+---------------------------------+-------------------------------------+

With a LIKE clause, the statement displays only those variables that match the pattern. To obtain a specific variable name, 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

Posted by Martin Mokrejs on August 27 2003 10:57am[Delete] [Edit]

Improve docs for max_allowed_packet

http://bugs.mysql.com/bug.php?id=1011