SHOW VARIABLES - MySQL

Overview

SHOW VARIABLES displays the values of configurable system variables and session variables. System variables are shared among all sessions, while session variables are specific to each connection. This command is useful for checking current settings, troubleshooting, and performance tuning.

Syntax

SHOW [SESSION | GLOBAL] VARIABLES
[LIKE 'pattern'] [WHERE variable_name operator value]
[ORDER BY variable_name]

Options/Flags

  • SESSION: Show session variables only.
  • GLOBAL: Show system variables only. (Default)
  • LIKE 'pattern': Filter variables by name using wildcards.
  • WHERE variable_name operator value: Filter variables by value using comparison operators (e.g., =, <, >, LIKE).
  • ORDER BY variable_name: Sort the output by variable name.

Examples

1. Display all system variables:

SHOW GLOBAL VARIABLES;

2. Show session variable character_set_client:

SHOW SESSION VARIABLES LIKE 'character_set_client';

3. Filter variables with names starting with innodb_:

SHOW VARIABLES LIKE 'innodb_%';

4. Search for variables with values containing auto%:

SHOW VARIABLES WHERE variable_name LIKE '%auto%';

5. Sort the output by variable name:

SHOW VARIABLES ORDER BY variable_name;

Common Issues

  • Incorrect filter syntax: Ensure proper use of wildcards and comparison operators.
  • Variable not found: Verify the variable name is correct and exists in the current context (system or session).

Integration

SHOW VARIABLES can be combined with other commands, such as:

  • SET VARIABLE: Set a variable’s value.
  • SET DEFAULT ROLE: Restrict subsequent queries to specific variable settings.