Examples of MySQL SHOW VARIABLES Statement
Example 1
SHOW VARIABLES;
As the SHOW VARIABLES statement is used to show all the variables present in the server along with their current values, so here this statement is doing the same. In this case, you need to display all the variables and their values then you can use the “SHOW VARIABLES” statement.
Output:
| Variable Name | Value |
|---|---|
| activate_all_roles_on_login | OFF |
| auto_generate_certs | ON |
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
|
… |
… |
Example 2
SHOW VARIABLES LIKE '%character_set%';
By using the command SHOW VARIABLES LIKE ‘%character_set%’; you can view the variables that contains the phrase character set means all the variables related to the character sets will appear along with their values.
Output:
| Variable Name | Value |
|---|---|
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
|
… |
… |
Example 3
SHOW VARIABLES LIKE 'max_allowed_packet';
The “SHOW VARIABLES LIKE ‘max_allowed_packet’;” is used to display the highest number of packets that MySQL can contain. It is important when the data is being transferred. When you execute the command then you will get the output something like this:
Output:
| Variable Name | Value |
|---|---|
| max_allowed_packet | 16777216 |
Example 4
SHOW VARIABLES LIKE 'query_cache%';
Whenever you execute this pattern, it will give you the variables related to the query cache, it might contains the variables like query_cache_size, query_cache_type, etc.
Output:
| Variable Name | Value |
|---|---|
| have_query_cache | YES |
MySQL – SHOW VARIABLES Statement
MySQL is an open-source Relational Database Management System that stores data in the form of rows and tables and SQL is known as a programming language that is used to manipulate the data. We can perform many operations in an SQL server with the help of SQL programming language such as manipulating the data, retrieving the data, and storing the data.
In this article, we will explore the SHOW VARIABLES statement in MySQL which is used for many purposes such as viewing configuration settings, Troubleshooting and Monitoring, Security, and so on.