MySQL - SHOW VARIABLES Statement
Last Updated :
14 May, 2024
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.
SHOW VARIABLES Statement
SHOW VARABLES statement in MySQL is one of the most used commands in MySQL server as it is used for retrieving the Global and Session variables and also for many other operations, in short, it is used for displaying the names and values of system variables. We can also use it with the LIKE and WHERE clauses in the condition of retrieving some particular variables.
Syntax:
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr];
The parameters used in the syntax of SHOW VARIABLES are as follows:
- GLOBAL or SESSION variable: These are the keywords used in the SHOW VARIABLES statement which specifies whether you want to retrieve the Global or Session variable.
- LIKE Clause: The LIKE clause is when you want to show only variables related to an entity based on the pattern.
- WHERE Clause: It is a condition statement that is used for to specify the condition where you want to use it.
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 |
Conclusion
MySQL SHOW VARIABLES statement is mostly used to display the variables along of the SQL server along with their values, it has many clauses which are used on a particular condition such as LIKE, WHILE, etc. By exploring the above article, you can easily understand the concept of SHOW VARIABLES statement.
Similar Reads
MySQL USE Statement
MySQL is a very flexible and user-friendly Database. The USE command is used when there are multiple databases and we need to SELECT or USE one among them. We can also change to another database with this statement. Thus, the USE statement selects a specific database and then performs queries and op
4 min read
MySQL - ALTER VIEW Statement
The ALTER VIEW statement in MySQL is a powerful tool that allows users to modify the definition of an existing view without the need to drop and recreate it. This statement is particularly useful for changing the query or structure of a view to better help the needs of the application or database de
5 min read
MySQL CREATE VIEW Statement
MySQL, an open-source relational database management system, offers a variety of features to manage and manipulate data efficiently. One of these features is the CREATE VIEW statement, which allows you to create a virtual table known as a view. A view provides a way to simplify complex queries, enha
5 min read
MySQL SELECT Statement
The MySQL SELECT statement is essential for fetching data from tables. It retrieves information and stores it in a result table, often referred to as a result set. Widely used in MySQL, SELECT is a fundamental command for querying databases. This article covers the basics of SELECT syntax and explor
4 min read
PostgreSQL - Row Type Variables
In PostgreSQL, row type variables are handy when you need to store a whole row of data returned by a query. They are particularly useful when dealing with SELECT INTO statements where the result contains multiple columns from a table or view. This feature simplifies handling data from complex querie
4 min read
SQL CREATE VIEW Statement
The SQL CREATE VIEW statement is a very powerful feature in RDBMSs that allows users to create virtual tables based on the result set of a SQL query. Unlike regular tables, these views do not store data themselves rather they provide a way of dynamically retrieving and presenting data from one or ma
4 min read
MySQL Show Trigger
SHOW TRIGGERS is the MySQL way of showing all the registered database triggers for a given database. Triggers are special kinds of rules that perform predefined actions on their own in response to some event. SHOW TRIGGERS makes public the triggers set up with the events to which they respond and th
7 min read
PL/SQL CREATE TABLE Statement
PL/SQL CREATE TABLE statement is a fundamental aspect of database design and allows users to define the structure of new tables, including columns, data types, and constraints. This statement is crucial in organizing data effectively within a database and providing a blueprint for how data should be
3 min read
PostgreSQL - Show Tables
In PostgreSQL, viewing tables is an essential task for managing and organizing our database. Although PostgreSQL does not support the SHOW TABLES command like MySQL, it offers alternative commands like \dt in the psql tool, which helps users list all tables within a specific databaseIn this article,
4 min read
SQLite Show Tables
SQLite is a lightweight database library written in C which is used for embedding the database with applications. SQLite is a serverless, lightweight, cross-platform, and highly reliable database engine that provides the standard SQL syntax making it easy to use standalone or integrate with any othe
8 min read