top of page
CerebroSQL

MySQL: 

SHOW VARIABLES

Syntax:
SHOW [GLOBAL | SESSION] VARIABLES
[LIKE 'pattern' | WHERE expr]

SHOW VARIABLES shows the values of MySQL system variables (see
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html).
This statement does not require any privilege. It requires only the
ability to connect to the server.

System variable information is also available from these sources:

o Performance Schema tables.
o The mysqladmin variables command.

For SHOW VARIABLES, a LIKE clause, if present, indicates which variable
names to match. A WHERE clause can be given to select rows using more
general conditions.

SHOW VARIABLES accepts an optional GLOBAL or SESSION variable scope
modifier:

o With a GLOBAL modifier, the statement displays global system variable
values. These are the values used to initialize the corresponding
session variables for new connections to MySQL. If a variable has no
global value, no value is displayed.

o With a SESSION modifier, the statement displays the system variable
values that are in effect for the current connection. If a variable
has no session value, the global value is displayed. LOCAL is a
synonym for SESSION.

o If no modifier is present, the default is SESSION.

The scope for each system variable is listed at
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html.

SHOW VARIABLES is subject to a version-dependent display-width limit.
For variables with very long values that are not completely displayed,
use SELECT as a workaround. For example:

SELECT @@GLOBAL.innodb_data_file_path;

Most system variables can be set at server startup (read-only variables
such as version_comment are exceptions). Many can be changed at runtime
with the SET statement.

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.

Example

bottom of page